I have a mail merge function which is supposed to merge the records (multiple) from a query (in which the user selects different criteria) into a letter in Word.
The merge works - except that it only merges the first record. I want to be able to merge all the results of the query into the document, producing several personalized letters. The code is outlined below, if anyone can help me figure out what I'm doing wrong, it would be greatly appreciated.
Private Sub cmdPrintLetterReport_Click()
Dim Whereclause As String
If Not IsNull(Me.cboFilterLocalNumber) Then
Whereclause = Whereclause & "([Local#] = """ & Me.cboFilterLocalNumber & """) And "
End If
If Not IsNull(Me.cboFilterLocalType) Then
Whereclause = Whereclause & "([LocalType] = """ & Me.cboFilterLocalType & """) And "
End If
If Not IsNull(Me.cboFilterAgeTo) Then
Whereclause = Whereclause & "([Age] < " & (Me.cboFilterAgeTo + 1) & ") AND "
End If
If Not IsNull(Me.cboFilterPartyAffiliation) Then
Whereclause = Whereclause & "([PartyAffiliation] = """ & Me.cboFilterPartyAffiliation & """) And """
End If
lngLen = Len(Whereclause) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
Whereclause = Left$(Whereclause, lngLen)
Debug.Print Whereclause
Me.Filter = Whereclause
Me.FilterOn = True
End If
Dim AddyLineVar As String, SalutationVar As String
SalutationVar = "Dear Friend and Brother;"
AddyLineVar = AddyLineVar & vbCrLf & [Mailing Name]
AddyLineVar = AddyLineVar & vbCrLf & [AddressLine1]
If Not IsNull([AddressLine2]) Then
AddyLineVar = AddyLineVar & vbCrLf & [AddressLine2]
End If
AddyLineVar = AddyLineVar & vbCrLf & [City State Zip]
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")
Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "\FPTC Letter Template.dotx"
Wrd.Documents.Add MergeDoc
Wrd.Visible = True
With Wrd.ActiveDocument.Bookmarks
.Item("TodaysDate").Range.Text = Date
.Item("AddressLines").Range.Text = AddyLineVar
.Item("Salutation").Range.Text = SalutationVar
End With
End Sub
Thanks!