WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

Cooking with Access
Pages: 1, 2, 3, 4

Recipe 12.4: Perform a Mail Merge from Access to Word

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.

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:

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

  2. Save the Word document in the same folder as the Access database. The sample application uses the name qryCustomers.doc.

  3. In Word, create a template by choosing File New Template from the menu. Fill in the plain text for your main merge document.

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

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

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

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

  1. 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
  2. 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
  3. Test the procedure by positioning your cursor anywhere in the MailMerge procedure and pressing the F5 key.

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.

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

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.

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.

Pages: 1, 2, 3, 4

Next Pagearrow