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 that 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

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.

Move Emails to a Standalone Outlook Folder

To start the process, 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

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 article's 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 tag makes it harder to find than a straight text search.

So now we locate the start of the actual transaction id in the HTML string using the following lines of VBA because TxnID occurs just before the Transaction ID.

 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 highly recommend that you learn to use the Immediate Window shown in Figure 4 so that you can find the exact values of variables such as 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 (number 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 shorter string are the Amount and the Currency. Let's 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, 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 handy functions 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 may be able to use the demo email in the download database.

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.

Click on the Review Order button and you can see the information that was read from the email in a 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.

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.

» See All Articles by Columnist Garry Robinson