A recent client of mine wanted me to make some enhancements to an Access database. The enhancements were all aimed at reducing the workflow. One of the issues they were encountering was at the end of the workflow, the user clicked a button inside the Access database that opened a Word file. This Word file would then walk the user through doing a Mail Merge from within Word to get elements out of the database and then create e-mails in Microsoft Outlook.
The problem was there were quite a few steps involved and at the end of it all, the user had to go back into the database and wipe out some temp tables to be able to restart the process the next day. If they got up from their desk and then came back – it was pretty easy to forget where they were in the workflow and could miss steps.
The client wanted to know if there was a way to do the entire mail merge process inside Access so that everything was automated and the wouldn’t have to go out to Microsoft Word. Piecing together a bunch of different scripts online, I was able to duplicate their Word template as an HTML formatted email, generated from within Access with all the appropriate data elements included in the e-mail.
Here is the outline of the code I used – I have included comments to explain what different parts are doing in the code. If you have specific questions, please let me know in the comments below.
Enjoy!
Private Sub send_mail() 'Substantiate the script
'Create application and mail objects
Dim olApp As Object
Dim objMail As Object
'Create a query definition and set it to run a specific query.
'Change "Email Query" in square brackets to match query name in your database.
Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs![Email Query]
'Create a record set and run the query defined above
Dim rst As Recordset
Set rst = qd.OpenRecordset()
'The following code loops through each record brought back by the query and
'creates an email for each record.
rst.MoveFirst
Do Until rst.EOF
strElement1 = rst![DataElement1]
strElement2 = rst![DataElement2]
rst.MoveNext
On Error Resume Next 'Keep going if there is an error
Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open
If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = "address@yourmailaddress.com"
'.Cc = "ccaddress@yourmailaddress.com"
Uncomment out above line to add a carbon copy e-mail address
'.Bc = "bcaddress@yourmailaddress.com"
Uncomment out above line to add a blind copy e-mail address
.Subject = "E-mail Message Subject Goes Here"
.HTMLBody = "<!DOCTYPE html>"
.HTMLBody = .HTMLBody & "<html><head><body>"
You can keep building out the html using the same syntax of adding .HTMLBody from the line above & tacking on whatever is new:
.HTMLBody = .HTMLBody & "<h1><u>This is an example header line</u></h1>"
.HTMLBody = .HTMLBody & "<h2><u>This is an example header 2 line</u></h2>"
.HTMLBody = .HTMLBody & "<table>"
.HTMLBody = .HTMLBody & "<tr><td>Element 1</td><td>"& strElement1 & "</td></tr>"
.HTMLBody = .HTMLBody & "<tr><td>Element 2</td><td>"& strElement2 &"</td></tr>"
.HTMLBody = .HTMLBody & "</table>"
.HTMLBody = .HTMLBody & "<br><br><img height=""20"" width=""684"" border=""0""
src=""C:\Users\USERNAME\Desktop\image001.png"" style=""width: 684px; height: 20px;""/img>"
Above is an example of adding in an image to the Email HTML
.HTMLBody = .HTMLBody & "<br><br>Email Customer Service at
<a href=""mailto:Support@youremailaddress.com"">Support@youremailaddress.com</a>"
Above is an example of adding a URL to the Email HTML
.HTMLBody = .HTMLBody & "<br>or call 1-800-YOUR NUMBER HERE (Mon – Fri, 8am - 8pm Eastern)."
.HTMLBody = .HTMLBody & "</body></html>"
By ucommentiong out the "Send" command below, emails will be sent out without allowing the user to review them first. Using the "Display" command brings up all the emails as draft emails and allows the user to review them prior to sending them.
'.send
.Display
End With
Loop
End Sub
Excellent Tips 
Hi, I would love to be able to use this code for my access database as It would be very useful.
Where do I paste this code and what would trigger it?
Hi Miguel!
Please see another of my posts – it’s for Microsoft Excel but the majority of the instructions are valid for using VBA scripts in any of the Microsoft Office tools including Access: https://excellenttips.wordpress.com/2015/01/14/intro-to-excel-vba-editor/
The options are pretty much the same for the VBA developer tools on the Microsoft Ribbon in office 2007 and beyond. Let me know if you have further questions!
Yosef
Hi Yosef, thank you for your quick reply.
I looked at the tutorial and my version of excel 2007 didn’t give me the option of enabling the developer tab.
I tried applying that concept on access. I created a button and chose Code Builder option under “on click” field, which took me to the VBA window.
I tried pasting the above code there, but i get many errors.
Further I don’t know how to:
Create application and mail objects
Create a record set
I know I don’t know much but i’m eager to learn. Thanks for posting.
Hi Miguel – check out the following link on how to enable the developer tab in Excel 2007: http://www.addintools.com/documents/excel/how-to-add-developer-tab.html
You will need to modify the VBA script above to work with the Access database you’ve created. You will need a table (or multiple tables) with the data you want included already defined. In addition, you will need to create a SQL query to bring back the data elements you want to include in the email. This query is your recordset.
Then you will need to modify the script above based on the comments/instructions I included (a comment in VBA is any line that starts with a tick mark (‘).
If you’re new to access and databases in general, this is not a great post to start on as it has some fairly advanced concepts. Unfortunately I don’t have a post on how to build a basic database in Access as a place for you to start. Definitely a good topic that I should get to at some point though 🙂
If you’re having trouble creating the recordset and application and mail objects, my guess is you need to reference some extra VBA libraries before your code will compile. Unfortunately it’s always hit or miss for me to figure out which libraries you need.
To add a library reference, open the VBA editor in Access (Database Tools > Visual Basic to open the editor, then Tools > References to open the library selection menu. Depending on what libraries your machine is loaded with (depends on your version of Office, etc.) you will need to search for libraries that include Recordset (e.g. Microsoft ActiveX Data Objects Recordset 2.8 Library, etc.) and potentially others such as Microsoft Office 16.0 Object Library, or Microsoft Outlook 16.0 Object Library, etc. The version numbers will change depending on your version of Office.
I would recommend searching online based on the error messages you’re getting from the VBA editor when it tries to run/compile the code to see if you can figure out which library references you are missing.
Let me know if you have further questions and I’ll see if I can help troubleshoot some more. 🙂
Yosef
Hi Yosef,
Thank you so much for this code! I have a questions as follows:
– I am trying to make borders around the table and I used
.HTMLBody = .HTMLBody & “table, th, td {border: 1px solid black;}”
which did not work for me. How can I make tables with borders?
– I am able to import the image but the height does not change even if I changed the hight to 1440px.
I will appreciate your help!
Thanks.
Hi Ali, my apologies for not replying quicker. I think the issue is you’re trying to use CSS to format your table. I don’t think Access can handle that (at least not with the code as I’ve laid out in this post). Have you tried using HTML to format the table directly? For example try adding a style element instead:
.HTMLBody = .HTMLBody & “<style> table, th, td {padding: 10px; border: 1px solid black; border-collapse: collapse;}</style>”
Should give you a solid border around every cell in the table with 10 point padding between the cell contents and the cell borders.
Alternatively, you may want to try an even simpler HTML format (will not be quite as nice looking) wherever you put your first tag, change it to “<table border=”1″>” such as here:
.HTMLBody = .HTMLBody & “<table border=”1″>”
I hope this helps! I haven’t tried this in many years so not sure how well this still works in the latest versions of Outlook.
It’s working on one computer and not on another. Anyone have any ideas? The only thing I’ve thought about so far is to check for missing references on the computer that isn’t working. Thanks.