# Field in Subject Line Mail Merge Using Word 2010



## rexhvn (Jan 7, 2016)

Hi All,

I am hoping for some assistance please. I am wanting to add a data source from my excel spreadsheet into my subject line however I do not have the add on tool for Mail Merge.

Is there any other way I can do it such as VBA code? If so, help would be greatly appreciated.

Thanks,


----------



## Macropod (Jan 8, 2016)

To do that, you would have to use Outlook as the email app, since Outlook automation would be required. The Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; and
Doug Robbins at http://bit.ly/1hduSCB
both use VBA to automate Outlook for this.


----------



## Macropod (Jan 9, 2016)

Correction:
You could do it with a macro like:

```
Sub Merge_To_Emails()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToEmail
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
      End With
      .MailAddressFieldName = "Recipient_Email_Address"
      .MailSubject = .DataSource.DataFields("Subject_Line")
      .MailFormat = wdMailFormatHTML
      .Execute Pause:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub
```


----------



## rexhvn (Jan 13, 2016)

Thank you so much Paul, much appreciated.

I use the mail merge using both excel and word using the Mail Merge Step by Step wizard, could you let me know where I should input the code? VBA via excel or Word? I've got VBA experience but not sure how this works...

Thanks you.


----------



## Macropod (Jan 13, 2016)

The macro is for Word, so you should add it to your mailmerge main document. Naturally, you will need to edit line lines:
.MailAddressFieldName = "Recipient_Email_Address"
.MailSubject = .DataSource.DataFields("Subject_Line")
to refer to your own field names.


----------



## rexhvn (Jan 28, 2016)

Macropod said:


> The macro is for Word, so you should add it to your mailmerge main document. Naturally, you will need to edit line lines:
> .MailAddressFieldName = .DataSource.DataFields("Recipient_Email_Address")
> .MailSubject = .DataSource.DataFields("Subject_Line")
> to refer to your own field names.



Thanks Paul much appreciated.

I added this Macro into my main documents and edited the fields to reflect my spread sheet. Went through the Step by Step wizard as per ususal, went to the final stage and instead of sending it normally, I run the macro but got an error.

Error: Run-Time Error '5630' Word cannot merge documents that can be distributed via mail or fax without a valid mail address. Chose the setup button to select a mail address data field.

I would also like a set a default email address that these are being sent from.

All help is appreciated.


----------



## Macropod (Jan 28, 2016)

Did you edit 'Recipient_Email_Address' as advised?

You cannot specify a default 'send' email address in code; the only way to change the default is by changing the default email account of the user sending the emails.


----------



## wordeo (Feb 14, 2019)

I found this to work when I replaced the line

.MailAddressFieldName = "Recipient_Email_Address"

with 

.MailAddressFieldName = "Email"

credit goes for the solution goes to Macropod for the original solution posted here and Bluejay07 for the edit at https://www.tek-tips.com/viewthread.cfm?qid=1714151 posted on 12 Jul 13 15:56


The following is the code I'm using


```
Sub Merge_To_Emails()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToEmail
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
      End With
      .MailAddressFieldName = "email"
      .MailSubject = .DataSource.DataFields("Subject")
      .MailFormat = wdMailFormatHTML
      .Execute Pause:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub
```

This was my first time making a macro. So note to newbies to use this code you have to click on the Macros button in word go to create a macro name that is appropriate or use 
Merge_To_Emails to keep changes to a minimum


----------



## wordeo (Feb 14, 2019)

I also had to save my word document as a .docm document forst (macro enabled)


----------



## Debbie1305 (Feb 20, 2019)

Hi folks

I have tried using this in word, can I add a CC field? When I run the macro nothing happens, which areas to I need to change to point to my source spreadsheet?

Thanks


----------



## rexhvn (Jan 7, 2016)

Hi All,

I am hoping for some assistance please. I am wanting to add a data source from my excel spreadsheet into my subject line however I do not have the add on tool for Mail Merge.

Is there any other way I can do it such as VBA code? If so, help would be greatly appreciated.

Thanks,


----------



## Macropod (Feb 20, 2019)

You need to do the basic mailmerge set-up before the macro can be used. See: https://support.office.com/en-us/ar...messages-0f123521-20ce-4aa8-8b62-ac211dedefa4.

As for the CC, mailmerges can't do those; you'd need an entirely different approach. See, for example, the 'Answer' post at: https://answers.microsoft.com/en-us...-copy-cc/8fa2684e-7a39-4bc9-a696-28d0d053ec10


----------



## wordeo (Feb 20, 2019)

I'm trying to customize my subject line based on a cell in my excel document. For example is there is a "Yes" in cell k2, I'd like the subject line to say "hello"

else if the cell J2 says yes then subject line should be "Bye"

I'd also like the cells compared to be the corresponding cells of each row

any suggestion? 
I basically still using the code from earlier


----------



## Macropod (Feb 20, 2019)

You can only have one field (column) in Excel for the Subject line. Each row in that column can be different. I have no idea what you mean by:


> I'd also like the cells compared to be the corresponding cells of each row


----------



## arehman626 (May 28, 2019)

This perfectly worked where I had a worksheet with the data along with a column with the heading as "Email" as well as another column called "Subject" and I had a concatenate formula in the "Subject" like +CONCATENATE("Re: Your order ",A2, " Placed on ",B2). Then I used the MS Word menu item "MAILINGS" - > "Select Recipients" ->  "Use an existing List" then select the Excel document containing the above information and using "Insert Merge Field" from (Write and Insert menu bar) to further customise the message body. I then opened the DEVELOPER tab and went to Macros and clicked on Create and under the Normal project, under Modules, under NewMacros, I selected and removed everything and pasted the below code
Sub Merge_To_Emails()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToEmail
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
      End With
      .MailAddressFieldName = "Email"
      .MailSubject = .DataSource.DataFields("Subject")
      .MailFormat = wdMailFormatHTML
      .Execute Pause:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub




then to run the macro, I had to go to DEVELOPER and select Macros and select the Merge_To_Emails from the drop down list and off you go.


----------



## Macropod (May 28, 2019)

arehman626 said:


> This perfectly worked where I had a worksheet with the data along with ... I then opened the DEVELOPER tab and went to Macros and clicked on Create and under the Normal project, under Modules, under NewMacros, I selected and removed everything and pasted the below code


As was previously advised - and which you seem to have disregarded, a mailmerge _cannot_ do CCs.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.


----------



## sur (May 26, 2020)

Can we add the CC filed in the above macro


----------



## Macropod (May 26, 2020)

No, because all that macro does is automate a mailmerge. And, as I already said:


> a mailmerge _cannot_ do CCs.


----------

