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:
- MS Word Template
- Access Database with sample data
- SQL Query built for the actual merge.
- Directory Structure for saving file outputs.
- 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!
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!
Update the SQL in your mailmerge query to filter the results you need.
How to use mailmerge code with selected record? Your code give results of all record in database.
Please help!
Thank you very much!
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?
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.
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
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.
Many thanks for this code, it has been super helpful.
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.
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.
fantastic, ths for this great example. Access had come a long way since 2000 !! 🙂
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
Very good example
Thank You
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?
Did you ever find a solution? I’m using 16 as well and I’m stuck. Thanks,
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?
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?
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
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
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.