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

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.

Paypal Notification email with VBA items highlighted

Figure 1 Paypal Notification email with VBA items highlighted

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

Move the email to the _Orders folder manually

Figure 2 – Move the email to the _Orders folder manually

An Outlook Rule to move the Paypal email to the _Orders folder

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 article’s sample database or head to this page to read
about the details.

The HTML body text as viewed in the VBA Immediate Window

Garry Robinson
Garry Robinson
Garry Robinson was the editor of www.vb123.com and the Access Unlimited Newsletter. He wrote a book on Microsoft Access Protection and Security and has written many articles for the Smart Access Magazine. Amongst Garry's online contributions is an Access 2007 Security paper for MSDN at http://msdn2.microsoft.com/en-us/library/bb421308.aspx. When Garry isn't working, he likes playing golf, snorkeling and being dragged along to kids soccer and kids basketball and kids golf and kids surf lifesaving and the second son hasn't even started sports yet!

Latest Articles