Search code examples
excelvbamailmerge

How to set Email and Subject on MailMerge from VBA

I have an Excel spreadsheet with VBA to automate a mailmerge, and it's falling at the final hurdle. This is the final code to execute the mailmerge, based on a macro recording:

 mWordDoc.MailMerge.Destination = wdSendToEmail
 mWordDoc.MailMerge.SuppressBlankLines = True
 mWordDoc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
 mWordDoc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord
 mWordDoc.MailMerge.Execute

It fails because I have not specified the email address field. When I do it through Word, the Email field is pre-populated with the Email column, it just seems to know what to use. I cannot find out how to specify via VBA which column to use as the email address. Also I can't set the Subject from VBA.

Solution

  • Use mWordDoc.MailMerge.MailSubject, mWordDoc.MailMerge.MailAddressFieldName (and you will need to pay attention to the MailFormat and SendAsAttachment).

    These are per-merge values - to change the Subject (in particular) per email you have to use the MailMErge Events (only available on Windows versions Of Word)