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
good info, but can you guide me through on how to select my Contacts first? I pasted the code via the outlook and run the code but it asked: "You need to select Contacts first". I need guidance from you on how to add my contact list? via .oft or word file?
thanks in advance,
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
https://www.slipstick.com/developer/send-mail-merge-contact-groups/
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.