Inserting merge fields into a word document based on an Access Database

Question

I have a complete Access database and a Word document, and I want to merge them using VBA through Word. Setting up the merge, I have the following:

Sub MergeTest
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users...MailMerge.accdb",_
         ConfirmConversions:=False,_ 
         ReadOnly:=False,_ 
         LinkToSource:=True, AddToRecentFiles:=False, _
         PasswordDocument:="", PasswordTemplate:="",_
         WritePasswordDocument:="", _
         WritePasswordTemplate:="", Revert:=False,_
         Format:=wdOpenFormatAuto,
End Sub

I know there are more parameters to the OpenDataSource command, but I'm not quite sure what to have for those. Regardless, once the documents are merged, I know it's something like:

ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Year"
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Volume"

To add labels. Thing is, this requires me to select each region on the sheet before executing the command. Is there a way to mark the regions on the document to be referred to in script?

score 1 · Answer 1 · answered Jun 14 '16 at 18:04

In order to define "targets" for inserting something at a later time, insert bookmarks into the document. Then

Document.MailMerge.Fields.Add Range:=Document.Bookmarks("NameOfBookmark").Range, Name:="Year"

Note that it's not a good idea to use ActiveDocument throughout your code. Much better is to declare a Word.Document object and instantiate it with the document you're opening (or creating from a template). This will be faster and less prone to errors (if something should change the currently active document in the Word application). Something like:

Dim doc as Word.Document
Set doc = ActiveDocument
doc.MailMerge.OpenDataSource 'and so on

Inserting merge fields into a word document based on an Access Database

1 Answers1