How To Read A PayPal Notification Email

for Access 2007 thru to Access 2000

In this article I will show you how to read information from a PayPal Notification email by using Microsoft Access, Outlook and VBA. If you are unsure what PayPal is, it is one of the biggest payment systems on the Internet and it is owned by eBay, the online auction king. Anyway the reason that you would want to read a PayPal notification email is because the alternative is to meticulously copy and paste all the different parts of the email into your computer system.  This is something that is both tedious and error prone and that should be avoided.

The PayPal email structure

When you receive a payment notification from PayPal, it is going to look like the email in figure 1. The structure of this email is HTML so it is a bit tougher to read than ordinary text emails as it includes HTML tags along with the text. In this article we will concentrate on retrieving the information in the email that is easy to find in code. In Figure 1, you will see the items that I will retrieve programmatically, these are numbered 1 to 5. You will also see further down the email that there are details on the Buyer that can include delivery address and other notes related to the purchase. These are quite difficult to trace in the email and I am not going to cover that in this article (because I handle that manually).

So in a nutshell we are going to read the following elements from the email using VBA

1. The transaction ID
2. The amount
3. The currency
4. The Buyers name
5. The email address

If we get all these correct, we will be far less likely to send the order details to the wrong person and believe me, getting these details and the email address wrong can lead to some messy situations. In our case, as our prices rarely change, we use prices to identify the product that the buyer has ordered.

Figure 1 Paypal Notification email with VBA items highlighted

Move Emails to a Standalone Outlook Folder

To start the process of, we need to get the email into a separate Outlook folder. Your can do this by either moving the email manually and dropping it into the folder as I have done in Figure 2 or by setting up an Outlook rule to do this for you as I have done in Figure 3. Note that I like to name this folder with an underscore prefix ( _Orders) so that the orders appear at the top of the Outlook folders.

Figure 2 -Move the email to the _Orders folder manually

Figure 3 - An Outlook Rule to move the Paypal email to the _Orders folder

Interacting With Outlook

To communicate with Outlook, we use MAPI and the Outlook namespace using code that looks like this

.Folders("Personal Folders").Folders("_Orders")

We then find the text of the email using code that looks like this

emailContents = .Items(1)Body

Now I have discussed the way you manage Outlook with Access in a previous article on my own website. I suggest that you either download this articles sample database or head to this page to read about the details.

http://www.vb123.com.au/toolbox/03_docs/outlookorders.htm

 

Processing The Body Text

To process the text of the email, that is the vba string EmailContents, there are three functions that we use. The first two, the INSTR and MID functions will be familiar to most VBA programmers. The other is GETWORD which I will explain later. If you look at Figure 1, you will see the Transaction ID highlighted by the 1 icon. Now what we want to do is search for the start of the Transaction ID in the EmailContents string. If you look at Figure 4, you will find that the HTML tags makes it harder to find than a straight text search.

Figure 4 - The HTML body text as viewed in the VBA Immediate Window

So now we locate the start of the actual transaction id in the HTML string using the following lines of VBA

 ipos1 = InStr(ppTransactionID, "TxnID")

This will return an integer number of something like 232. We then do then same thing to locate Hello which is right after the end of the transaction ID. We now know the start and the end of the string and with the use of some constants, we can lift the actual transaction ID using the MID function.  I highlighy recommend that you learn to use the Immediate Window like in Figure 4 so that you can find out the exact values of variables like iPos1 and IPos2.

ipos1 = InStr(emailContents, "TxnID")
ipos2 = InStr(emailContents, "Hello")
ppTransactionID = Mid(emailContents, ipos1 + 6, ipos2 - 6 - ipos1)

Using A Custom Function Called GETWORD

Once you have mastered the art of pulling text out of the Body text strings, you then need to tackle the payment entries (no's 2 and 3 in figure 1) which I find using a function called GetWord. This function was released by Microsoft as freeware back in the days of Access 97. In the following code, I identify the start of the payment and then split the body string so that all the text before the payment information is removed. The first two words in this string are the Amount and the Currency. Lets look at the code that makes this possible.

ipos1 = InStr(emailContents, "You received a payment of ")
strEc = Mid(emailContents, ipos1 + Len("You received a payment of "), 200)
ppTotalAmount = GetWord(strEc, 1)
strCurrency = GetWord(strEc, 2)

I will bet now that if you are still reading this article, that you will be keen to look at these two functions in the demonstration database. So open the database and do a global search in the VBA container for these functions.

Function GetWord(StringReq, integerWordPosition)

and

Function CountWords(StringReq) As Integer

These are pretty handy functions that allow you to find words in sentences that are generated by computer software such as that used in the PayPal email. I use these in other email reading programs in my business. If you want to use the functions, import the module called StringParsing into your database.

How Does the Download Application Work

When the first form is open and you press the Extract button as in Figure 5, the software looks to see if there are any emails in the Orders folder and then works out the Person and the Cost. It goes without saying that you need at least one authentic PayPal order email to use this software but you maybe able to use the demo email in the download database.

Figure 5 - The first of the forms to process your PayPal email

If you press Yes (see Figure 5), the ID, Name, Person and Email address are copied into a new record in a table in the database and a button appears on the form as shown in Figure 6. The email is then moved to another folder using Outlook Automation VBA.

Figure 6. The Processing form after the email has been read

Click on the Review Order button and you can see the information that was read from the email in an Access form that shows all the fields in the PaypalOrders table (see Figure 7).  At this stage you will add or edit any other information relevant to the PayPal email and then you can proceed with other activities such as emailing the order instructions back to the buyer.

Figure 7 - The form that is use to add additional information from the PayPal email.

Conclusion

In this article, I have structured my text to inspire you to stop processing PayPal orders manually and to go hunting in the download database for the code to process your orders. I also hope that the reason that you have read this article is because you are getting lots of orders and haven't got the time to process them in the old way.

Good luck

Garry Robinson

 

  Your Sample Database Is Called   "paypalorders.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

  Your Access 2007 Sample Database Is Called   "paypalorders2007.zip"

Sample database is suited to all versions of Access

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

Note: The Toolbox download for this article comes with full source code which includes late binding Outlook Automation calls and tricky html email parsing of the paypal email..

Other Pages At VB123.com You Might Want To Try
Secure Microsoft Access Passwords and Encryption in Access 2007

 

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. Garry has been involved in 100s of Access databases Access. Contact details  in Australia     Web http://www.gr-fx.com/