Language: Deutsch English















Last Update: 2017 - 12- 27





Send serial email with Microsoft Access, VBA and Outlook

by Philipp Stiefel, originally published June 17th, 2016


VBA code and envelopes, article header image

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.

Sample data table Sample data table - Design View

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.

A core principle of database development is: Query only the data you need!

We apply this principle here. So we are not querying all customers and using conditional logic in our VBA code to decide whether to send the newsletter to a particular customer or not. Instead we use a query to retrieve only the newsletter subscribers from the table for processing.

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

Especially if you are new to programming, it is a good practice to write your code in iterations. You can focus on one aspect of the task at hand in each iteration. I see two significant advantages in this approach. 

  • You reduce the risk of forgetting something early on that would be much more difficult to spot later, when there is more code in your VBA procedure.
  • If you start with working on something that is at least a bit familiar, you will be able to create a structure for your code. This will help you to get a clearer picture of the whole procedure you work on.

Compile (Menu: Debug - Compile) and run (if possible) the code after each iteration. It should not result in any errors. If it does, the error is easier to find, because you can focus on the newly added parts when debugging the error.

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:

Public Sub SendSerialEmail() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("SELECT Firstname, LastName, EmailAddress, IsVIP " & _ " FROM qryCustomer_SubscribedToNewsletter") Do Until rs.EOF rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End Sub

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.

Some general things on string concatenation you should know.

  • The ampersand (&) is used to concatenate strings in Visual Basic.
  • You can put an underscore at the end of the line to continue any statement on the next line. This is very useful to make long statements more readable.
  • To append data to a string variable, you need to concatenate the current value with the new data you want to append. That looks like myVar = myVar & “new data”.

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.  

Public Sub SendSerialEmail()
Dim db As DAO.Database Dim rs As DAO.Recordset
Dim emailTo As String Dim emailSubject As String Dim emailText As String
Set db = CurrentDb Set rs = db.OpenRecordset("SELECT Firstname, LastName, EmailAddress, IsVIP " & _ " FROM qryCustomer_SubscribedToNewsletter") Do Until rs.EOF
emailTo = Trim(rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value) & _ " <" & rs.Fields("EmailAddress").Value & ">" emailSubject = "Amazing newsletter" If IsNull(rs.Fields("FirstName").Value) Then emailSubject = emailSubject & " for " & _ rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value End If emailText = Trim("Hi " & rs.Fields("FirstName").Value) & "!" & vbCrLf If rs.Fields("IsVIP").Value Then emailText = emailText & "Here is a special offer only for VIP customers!" & _ "Only this month: Get the foo widget half price!" & vbCrLf End If emailText = emailText & _ "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. " & _ "Maecenas porttitor congue massa. Fusce posuere, magna sed " & _ "pulvinar ultricies, purus lectus malesuada libero, sit amet " & _ "commodo magna eros quis urna."
rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing
End Sub

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.

Before you try out any code to send emails, please do make sure that …

  • You either use a database containing only a few test records or you modify your query for the email to return only test records.
  • Configure Outlook to not send any email automatically. Neither immediately nor every X minutes.

Test your outlook configuration by manually sending an email to yourself before running any email code.

It is really embarrassing to accidentally send gibberish test emails to hundreds of your customers. - So be very careful here.

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.

Add Reference to Outlook Object Library

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

Public Sub SendSerialEmail()
Dim db As DAO.Database Dim rs As DAO.Recordset Dim emailTo As String Dim emailSubject As String Dim emailText As String
Dim outApp As Outlook.Application Dim outMail As Outlook.MailItem Dim outlookStarted As Boolean On Error Resume Next Set outApp = GetObject(, "Outlook.Application") On Error GoTo 0 If outApp Is Nothing Then Set outApp = CreateObject("Outlook.Application") outlookStarted = True End If
Set db = CurrentDb Set rs = db.OpenRecordset("SELECT Firstname, LastName, EmailAddress, IsVIP " & _ " FROM qryCustomer_SubscribedToNewsletter") Do Until rs.EOF emailTo = Trim(rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value) & _ " <" & rs.Fields("EmailAddress").Value & ">" emailSubject = "Amazing newsletter" If IsNull(rs.Fields("FirstName").Value) Then emailSubject = emailSubject & " for " & _ rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value End If emailText = Trim("Hi " & rs.Fields("FirstName").Value) & "!" & vbCrLf If rs.Fields("IsVIP").Value Then emailText = emailText & "Here is a special offer only for VIP customers!" & _ "Only this month: Get the foo widget half price!" & vbCrLf End If emailText = emailText & _ "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. " & _ "Maecenas porttitor congue massa. Fusce posuere, magna sed " & _ "pulvinar ultricies, purus lectus malesuada libero, sit amet " & _ "commodo magna eros quis urna."
Set outMail = outApp.CreateItem(olMailItem) outMail.To = emailTo outMail.Subject = emailSubject outMail.Body = emailText outMail.Send
rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing
If outlookStarted Then outApp.Quit End If Set outMail = Nothing Set outApp = Nothing End Sub

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: Share on Facebook Tweet Share on LinkedIn Share on XING

Subscribe to my newsletter

*

I will never share your email with anyone. You can unsubscribe any time.



© 1999 - 2017 by Philipp Stiefel