Using Mailmerge to Email with VBA
Results 1 to 6 of 6

Thread: Using Mailmerge to Email with VBA

  1. #1
    Senior Member
    Join Date
    Sep 2004
    Location
    Bedfordshire
    Posts
    3,407
    Thanks
    295
    Thanked 396 Times in 244 Posts

    Using Mailmerge to Email with VBA

    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?
    Allan
    ________________________
    Do I come here often? DiFF

  2. Remove Advertisements
    Yorkshire-Divers.com
    Advertisements
     

  3. #2
    Member
    Join Date
    Oct 2008
    Posts
    288
    Thanks
    35
    Thanked 30 Times in 23 Posts
    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.

  4. #3
    Senior Member
    Join Date
    Sep 2004
    Location
    Bedfordshire
    Posts
    3,407
    Thanks
    295
    Thanked 396 Times in 244 Posts
    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!
    Allan
    ________________________
    Do I come here often? DiFF

  5. Remove Advertisements
    Yorkshire-Divers.com
    Advertisements
     

  6. #4
    Member
    Join Date
    Oct 2008
    Posts
    288
    Thanks
    35
    Thanked 30 Times in 23 Posts

    Thumbs up

    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.

  7. #5
    Senior Member
    Join Date
    Sep 2004
    Location
    Bedfordshire
    Posts
    3,407
    Thanks
    295
    Thanked 396 Times in 244 Posts
    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!
    Allan
    ________________________
    Do I come here often? DiFF

  8. #6
    New Member Hoffmann's Avatar
    Join Date
    Dec 2019
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Finally found the right settings! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •