Performing the Mail Merge

After users select the form letter and its recipients, they click on the OK button to retrieve the records specified and to merge them with the Word document. The code that handles this is in the cmdOK_Click subroutine.

This subroutine uses the AdvancedFilter method to retrieve the specified records. This code is nearly identical to that used in the previous section, except that it requires a CriteriaRange argument. which contains the criteria for selecting records.

BulkMail's criteria range is a single column whose first entry indicates the selected option button: Last Names, Companies, or States. The remaining entries indicate the items that users selected from the list box. The AdvancedFilter method retrieves records that match these criteria and copies them into the CopyToRange.

After selecting the recipients' records, BulkMail uses OLE automation to perform a mail merge in Word. Although Word normally lets you use a Microsoft Excel database as the data source for a mail merge, it doesn't let you do this under OLE automation. Why? Because Word itself uses DDE to communicate with Microsoft Excel during such a mail merge, and you can't DDE Microsoft Excel from inside an OLE automation operation controlled by Microsoft Excel.

As a workaround, BulkMail creates a temporary Word document (called TMP.DOC), paste the selected records from Microsoft Excel into this document, performs the mail merge, and then deletes the temporary file.

The code for cmdOK_Click follows. The WordBasic code is non-bold so it's easy to spot.

Sub cmdOK_Click()
On Error GoTo OKError

    Dim i As Integer
    Dim iRow As Integer
    Dim strDocDirectory As String
    Dim strFormLetter As String
    Dim objAddresses As Object

'Read selected names from list box and use these as
'criteria for AdvancedFilter method.
    iRow = 2
    objDatabase.Cells(iRow, 12).Value = strField
    With objCriteriaList
        For i = 1 To .ListCount
            If .Selected(i) Then
                iRow = iRow + 1
                objDatabase.Cells(iRow, 12).Value = .List(i)
            End If
       Next i
    End With
    
'Run advanced filter.
    With objDatabase
        .Range("Database").AdvancedFilter Action:=xlFilterCopy, _
          CriteriaRange:=.Cells(2, 12).CurrentRegion, _
          CopyToRange:=.Cells(2, 14), Unique:=False
        
'Set object variable to extracted data range and trim off
'column headings.
        Set objAddresses = .Cells(2, 14).CurrentRegion
        objAddresses.Rows(1).Delete

'Clear criteria range.
        .Cells(2, 12).CurrentRegion.ClearContents
    End With
    
'Do mail merge with extracted data.
'Get path and name of mail merge letter.
    strDocDirectory = objDocDirectory.Caption
    strFormLetter = objDocList.Text
    
'Copy extracted data to clipboard.
    objAddresses.Copy
    
    Set objWord = CreateObject("Word.Basic")
    With objWord
        .ScreenUpdating 0
        
'Create temporary document and paste mail merge data in it.
        .FileNew Template:="Normal"
        .EditPaste
        .FileSaveAs Name:=strDocDirectory & "tmp.doc"
        
'Open the mail merge letter.
        .FileOpen Name:=strDocDirectory & strFormLetter
        
'Do mail merge.
        .MailMergeOpenDataSource Name:=strDocDirectory & "tmp.doc"
        .MailMerge CheckErrors:=2, Destination:=0, MergeRecords:=0, Suppression:=0, MailMerge:=True

'Close the mail merge letter.
        .Activate strFormLetter
        .FileClose 2

'Close the temporary document.
        .Activate "tmp.doc"
        .FileClose 2
        .ScreenUpdating 1
    End With

'Delete the temporary document.
    Kill strDocDirectory & "tmp.doc"
    
'Clear the range containing the extracted data.
    Application.CutCopyMode = False
    objAddresses.ClearContents
    Set objAddresses = Nothing
   
CleanUp:
    ClearObjectVariables
    Exit Sub
       
OKError:
    If Err <> 0 Then
        GlobalErrorMsg "cmdOK_Click", Err
        Resume CleanUp
    End If
End Sub