Recipe 12.4: Perform a Mail Merge from Access to Word
You'd like to be able to do a mail merge to Word using Access data, without having to launch the mail merge from Word using its mail merge features.
Access allows you to output data directly to any format using the DoCmd.OutputTo functionality. You can then run a mail merge from Word to a predefined Word template that contains the merge codes.
First you must create the Word template that holds your merge codes; then you can write the code in Access that performs the merge. The sample application 12-04.MDB contains a table and a query that retrieves the data to be sent to Word.
To perform a mail merge from Access to Word, follow these steps:
In Access, create the query that you will use for your data. Copy the rows from the datasheet view of the query and paste them into a Word document.
Save the Word document in the same folder as the Access database. The sample application uses the name qryCustomers.doc.
In Word, create a template by choosing File New Template from the menu. Fill in the plain text for your main merge document.
Choose Tools → Mail Merge from the menu to add the merge fields to the template. Use the Active Document option and select the Word document you created in Step 2. This will add the merge toolbar to your application.
Insert the merge codes for the fields in your template, then save the template in the same folder as qryCustomers.doc and the Access database.
In Access, write the code to perform the mail merge. Declare two module-level constants for the name of the template and the name of the query:
Private Const conTemplate As String = "acbMailMerge.dot" Private Const conQuery As String = "qryCustomers"
Create a procedure to perform the mail merge. Here's the complete listing:
Public Sub MailMerge( ) Dim strPath As String Dim strDataSource As String Dim doc As Word.Document Dim wrdApp As Word.Application On Error GoTo HandleErrors ' Delete the rtf file, if it already exists. strPath = FixPath(CurrentProject.Path) strDataSource = strPath & conQuery & ".doc" Kill strDataSource ' Export the data to rtf format. DoCmd.OutputTo acOutputQuery, conQuery, _ acFormatRTF, strDataSource, False ' Start Word using the mail merge template. Set wrdApp = New Word.Application Set doc = wrdApp.Documents.Add(strPath & conTemplate) ' Do the mail merge to a new document. With doc.MailMerge .OpenDataSource Name:=strDataSource .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With If .State = wdMainAndDataSource Then .Execute End If End With ' Display the mail merge document. wrdApp.Visible = True ExitHere: Set doc = Nothing Set wrdApp = Nothing Exit Sub HandleErrors: Select Case Err.Number Case 53 ' File not found. Resume Next Case Else MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Select End Sub
Create the FixPath procedure to handle any backslashes in the pathname:
Private Function FixPath(strPath As String) As String If Right(strPath, 1) = "\" Then FixPath = strPath Else FixPath = strPath & "\" End If End Function
Test the procedure by positioning your cursor anywhere in the MailMerge procedure and pressing the F5 key.
Microsoft Word exposes an Application object, which you can use to launch Word, and a Document object, which you can use to open a new Word document. Once you've launched Word, you can use all its capabilities from your Access application. The following sections outline the steps involved in communicating with Word via Automation.
Starting the connection with Word for Windows
To be able to work with Word from Access, you must create an object variable to refer to the Word Application object. You also need a Document variable to work with a specific Word document. The following code fragment defines these variables:
Dim doc As Word.Document Dim wrdApp As Word.Application
The next step is to delete any previously existing data source documents:
strPath = FixPath(CurrentProject.Path) Kill strPath & conQuery & ".doc"
If the document doesn't exist, the error handler will simply resume on the next statement and create a new document containing the data from the query using the OutputTo method of the DoCmd object:
DoCmd.OutputTo acOutputQuery, conQuery, _ acFormatRTF, strPath & conQuery & ".doc", False
Performing the mail merge
launch Word and create a new document based on the mail merge
template, set the Application object to a new instance of
Word.Application. Set the Document object to
create a new document using the Application's Add
method, basing it on your template:
Set wrdApp = New Word.Application Set doc = wrdApp.Documents.Add(strPath & conTemplate)
Once the document is open, use the Document object's MailMerge method to merge the data to a new document:
With doc.MailMerge .OpenDataSource Name:=strDataSource .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With If .State = wdMainAndDataSource Then .Execute End If End With
In Access 2002 and later you must use the .OpenDataSource method in your code, but this isn't required in Access 2000.
Finishing the mail merge
display the Word documents, set the Application
object's Visible property to
wrdApp.Visible = True
Once the Word document is displayed, clean up by setting the Word
object variables to
Nothing. This frees up the
memory and system resources:
Set doc = Nothing Set wrdApp = Nothing
You'll see both the new document, named Document1 (based on the template), and the actual merge documents. You can save the merge documents or print them from Word.