Automatic Mail Merge With VBA and Access

I once built a program with MS Access that handled a mail merge job of ~1000 letters per week. It used a very simple word template. For input, every Friday, I pulled data from a production SQL database which I saved to an Excel spreadsheet. I built an import function that loaded the Excel data into table in my MS Access tool.

The program ran a mail merge process for all letters that were freshly loaded and auto-archived to multiple network folders in PDF format. Also, all files were named with a standardized naming convention. After all of the files were archived, the program shot an email off to our print services people with a file path to the new print file.

With my tool, this whole task was completed in under two minutes including QA. If I had to run this process manually, it would have probably taken a half hour (including QA) to complete every week.

Why am I telling you all of this? Because manual processes lead to human error. Tested, automated processes can be measured and deliver consistent results when the right controls are used.

Thirty minutes is not much time, but the process of pulling data, running a mail merge, checking a sample of documents for consistency, sending to print and saving to multiple network folders with a standardized file naming convention is very tedious and error prone. Even for someone who is detail oriented. All of the process improvement stuff being said, this post is not a tutorial about process improvement, so moving on…

My example today is not as feature rich as the one described above, but with some planning and with the use of some of the other examples on this site, I am sure something stable could be built in a reasonable amount of time. Unless you are like “Shut Up and Take my Money!”, follow the VBA help link to the right and lets chat!
Import Function Example

Because this example has a lot of moving pieces, here is a sample file for you to follow along with:
mailmerge-example

I added a module “example” to the mail merge code in the example database to handle the demo. The module “main” contains the same example code listed below.

The example file contains:

1. MS Word Template
2. Access Database with sample data
3. SQL Query built for the actual merge.
4. Directory Structure for saving file outputs.
5. Function for completing automatic mail merge

Note about integrating with an existing mail merge solution

If you are going to integrate this with an existing solution, I recommend making and working off of a backup as you build your custom version of this solution. As always, I take zero responsibility for misuse, data loss, etc…

Merge from Query instead of Table

I strongly recommend building your mail merge from a query instead of directly with a table. With a table, you don’t control over your merge data. There are some things that I did on purpose with the mail merge query in the example. First off, I only included the fields that I needed to use for the mail merge. Secondly, there is a field in the ‘customers’ table that is called [SendMail]. I built my query to only include the names in the table that are marked ‘yes’ for [SendMail].

Another benefit that is not included in my example is the ability to join data from other tables. If you are building a custom solution, the sky is the limit and you can include any data you want.

Update your code so it references the name of your mailmerge query. Unless you named your query mailmerge, then the example has you covered:

SQLStatement:="SELECT * FROM [mailmerge]"

Create your mail merge template before using the automatic VBA merge solution

After you have your query created, SAVE and CLOSE your Access database. I am going to say that again, SAVE and CLOSE your Access database. If you don’t follow this step, you may run into issues where your ‘database is locked’ when selecting your list from the database.

Open your mail merge template, click on the “Mailings” tab and select “Select Recipients > Use an Existing List”. Browse to your Access database and Select the QUERY you created. Follow the rest of the instructions.

Use the address block feature for addresses. This helps to convert FirstName, LastName fields into a fullname in the address block as well. Select “Match Fields” and check to ensure your fields match up (firstname, lastname, address, etc.). Click the checkbox to save the settings for “this computer”.

Add the fields to your document, click on preview to make sure everything looks good. If everything is good, SAVE and close your word document.

I recommend saving your template to a directory that is relative to your Access Database. This way you can reference your template filename with a relative file path object:

CurrentProject.path & "\templates\mytemplate.docx"

.

You will want to update your example function to properly reference your template file:

' Set Template Path
'------------------------------------------------
wdInputName = CurrentProject.Path & "\templates\mailmerge-template.docx"

Automatic Mail Merge with VBA and MS Access – Early Binding Example

'----------------------------------------------------
' Auto Mail Merge With VBA and Access (Early Binding)
'----------------------------------------------------
' NOTE: To use this code, you must reference
' The Microsoft Word 14.0 (or current version)
' Object Library by clicking menu Tools > References
' Check the box for:
' Microsoft Word 14.0 Object Library in Word 2010
' Microsoft Word 15.0 Object Library in Word 2013
' Click OK
'----------------------------------------------------
Sub startMerge()
    Dim oWord As Word.Application
    Dim oWdoc As Word.Document
    Dim wdInputName As String
    Dim wdOutputName As String
    Dim outFileName As String
    
    ' Set Template Path
    '------------------------------------------------
    wdInputName = CurrentProject.Path & "\templates\mailmerge-template.docx"
    
    ' Create unique save filename with minutes
    ' and seconds to prevent overwrite
    '------------------------------------------------
    outFileName = "MailMergeFile_" & Format(Now(), "yyyymmddmms")
    
    ' Output File Path w/outFileName
    '------------------------------------------------
    wdOutputName = CurrentProject.Path & "\completed\" & outFileName
    
    Set oWord = New Word.Application
    Set oWdoc = oWord.Documents.Open(wdInputName)
    
    ' Start mail merge
    '------------------------------------------------
    With oWdoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.FullName, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="QUERY mailmerge", _
            SQLStatement:="SELECT * FROM [mailmerge]"
        .Destination = wdSendToNewDocument
        .Execute Pause:=False
    End With
    
    ' Hide Word During Merge
    '------------------------------------------------
    oWord.Visible = False
    
    ' Save file as PDF
    ' Uncomment the line below and comment out
    ' the line below "Save file as Word Document"
    '------------------------------------------------
    'oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
    
    ' Save file as Word Document
    '------------------------------------------------
    oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
              
    ' Quit Word to Save Memory
    '------------------------------------------------
    oWord.Quit savechanges:=False
       
    ' Clean up memory
    '------------------------------------------------
    Set oWord = Nothing
    Set oWdoc = Nothing
End Sub

Automatic Mail Merge with VBA and MS Access – Late Binding Example

'----------------------------------------------------
' Auto Mail Merge With VBA and Access (Late Binding)
'----------------------------------------------------
' NOTE: This is the late binding version of the
' Auto Mail Merge With VBA and Access code. No ref
' to Microsoft Word XX.0 Object Library is needed
'----------------------------------------------------
Sub startMerge()
    Dim oWord As Object
    Dim oWdoc As Object
    Dim wdInputName As String
    Dim wdOutputName As String
    Dim outFileName As String
    
    ' Set Template Path
    '------------------------------------------------
    wdInputName = CurrentProject.Path & "\templates\mailmerge-template.docx"
    
    ' Create unique save filename with minutes
    ' and seconds to prevent overwrite
    '------------------------------------------------
    outFileName = "MailMergeFile_" & Format(Now(), "yyyymmddmms")
    
    ' Output File Path w/outFileName
    '------------------------------------------------
    wdOutputName = CurrentProject.Path & "\completed\" & outFileName
    
    Set oWord = CreateObject("Word.Application")
    Set oWdoc = oWord.Documents.Open(wdInputName)
    
    ' Start mail merge
    '------------------------------------------------
    With oWdoc.MailMerge
        .MainDocumentType = 0 'wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.FullName, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="QUERY mailmerge", _
            SQLStatement:="SELECT * FROM [mailmerge]"
        .Destination = 0 'wdSendToNewDocument
        .Execute Pause:=False
    End With
    
    ' Hide Word During Merge
    '------------------------------------------------
    oWord.Visible = False
    
    ' Save file as PDF
    ' Uncomment the line below and comment out
    ' the line below "Save file as Word Document"
    '------------------------------------------------
    'oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
    
    ' Save file as Word Document
    '------------------------------------------------
    oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
              
    ' Quit Word to Save Memory
    '------------------------------------------------
    oWord.Quit savechanges:=False
       
    ' Clean up memory
    '------------------------------------------------
    Set oWord = Nothing
    Set oWdoc = Nothing
End Sub

I understand that this example has a lot of moving pieces. Please comment if you run into questions/roadblocks. I am always looking to improve this site, my code and provide the best experience.

Happy Coding!