Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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"

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

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

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

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