Dynamic Subject Line Email Merge
I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:
Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
.MailSubject = "Your Refund " & ActiveDocument.MailMerge.DataSource.DataFields("Re turn_code").Value
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail using this code:
Dim i As Integer
Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
i = .DataSource.DataFields.Count
Do While i 0
.MailSubject = "Your Refund " & ActiveDocument.MailMerge.DataSource.DataFields("Re turn_code").Value
i = i - 1
Loop
End With
End With
End Sub
But keep receiving the message the data member (.DataSource) is not found.
Where am going wrong?
Regards,
Michael
|