Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 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.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"
```
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 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.

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

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.

[ Team LiB ]