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