• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Automatic Mail Merge With VBA and Access

November 23, 2015 by Ryan McCormick 20 Comments

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!

Related

Filed Under: Microsoft Access, VBA Tagged With: automatic mail merge, MS Access, vba

Reader Interactions

Comments

  1. Tien Phan says

    June 29, 2016 at 10:31 am

    Morover, Your code gave results of all data in query but I just wanted one. How to get results with the selected records?

    Can you help me?

    Thnks for your help!

    Reply
    • Ryan McCormick says

      March 19, 2017 at 4:19 pm

      Update the SQL in your mailmerge query to filter the results you need.

      Reply
  2. Tien says

    July 2, 2016 at 3:28 am

    How to use mailmerge code with selected record? Your code give results of all record in database.

    Please help!

    Thank you very much!

    Reply
  3. keith says

    July 22, 2016 at 1:01 am

    I do similar things with mail merge and I have started to encounter difficulties with new installs – office 2013/2016 windows 10 – ‘cannot open macro storage’ errors appearing when users try to use mail merge.

    Have you encountered this?

    Reply
    • Ryan McCormick says

      March 19, 2017 at 4:11 pm

      Sounds like you may be experiencing problems related to the object library references. Make sure you update your references when changing office versions. Alternatively, you might be able to implement late binding like this example: http://www.minnesotaithub.com/2015/11/solved-late-binding-file-dialog-vba-example/

      You would have a little more leg work to do since the example in the link above is just for the filedialog, but it is possible to do.

      Reply
  4. Robert Helgeson says

    September 28, 2016 at 6:01 pm

    The demo you included with this article is great. Is there anyway the code can be modified to create an individual PDF file for each record in the database?

    Thanks in advance

    Reply
  5. al says

    November 30, 2016 at 9:13 am

    Im trying to create a button that will open a word document that calls upon data from my access db. The button I have only opens the word document, it does not give me the option to scroll through the list in the mail merge nor does it update the file with teh current information.

    Reply
  6. Veronica says

    January 24, 2017 at 9:19 am

    Many thanks for this code, it has been super helpful.

    Reply
  7. Ann says

    January 27, 2017 at 1:19 pm

    Hi, I copied this and changed what I needed to do a Word merge from a button in Access. Occasionally it works but mostly it doesn’t. I am using the Late Binding.

    The times it doesn’t work I have to open the Task Manager and end the WinWord.exe process that is running. then I get the following two messages.

    Run-time error 462 : The remote server machine does not exist or is unavailable and Error -2147023170: Automation error. The remote procedure failed.

    Reply
    • Ryan McCormick says

      March 19, 2017 at 3:59 pm

      During development, you could have potentially executed a version of MS Word running in the background. Open your task manager and kill off any running versions of MS Word before proceeding.

      Reply
  8. Reinier says

    April 17, 2017 at 1:50 pm

    fantastic, ths for this great example. Access had come a long way since 2000 !! 🙂

    Reply
  9. Johnny says

    August 20, 2017 at 4:28 pm

    Hi

    Thanks for the code. Is there a way to generate a directory using this code. I want to get rid of the section breaks. I have tried setting it up in the Word template but can’t get it to work. Thanks

    Reply
  10. Walter says

    November 14, 2017 at 8:24 am

    Very good example
    Thank You

    Reply
  11. ver_tips says

    January 31, 2018 at 3:14 am

    I tried the late binding with Office 2010 and it works well.
    I tried it with Office 2016 and it does not work.
    Anyone has has an idea to solve this issue?

    Reply
    • Patty says

      October 13, 2019 at 10:17 am

      Did you ever find a solution? I’m using 16 as well and I’m stuck. Thanks,

      Reply
  12. George says

    February 15, 2018 at 5:20 am

    Very good example. The sample works great. However, when I tried the same on my Access DB, it gives an error “The database has been placed in a state by the user ‘Admin’….” and I am not able to get any way around. Can you help?

    Reply
  13. Shane says

    March 20, 2018 at 10:03 pm

    I’m trying to use your late binding code in Access 2013. I keep getting a run-time 424: Object required error on this section of code:

    Set oWdoc = oWord.Documents.Open(wdInputName)

    I did kill the winword.exe tasks but that doesn’t work.

    Any thoughts?

    Reply
  14. Katherine Taylor Fletcher says

    March 27, 2019 at 3:29 pm

    Hi,
    I changed the code to point at my selected Query to only get one line of information. I keep getting the issue that I need to confirm the data source. The only option it gives is OLE CB Database Files, then it fails because it says it can’t confirm data source.

    All I did to start getting this program was point at a new query.

    With oWdoc.MailMerge
    .MainDocumentType = 0 ‘wdFormLetters
    .OpenDataSource _
    Name:=CurrentProject.FullName, _
    AddToRecentFiles:=False, _
    LinkToSource:=True, _
    Connection:=”QUERY Selected_PTE”, _
    SQLStatement:=”SELECT * FROM [Selected_PTE]”
    .Destination = 0 ‘wdSendToNewDocument
    .Execute Pause:=False
    End With

    Reply
  15. wouter Schouten says

    June 9, 2019 at 8:07 pm

    I am trying to link this to an onclick event on an button. but i keep getting this error :

    the expression on click you entered as the event property setting produced the following error: A problem occured while microsoft acces was communicating with ole server or activex control

    I have no clue why

    Reply
  16. Anthony19510 says

    March 25, 2020 at 5:46 am

    This is fine for a standalone solution or if using shared mapped drives but not for other scenarios. if you want a multi-user solution then export data to Excel spreadsheet in a folder on drive C: and link template to that.

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in