9.4 Mark a Record on a Form and Return to It Later

12.4 Perform a Mail Merge from Access to Word

12.4.1 Problem

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.

12.4.2 Solution

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".
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"
```
Set a reference to the Word library by choosing Tools References... and selecting the Word library from the list of objects, as shown in Figure 12-6.

Figure 12-6. Set a reference to the Word library

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 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     Resume 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.

12.4.3 Discussion

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.

12.4.3.1 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

12.4.3.2 Performing the mail merge

To 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 With

In Access 2002 you must use the .OpenDataSource method in your code, but this isn't required in Access 2000.

12.4.3.3 Finishing the mail merge

To display the Word documents, set the Application object's Visible property to True:

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.