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 = "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:
- 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
Getting compile error
User-defined type not defined on oWord as Word.Application
You need to set a reference to the Word object model in The VB Editor's Tools, References menu.
Thanks for the code, however it keeps popping out with error that I need to select the contact first. Is there a extra that needs to be done?
Hmmm. That title doesn't seem right. :) Oh, it's to do merge of selected contacts and send it From a DL. If you want to use a DL/contact group in a merge, i have a macro around here somewhere that does that too.
You'll need to select the contacts - the code sample checks the item type:
If Not TypeOf Selection.Item(1) Is Outlook.ContactItem Then
MsgBox "You need to select Contacts first!"
Exit Sub
End If
if you want to send a merge to the members of a contact group, see
I really like how this works, however, i need to send an HTML email and all that this spits out is text. I can't really tell why, so I was wondering if this is the intended behavior. Thanks!
it should use the default mail format - if not, you can force it to use HTML.
in the with omail block, add this before the .body line.
.BodyFormat = olFormatHTML
you may need to change .Body to .htmlbody.
That *did* set it to HTML in the message, but, unfortunately, still none of the formatting from the word document is showing up in the email.
looks like you'll need to copy and paste instead - https://www.slipstick.com/developer/code-samples/paste-formatted-text-vba/ shows how to do it. It's also possible to apply formatting to the text after its added to the message - this works best if you either need everything formatted the same or want to format a specific phrase or line.
Thanks for the article. I am getting a compile error on this line (Method or data member not found).
Set currentExplorer = Application.ActiveExplorer
I understand 'ActiveExplorer' is not a property of Application, but I don't know how to resolve it?
Are you using this macro in Outlook or Word? It's an Outlook macro.