Mail merge and send from a distribution group

Last reviewed on September 2, 2014   —  No comments yet

Can you use a distribution group as sender when sending mail merge using Microsoft Word and Outlook?

No, you can't send the merge from other addresses using Word's mail merge function. You cannot use a distribution group when you send a mail merge because mail merges use the default email address in Outlook. If the address belonged to a shared mailbox, you can send a mail merge from a shared mailbox address by creating a profile for the shared mailbox.

When you use a macro to complete the mail merge, you will be able to send from other email addresses. As a bonus, you can add attachments to the messages as you merge.

Merge to email

To use: Create a document and insert merge fields. This code looks for merge fields for the first, last, and company names but you can add more fields if you need them. Select the contacts you're sending the merged document to then run the macro.

For testing purposes, the macro displays the merged messages on the screen. To send the messages automatically, change .Display to .Send.

Public Sub MailMergeAttachments()
    
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim oContact As ContactItem
    Dim oMail As MailItem
    
    Dim attach As Attachment
    Dim obj As Object
    Dim filename As String
    Dim imagePath As String
    Dim oWord As Word.Application
    Dim tmp As String
    ' Uses current user's profile
    Dim enviro As String
    enviro = CStr(Environ("USERPROFILE"))
    On Error Resume Next

    Set oWord = GetObject(, "Word.Application")

' document is open on screen
 oWord.Documents(1).Activate
 
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
 
  If Not TypeOf Selection.Item(1) Is Outlook.ContactItem Then
  MsgBox "You need to select Contacts first!"
  Exit Sub
  End If
  
For Each obj In Selection

'Test for ContactGroups
 If TypeName(obj) = "ContactItem" Then
 Set oContact = obj
 
Dim mText As String
Dim f As Word.Field

For Each f In oWord.Documents(1).Fields

  If f.Type = wdFieldMergeField Then
      
 ' match Word mergefields with Outlook fields
      Select Case f.Code
       Case " MERGEFIELD First "
       mText = oContact.FirstName
       
       Case " MERGEFIELD Last "
       mText = oContact.LastName
       
       Case " MERGEFIELD Company "
       mText = oContact.CompanyName
                
       End Select
       
     f.Result.Text = mText
 End If

Next
 
Set oMail = Application.CreateItem(olMailItem)

With oMail
    .To = oContact.Email1Address
  ' trims .docx off a file name to use as subject
    .Subject = Left(oWord.Documents(1).Name, Len(oWord.Documents(1).Name) - 5)
  ' The content of the document is used as the body for the email
    .Body = oWord.Documents(1).Content
    .Attachments.Add enviro & "\Dropbox\file.txt"
    .SentOnBehalfOfName = "sales@domain.com"
  ' use display for testing, change to .send to send automatically. 
    .Display ' .send
End With
 
 End If
    Next
    Set oWord = Nothing
    Set currentExplorer = Nothing
    Set obj = Nothing
    Set Selection = Nothing

End Sub

How to use macros

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

Leave a Reply

If the Post Coment button disappears, press your Tab key.