Microsoft KB Archive/170988
Article ID: 170988
Article Last Modified on 1/20/2007
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
- Microsoft Word 97 Standard Edition
This article was previously published under Q170988
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article shows you how to use Automation to create a Microsoft Word 97 mail merge document to insert merge fields, and then to run the merge.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
In Microsoft Access, you can use Automation to run a mail merge in a Microsoft Word 97 document. The sample procedure in this article uses the OpenDataSource and Execute methods of the MailMerge object that is available in Microsoft Word 97.
The following example creates a procedure called CreateMergeDoc, which takes two arguments:
Argument Value -------- ------------------------------------------------------ UseDDE True if you want to use DDE False if you want to use ODBC PrintDoc True if you want to print the result of the merge False if you only want to view the result of the merge
To create the sample procedure, follow these steps:
- Open the sample database Northwind.mdb.
Create a module and type the following line in the Declarations section if it is not already there:
- On the Tools menu, click References.
- In the References box, make sure the Microsoft Word 8.0 Object Library check box is selected, and then click OK.
NOTE: If the Microsoft Word 8.0 Object Library does not appear in the Available References box, browse your hard disk for the file MsWord8.olb. If you cannot locate this file, you must run the Microsoft Word 97 Setup program to install it before you can proceed with this example.
Type the following procedure:
Sub CreateMergeDoc(UseDDE As Boolean, PrintDoc As Boolean) Dim WordApp As Word.Application Dim WordDoc As Word.Document Dim strLetter As String Dim strConnect As String ' Create an instance of Microsoft Word 97. Set WordApp = CreateObject("Word.Application") ' Create a new, empty document. Set WordDoc = WordApp.Documents.Add With WordDoc.MailMerge If UseDDE Then strConnect = "TABLE Customers" Else ' Note that on your computer the path ' to Northwind.mdb may be different. ' You can set DSN=MS Access 97 ' in Microsoft Access 7.0. strConnect = "DSN=MS Access 97 " _ & "Database;DBQ=C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb;" _ & "FIL=MS Access;" End If ' Note that on your computer the path ' to Northwind.mdb may be different. .OpenDataSource _ Name:="C:\Program Files\Microsoft Office\Office\" _ & "\Samples\Northwind.mdb", _ ReadOnly:=True, LinkToSource:=True, _ Connection:=strConnect, _ SQLStatement:="SELECT * FROM [Customers]" ' Define the Merge fields in the document. With .Fields .Add Range:=WordApp.Selection.Range, Name:="CompanyName" WordApp.Selection.TypeParagraph .Add Range:=WordApp.Selection.Range, Name:="Address" WordApp.Selection.TypeParagraph .Add Range:=WordApp.Selection.Range, Name:="City" WordApp.Selection.TypeText Text:=", " .Add Range:=WordApp.Selection.Range, Name:="Region" WordApp.Selection.TypeText Text:=" " .Add Range:=WordApp.Selection.Range, Name:="PostalCode" WordApp.Selection.TypeParagraph .Add Range:=WordApp.Selection.Range, Name:="Country" End With End With ' Define the body of the letter in the merge document. strLetter = "Thank you for your business during the past year." With WordApp.Selection .TypeParagraph .TypeParagraph .TypeText Text:=strLetter .TypeParagraph .TypeParagraph .TypeText Text:="Sincerely," .TypeParagraph .TypeParagraph .TypeText Text:="Northwind Traders" End With With WordDoc.MailMerge ' Only merge records 1-10 from the table. .DataSource.FirstRecord = 1 .DataSource.LastRecord = 10 ' Merge the data to a new document. .Destination = wdSendToNewDocument ' Execute the mail merge. .Execute ' If user specified to print the document, disable ' background printing, and then print the merged document. If PrintDoc Then .Application.Options.PrintBackground = False .Application.ActiveDocument.PrintOut End If End With ' Show the instance of Microsoft Word. WordApp.Visible = True End Sub
- To test this procedure, type the following line in the Debug window, and then press ENTER:
CreateMergeDoc UseDDE:=False, PrintDoc:=False
For more information about using Automation to open an existing Microsoft Word 97 mail merge document, please see the following article in the Microsoft Knowledge Base:
159328 ACC: Using Automation to Run Word 97 Mail Merge from MS Access
For information about using Automation to run a Microsoft Word 7.0 mail merge from Microsoft Access 7.0, please see the following article in the Microsoft Knowledge Base:
154571 ACC95: Running Word Mail Merge from Access Using OLE Automation
Keywords: kbcode kbhowto kbinterop kbprogramming KB170988