Using Microsoft Access with Exchange/Outlook to Manage Email Lists and Automate Unsubscribe/Removal Requests

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?

Using Microsoft Access to Manage Unsubscribe/Removal Requests

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.

Contacts

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:

Outlook import

Choose the option to create a link, and then you will be prompted for the folder:

Choose Outlook Folder

Click Next, and follow the prompts to link to the folder. When finished, the folder shows up as a linked table:

Import table

Now you can run queries using your desired logic to manage the list.

Running Queries to Unsubscribe Recipients

In this example, we will unsubscribe recipients using the following rules:

  • Case 1: Emails that include the word "Unsubscribe" in the message subject.
  • Case 2: Emails that failed with the message "User Unknown" three or more times.

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.

Step 1: Add Emails from Case 1

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.

Step 2: Add Emails from Case 2

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;

Step 3: Unsubscribe

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));

Download the Sample

A sample database is posted here: UnsubscribeSample.zip

Run the following queries, in this order:

  1. Case1a_Insert
  2. Case2c_Insert
  3. Final_ProcessUnsubscribe

Additional Comments

A few things worth noting are:

  • Be careful to review the returned messages, don’t just assume that you should unsubscribe the email address. Look for notifications that your message has been quarantined or classified as Spam. In this case, you may need to revise your message or ask your recipient to add you to their White List. In other cases, mail servers may ask for a manual confirmation before delivering a message, such as clicking on a link or replying to an email.
  • We don’t recommend unsubscribing a recipient the first time that their message is returned as undeliverable. There may be cases when a non-delivery notification is due to a mailbox being full or a mail server being temporarily offline. Instead, we recommend unsubscribing recipients if their message is undeliverable for a few weeks or months.

Additional Tips and Techniques for Outlook/Exchange

Messages

Calendar

Microsoft Access and Outlook

Microsoft Access Email Documentation

Microsoft Access 2016 Version is shipping!

New Features

New X.7 Versions for Microsoft Access 2013, 2010, and 2007!

X.6 Versions for Microsoft Access 2003, 2002, and 2000


View all FMS products for Microsoft Access All Our Microsoft Access Products

Emailer Info

Reviews

"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."

Sandra Daigle
Microsoft Access MVP

More Reviews

Additional Info

 

 

Free Product Catalog from FMS