15.4. Exchanging Information with Microsoft Word

Access has some wonderful reporting features. You can sort, group, filter, and even perform sophisticated combinations of those operations using VBA. If you can create such sophisticated reports in Access would you even need Word? Well, the simple answer is yes. There are a number of tasks you can't perform in Access and still others that you might simply want to transfer into Microsoft Word. For example, the first code sample we'll build starts a mail merge within Word from your Access data. While you can create a letter within Access, it must be done by someone with adequate permissions on your database. You probably don't want to give every user of your application permissions to create and modify reports. Many users probably have no desire to ever modify a report. Working around this limitation is pretty simple. Provide your users with a boilerplate mail merge document in Word and allow them to customize the mail merge document to suit their needs. Then they can simply initiate the mail merge from within Access.

Figure 15.8. Figure 15-8

You can write the mail merge code in two ways. The first, and simplest way, is to use Access VBA to simply define your data source and open the merge document. The second way is to use VBA to perform every step of the mail merge process. We'll examine both methods in this section.

15.4.1. The Easy Way—Use Access VBA to Start Your Merge

If your users are fairly technically savvy with Microsoft Word, you can rely on them to perform most of the steps of the mail merge. In this case, use Access VBA to define the data source and initiate the merge. The following short code segment assumes the user has already created a mail merge template and saved it to his or her hard disk. The code allows the user to select the created template and initiate the merge. Like the previous example, this code utilizes the CommonDialog control from the Windows Common Controls to allow the user to select the filename and location.

Private Sub cmdMailMerge_Click()
Dim strFilePath as String
Dim objWord As Word.Document
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName

Set objWord = GetObject(strFilePath, "Word.Document")
' Make Word visible.
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:="C:Program FilesMicrosoft " & _
"OfficeOffice11SamplesNorthwind.mdb", _
LinkToSource:=True, _
Connection:="TABLE Customers", _
SQLStatement:="Select * from [Customers]"

' Execute the mail merge.
objWord.MailMerge.Execute
End Sub

This code creates a new document, opens the data source, and executes the mail merge. However, there's one key component missing. There are no merge fields. If you have a standard merge document already set up with merge fields and you're only attempting to requery the data source and fill the document with data, this code will work just fine. However, what if you want to start with a blank document? Now we'll examine how you'd set up the merge document with content and merge fields.

15.4.2. The Hard Way—Using VBA to Set Up Your Merge Document

This method really isn't difficult, it merely requires you to write more code. It's actually easier for your users, as they really don't have to do anything other than click the big button. The following code sample will create the merge document from a blank document, add the merge fields, and merge the data.

Private Sub cmdMailMerge_Click()
Dim objWordApp As Word.Application
Dim objWord As Word.Document
Dim oSel As Word.Selection
Set objWordApp = CreateObject("Word.Application")
Set objWord = objWordApp.Documents.Add

' Make Word visible.
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:="C:Program FilesMicrosoft " & _
"OfficeOffice11SamplesNorthwind.mdb", _
LinkToSource:=True, _

Connection:="TABLE Customers", _
SQLStatement:="Select * from [Customers]"
'Add fields
With objWord.MailMerge.Fields
    Set oSel = objWord.Application.Selection

    .Add oSel.Range, "CompanyName"
    oSel.TypeParagraph
    .Add oSel.Range, "Address"
    oSel.TypeParagraph
    .Add oSel.Range, "City"
    oSel.TypeText ", "
    .Add oSel.Range, "Country"
    oSel.TypeParagraph
    oSel.TypeParagraph
    oSel.TypeText "Dear "
    .Add oSel.Range, "ContactName"
    oSel.TypeText ","
    oSel.TypeParagraph
    oSel.TypeParagraph
    oSel.TypeText " Replace with your pithy text."
    oSel.TypeParagraph
    oSel.TypeParagraph
    oSel.TypeText "Sincerely, [Your Name Here]"
End With
    ' Execute the mail merge.
    objWord.MailMerge.Execute
    objWord.Close (0)
Set oSel = Nothing
Set objWord = Nothing
Set objWordApp = Nothing
End Sub

When populating the Word document with the message text, it's important to know that the TypeParagraph method of the selection object inserts a carriage return in the document. The TypeText method pretty simply types the specified text on the screen. You can build your entire letter this way line by line. If you want to give your users the ultimate flexibility, put a text box on your form and allow the users to type their text right on the form. Then set a string variable to the text box's text property and then use that same variable to populate the letter within your VBA code. When you're done executing the previous code, you'll wind up with a letter similar to the one shown in Figure 15-9.

15.4.3. Non-Mail Merge Operations—Sending Access Data to Word

In addition to using VBA within Access to create a mail merge, you can also export just about any information within Access to Word using VBA and the Word object model. As a short example, the following code uses the CommonDialog control to allow a user to export an Access report to a specific Word document. You might want to do this if you have users who need to modify the report or need to perform other operations such as sending an external document to the report or e-mailing the report to others in an editable format.

Dim objWordApp As Word.Application
Dim objWord As Word.Document

Figure 15.9. Figure 15-9

Dim strFilePath As String
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName
DoCmd.OutputTo acOutputReport, "Samples", acFormatRTF, strFilePath
Set objWordApp = CreateObject("Word.Application")
Set objWord = objWordApp.Documents.Open(strfilePath)

' Make Word visible.
objWord.Application.Visible = True
Set objWord = Nothing
Set objWordApp = Nothing