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 this Outlook macro to complete the mail merge, you will be able to send from other email addresses, including a distribution group address in Exchange Global Address List or a shared mailbox. As a bonus, you can add attachments to the messages as you merge.
To use: Create a document and insert merge fields; the content of the document is used in the merged email message. Save the document (Outlook uses the filename as the message subject) but leave the document open on the screen. Switch to Outlook, select the contacts you're sending the merge to then run the macro.
This code looks for merge fields for the first, last, and company names but you can add more fields if you need them.
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 = "firstname.lastname@example.org" ' 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:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor