[Allan Carr]

Do we have any VBA experts on YD? I am trying to use Mailmerge to send to a list of e-mail addresses held in an Excel workbook. I can't find how to link to link to the address table in the Word VBA Macro. I've tried using macro recorder but it doesn't record this part of the process. I presume it has to link to the mailaddressfieldname but what is the exact format?

 

[LearnerDiver]

The name of the to field in Word is ActiveDocument.MailMerge.MailAddressFieldName

Do you want to;
1. just email info from Excel
2. mailmerge docs into Word using data in Excel then email the resulting docs from Word
3. Create something reusable where you change the emails in the data source and the emailed message each time you run it

If 1:
With VBA you can just create an instance of the Outlook object in Excel and email from there, recipient is the mailitem.To property, subject and email content can be in cells in Excel

See:
Merging With Attachments
How To Automate Microsoft Word to Perform Mail Merge from Visual Basic

2. Can do same as 1 in Word or for a one off just use the mailmerge wizard to walk you through the whole process then one of the last steps is to select the field from your data source that has the email address in.

3.See first link in 1 then extend it with dialog boxes to ask you which Excel file you want to use as source.

 

[Allan Carr]

What I have is a big Excel application (20,000 lines of VBA code) which records all the information about an operation and holds within it contacts list for each referral that they get, which includes postal and e-mail addresses. This organisation needs to send out letters to the people on these lists, some by snail mail and some by e-mail. The address fields are set up in a word document, the letter is written and a VBA macro built into the document template is run to get the address lists and do a mailmerge. I've got the printed letter bit working fine but I haven't worked out how to link to the Excel table containing the e-mail addresses to add them to the mailaddressnamefield to send out the e-mails. When I record a macro, the Word VBA recorder doesn't record this bit!

 

[LearnerDiver]

There are lots of ways to do this, the simplest is probably;

1. Add a hidden field in your doc and when you mailmerge put the email address into it.

Add a mailmerge field as normal then font, hidden
​

2. Mailmerge your doc

3. Mailings, Finish and Merge, Send E-Mail messages or run VBA

4. Your emails will be sent

To automate the sending via VBA;

Assumes excel source is C:\Documents and Settings\MMerge.xlsx
Assumes excel worksheet is Sheet$
Assumes header of columns in Excel containing email is Address an name is Name
Assumes Office 2007 so may need changes for older version

Sub Email_After_Merging()

'set to send as email
ActiveDocument.MailMerge.MainDocumentType = wdEMail

'-------------------
'You may not need this section as it does the actual mailmerge
'get the data and mailmerge
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\MMerge.xlsx", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Documents and Settings\MMerge.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
Selection.TypeText Text:="To: "
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Name"
Selection.TypeParagraph
Selection.TypeText Text:="EmailAddress: "
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= "Address"
'--- end optional section -------------
​

'now send the emails
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True

'Use the field in the mailmerge as the email address
.MailAddressFieldName = "Address"

'Either hard code or use another hidden field
.MailSubject = "An Email"

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
​

From word; run macro, check sent items and your emails will be there.

 

[Allan Carr]

Many thanks for your help. However, when I looked at your code, it was very similar to mine, which was:

Sub Mailing()
Dim ContactsFN As String
ContactsList.ContactsFileName = ""
ContactsList.Show
ContactsFN = ContactsList.ContactsFileName
If ContactsFN = "" Then Exit Sub
With ActiveDocument.MailMerge
.OpenDataSource Name:=ContactsFN, LinkToSource:=True, SQLStatement:="SELECT * FROM `'Print List$'`"
.Destination = wdSendToPrinter
.SuppressBlankLines = False
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
With ActiveDocument.MailMerge
.OpenDataSource Name:=ContactsFN, LinkToSource:=True, SQLStatement:="SELECT * FROM `'E-mail List$'`"
.Destination = wdSendToEmail
.MailAddressFieldName = "email"
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub


The first bit of code is just a form which browses for the file name containing the contacts list.

I thought I was going mad but I eventually found the problem - the address field name I had was 'e-mail'. When I changed it to 'email', it all worked!

 

[Hoffmann]

Finally found the right settings! Thanks!