Many organizations use Microsoft Access to manage a list of email contacts. In conjunction with Total Access Emailer from FMS, it's easy to send everyone a personalized email (such as a newsletter, order notification, or even a recipient’s individual invoice or sales receipt) in a convenient and automated way.
Handling undeliverable emails and unsubscribe requests, however, can be tricky. While a program like Total Access Emailer can determine if a given email address adheres to the standard email address convention, it cannot receive or manage a response from the recipient server (such as an non-delivery notification or an unsubscribe request).
Unsubscribe requests can be handled by a web site that interacts with your mailing list database, and requires recipients to click on a link in the email. But how can you clean your list and get rid of non-deliverable emails?
Microsoft Access includes the ability to link to Exchange/Outlook folders and treat the email messages just like records in a table. Using your own logic, you can write a series of queries to review the incoming messages and handle unsubscribe requests.
To demonstrate, we’ll use the Access 2007 Contacts template. For example, assume that our Contacts list is as follows and that we have sent several monthly newsletters to this contact list.
In this case, some of our newsletters failed to reach their recipients and were returned to us as undeliverable. Others reached their recipients, and the recipients replied requesting that they be unsubscribed from the mailing list. I have made a folder in Outlook to store these items, and I created an Outlook rule to move the items there automatically. Now, I can import or link to this folder from Outlook into my Access database using these steps.
On the External Data ribbon, in the Import group, drop down the More button and choose Outlook Folder:
Choose the option to create a link, and then you will be prompted for the folder:
Click Next, and follow the prompts to link to the folder. When finished, the folder shows up as a linked table:
Now you can run queries using your desired logic to manage the list.
In this example, we will unsubscribe recipients using the following rules:
You will need to examine your own list of returned emails and add logic as your needs require. Your logic can be as simple or as complex as you want. It may take some trial and error, but compared with manually reviewing all emails, this can be a significant time saver.
We’ll start by adding to a table named UnsubscribeList, which will keep track of who was unsubscribed and when:
Then we’ll run a single query to unsubscribe all emails in the list.
First, we’ll create a query to handle Case 1. This is a simple Append query:
INSERT INTO UnsubscribeList ( ContactID ) SELECT Contacts.ID FROM ListManagerFolder INNER JOIN Contacts ON ListManagerFolder.From = Contacts.Email WHERE (((ListManagerFolder.Subject) Like "*unsubscribe*"));
where Contacts is our main Contacts table and ListManagerFolder is the table name for our linked Outlook folder.
Case 2 is a bit more complex and it requires a series of queries to accomplish it.
Our first challenge is getting the recipient’s email address. Unlike in Case 1, the From field does not show the recipient’s email address. We need to parse out the recipient’s email address from the [Contents] field using a few functions:
SELECT ListManagerFolder.Contents, InStr([Contents],"<")+1 AS EmailAddressStartPos, InStr([Contents],">") AS EmailAddressEndPos, Mid$([Contents],[EmailAddressStartPos],[EmailAddressEndPos]-[EmailAddressStartPos]) AS EmailAddress FROM ListManagerFolder WHERE (((ListManagerFolder.Contents) Like "*User unknown*"));
where Contacts is our main Contacts table and ListManagerFolder is the table name for our linked Outlook folder)
We will name this query Case2a_GetEmail.
Next, we’ll run a query on the Case2a_GetEmail query to group on the Email Address and count the number of Undeliverable messages for each email address:
SELECT Case2a_GetEmail.EmailAddress, Count(Case2a_GetEmail.Contents) AS CountOfContents FROM Case2a_GetEmail GROUP BY Case2a_GetEmail.EmailAddress HAVING (((Count(Case2a_GetEmail.Contents))>=3));
We’ll name this query Case2b_CountEmails
Finally, we can run a query on the Case2b_CountEmails query to update the Contacts table and unsubscribe recipients who were undeliverable at least 3 times:
INSERT INTO UnsubscribeList ( ContactID ) SELECT Contacts.ID FROM Contacts INNER JOIN Case2b_CountEmails ON Contacts.Email = Case2b_CountEmails.EmailAddress;
Now that our UnsubscribeList is complete, we can update our Contacts table with a simple Update query:
UPDATE UnsubscribeList INNER JOIN Contacts ON UnsubscribeList.ContactID = Contacts.ID SET Contacts.SendEmail = False, UnsubscribeList.UnsubscribedDate = Now() WHERE (((UnsubscribeList.UnsubscribedDate) Is Null));
A sample database is posted here: UnsubscribeSample.zip
Run the following queries, in this order:
A few things worth noting are:
"I was thrilled with how easy it was to run and I appreciated that I didn't need to build any special tables or queries."
Microsoft Access MVP