Send serial email with Microsoft Access, VBA and Outlook

by Philipp Stiefel, originally published June 17th, 2016

As you probably have seen already, I’ve got quite a few samples and articles on sending email with Access and VBA on this site. Those are dealing with some of the core challenges of that topic. Even if you are not too experienced with Access and VBA, you will benefit from reading them. Still you might struggle to put it all together and create a working solution to send emails from your database.

If that rings with you, this article series is for you. I will show from start to finish how to send a serial email from your database. Beginning with storing email addresses in a table. Then querying them in VBA, concatenating custom, individual email texts and finally creating the emails and sending them off through your Outlook account.

So let’s get started.

Storing email addresses

Storing email addresses in an Access database is very simple. – As long as you resist the temptation to store them in a hyperlink column.

I recommend you use the plain (Short) Text data type to store your email addresses. Its maximum length of 255 characters should usually be enough for any email address.

If you want your email addresses to be clickable and look like a link when displayed on a form in your applications GUI, you can still achieve that with formatting and a few lines of VBA in your form.

Here is the table I am going to use for the rest of this article.

Create a base query for the email newsletter

The rest of this article will be based on the customer table shown above. For this article I assume we have the objective to send an email newsletter to the customers in our database.

If you looked closely at the table in the screenshot, you probably noticed the SubscribedToNewsletter column in that table. We are no spammers, so we are going to send this newsletter only to those customers, who subscribed to it.

We could just use an ad-hoc SQL query in the VBA code to retrieve those customers, but a stored query with the newsletter subscribers is something we can reuse elsewhere in our application. So we create a select query and save it for later reuse.

Here are the steps required to do this: In the Create Ribbon, click Query Design, add the tblCustomer table to the select query and select all columns for output. Then filer the by the SubscribedToNewsletter column using True as criteria. Finally, save the query using the name qryCustomer_SubscribedToNewsletter.

If you are a beginner in Access and not familiar with this, you can watch this short video I recorded.

Writing the code

Now we get to the meaty part of this article; the actual VBA programming.

To write the code, open the VBA Editor, create a new module and name it modSerialEmail. Then create a new Public Sub procedure and name it SendSerialEmail.

I’m going to write the code in three iterations, focusing on one aspect of the code in each iteration.

1. Retrieving a Recordset and looping through the records
2. Concatenating the strings, we need for the email
3. Creating the actual email (Outlook.MailtItem) and sending it

Loop through the records in VBA

To retrieve data from a table or query and use it in VBA we need a Recordset. A Recordset is an object that contains the records resulting from a query and is created by the OpenRecordset method of a Database object with a SQL statement (or the name of a table or stored query). We use the CurrentDb method to get a reference to the database currently open in Access.

Next we use the Do-Loop-Statement to iterate over (MoveNext) all records of our Recordset until we are at the end of the Recordset (EOF). Finally, we Close the Recordset and free/destroy our object variables by setting them to Nothing.

The resulting code looks like this:

This VBA code looks nice and compiles and runs as it is, but it does absolutely no real work.

Concatenating the subject and email text

Let’s continue with the second iteration. We will now concatenate the strings used for the email.

Technically is not required to assign these values to variables first. You could as well just set the properties of the MailItem directly to the values. Using variables makes your code easier to understand as well as much easier to debug, if anything does not work as intended.

I encourage you to use this approach unless it’s the most basic string concatenation.

Now for the actual coding. I declare the variables emailTo, emailSubject and emailText. All of them are strings. Then I add code to assign the values from the Recordset to these strings. That needs to happen inside the Do-Until-Loop, to get the current value for each customer record.

It would be sufficient to use only the email address for emailTo, but it looks more professional to use the recipient’s name as well. If you do this, you will need to enclose the actual email address in angle brackets.

The FirstName column in the database is nullable. So there might be customers whose first name we can’t retrieve from the table. That would result in a leading space before the LastName in the email-To-Header. As that looks crappy, I use the Trim-Function to remove any blanks in front of and after the name.

Next is the subject of the email. I add the customer’s name to the subject. I’m not sure if this would be a good idea for a real email, but I’ll do it here for the sake of the exercise. I use an If-Statement with the IsNull-Function to only add the name if we got a FirstName on record, because otherwise that would look really spammy.

Finally, I assign the intended email text to the emailText variable. I address the recipient by first name, but again use Trim to avoid an ugly blank if the FirstName is Null. Then I add a special offer text to the email, if the customer is a VIP customer (IsVIP). The main text of the email is just a Lorem-ipsum dummy text.

Here is the resulting code.  

The code still looks good, still compiles and executes without error, but still does no real work.

Creating and sending the email

Ok, were at the crucial point now. It’s time to actually create and send the email.

First we need to add a reference to the Outlook-Object-Library.  To do this click Tools – References in the menu of the VBA­-Editor. Find the Microsoft Outlook x.xx Object Library (x.xx is the version number, it depends on the version of Outlook you have got installed.), check this library and confirm the dialog with OK.

Now we can declare our variables; outApp (Outlook.Application), outMail (Outlook.MailItem) and outStarted (Boolean). They should be pretty self-explaining except for outStarted. I will get to that in a second.

I use the GetObject-Function to try to get a reference to an already running Outlook Application. Obviously, this will fail with an error if Outlook is not running. Anticipating that, I use the On Error Resume Next statement to tell VBA to ignore the error and continue executing. Right after the statement I reset the error handling with On Error Goto 0.

If the GetObject failed, our outApp variable is not set (= Is Nothing). In that case I create a new Outlook Application Instance with the CreateObject-Function and set outStarted to true. The latter is just for us to know that our code started Outlook, not the user himself.

We need only a single Outlook Application instance for any number of emails, so I place the code to get/create it before the Do-Until-Loop, right after the variable declaration.

The actual code to create and send each email should be executed for each record in our query. So we put that code inside the Do-Until-Loop. As our variables holding the concatenated strings need to be set properly first, we put the mail code right before the end of the loop.

We use the CreateItem-Method to create a new MailItem object. Then we set the values of the To, Subject and Body properties to the values of the strings we built for these. Finally, we call the Send-Method to send the email.

In the cleanup code after the loop, we close Outlook using the Quit-Method only, if we (our code) started it. Only in that case outStarted will be True.

Here is the complete code of our Sub Procedure

Now the code is complete, but it still looks pretty simple, doesn’t it?

The structuring of the code really pays off now. If you are a beginner to VBA programming, writing the code to send an email might have been an intimidating task. But looking at the few lines that are actually required for completing this now, it should be comprehensible and far less intimidating.

If you would like to watch me actually writing the code, then here is a video for you.

Bottom line

We created actual working code to create and send a serial email with Outlook here. If you paid attention to all the additional information I included here, you will have learned much more than that.

Most valuable is the approach how to write and structure your code to be readable and easily debugable. That will help you with writing code for any purpose. Even if do not adopt this exactly the way as I have demonstrated here, you should see the value in structuring your code beyond the plain requirements of error free code execution.

Thank you for reading. If you enjoyed this article, please share it with your friends and colleagues.

Share this article: