Dynamic Subject Email Merge

[MHutcheson]

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.

Thank you for any help.

Regards,

Michael