Using Microsoft Access with Exchange/Outlook to Manage Email Lists and Automate 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.

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:

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

A sample database is posted here: UnsubscribeSample.zip

Run the following queries, in this order:

1. Case1a_Insert
2. Case2c_Insert
3. Final_ProcessUnsubscribe

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.