Access Word email merge dynamic subject VBA

[MichaelHutcheson1981]

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 = ActiveDocument.MailMerge.DataSource.DataFields("Return_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. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"

Thank you for any help.

Regards,

Michael

 

[Andrzejek]

how do I add static text to the Subject"

Try:
[tt]
...
.MailSubject = [blue]"Your Return Code " & [/blue]ActiveDocument.MailMerge.DataSource.DataFields("Return_code").Value
...
[/tt]

Have fun.

---- Andy

 

[MichaelHutcheson1981]

Yes this has worked - thank you. Now I just need to solve making the subject dynamic so it changes with every record.......

 

[MichaelHutcheson1981]

Its ok, I finally got it working, here is the code if anyone is interested:

Private Sub Document_Open()

Dim intSourceRecord
Dim objMerge As Word.MailMerge
Dim bTerminateMerge As Boolean

' If no data source has been defined, do it here using OpenDataSource.
' But if it is already defined in the document, you should not need to define it here.


' .OpenDataSource _
' Name:="whatever"

' Need to set up this object as the ActiveDocument changes when the
' merge is performed. Besides, it's clearer.

Set objMerge = ActiveDocument.MailMerge
With objMerge

' I don't use FirstRecord, LastRecord because they do not behave
' the way you expect in all data sources.

intSourceRecord = 1
bTerminateMerge = False

Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end (and possibly, if there are no records)
' then the Activerecord will not be what we have just tried to set it to


If .DataSource.ActiveRecord <> intSourceRecord Then
bTerminateMerge = True
' the record exists
Else


.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToEmail

' set up the field containing the e-mail address
ActiveDocument.MailMerge.MailAddressFieldName = "EmailAddress"
' Set up the subject - make sure any field name in here has the same
' capitalisaiton as the name in the data source

.MailSubject = "Your Refund " & ActiveDocument.MailMerge.DataSource.DataFields("ReturnCode").Value
.Execute
intSourceRecord = intSourceRecord + 1
End If
Loop
End With
End Sub

Michael