Am Outlook user had a question:
I'm trying to send a mail merge from multiple people. I was wondering if there was a way to choose the sender address based on the recipient email address? I'm using an Excel file as the source of the merge and the sender names associated with the recipients in the excel file so I can add a field after the closing line in the document to have the senders name appear.
No, Outlook can’t do this, at least not as a native feature within the mail merge. Mail merges will always be sent from the default email account. However, it is possible if you use a macro to handle the merge, not the mail merge wizard.
This macro reads values from an Excel worksheet and sends a mail merge, replacing unique values in the Outlook template with values in the worksheet. It also sends the message From an address in the worksheet.
Create an Outlook template, entering unique values where the merge fields would be entered. While you could use standard merge fields or bookmarks, you would need to use the Word Object Library to update the fields. Using unique values allows us to use VBA's Replace function.
The finished merge will look like the following. (Yes, I know, I'm not creative with demo values and prefer to use Excel's features to create demo values.)
' This is an Excel macro
' you need to set a reference to Outlook Object Library
Public Sub SendMailMergeExcel()
Dim olApp As Object
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim appdata As String
Dim strPath As String
Dim strAttachPath As String
Dim SendTo As String
Dim CCTo As String
Dim strSubject As String
Dim strAcctMgrName As String
Dim AcctMgrEmail
Dim olItem As Outlook.MailItem
Dim Recip As Outlook.Recipient
' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
appdata = CStr(Environ("appdata"))
On Error Resume Next
Set xlApp = Excel.Application
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.ActiveWorkbook
Set xlSheet = xlWB.Sheets("Sheet1")
' Process the message record
On Error Resume Next
rCount = 2
strAttachPath = enviro & "\Documents\Send\"
Set olApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set olApp = CreateObject("Outlook.Application")
bXStarted = True
End If
Do Until Trim(xlSheet.Range("A" & rCount)) = ""
strFirstname = xlSheet.Range("A" & rCount)
SendTo = xlSheet.Range("B" & rCount)
CCTo = xlSheet.Range("C" & rCount)
strSubject = xlSheet.Range("D" & rCount)
' if adding attachment
'strAttachment = strAttachPath & xlSheet.Range("E" & rCount)
strAcctMgrName = xlSheet.Range("F" & rCount)
AcctMgrEmail = xlSheet.Range("G" & rCount)
'Create Mail Item and view before sending
' Default message form
'Set olItem = olApp.CreateItem(olMailItem)
' use a Template
Set olItem = olApp.CreateItemFromTemplate(appdata & "\Microsoft\Templates\macro-test.oft")
With olItem
.SentOnBehalfOfName = AcctMgrEmail
.To = SendTo
.CC = CCTo
.Subject = strSubject
.Body = Replace(.Body, "[FirstName]", strFirstname)
.Body = Replace(.Body, "[AcctMgrEmail]", AcctMgrEmail)
.Body = Replace(.Body, "[strAcctMgrName]", strAcctMgrName)
if adding attachments:
'.Attachments.Add strAttachment
.Save
.Display
'.Send
End With
rCount = rCount + 1
Loop
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
How to use the macros on this page
First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.
To check your macro security in any Office 2010 application and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In older versions look 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.
Set a reference to other Object Libraries
If you receive a "User-defined type not defined" error, you need to set a reference to another object library.
- Go to Tools, References menu.
- Locate the object library in the list and add a check mark to it.
More information as well as screenshots are at How to use the VBA Editor
Lanny Newville says
Got this working pretty quickly, thanks for the well documented example. The template I created had some formatting including use of bold, underline and an embedded logo. They were stripped from the emails that were generated by the script. Is there a way to allow the formatting and image in the final product?
Diane Poremsky says
Try changing .Body to .HTMLBody.
Marin says
Thank you, it is working just fine.
Small note, to avoid that warning window regarding the use of macro, in Trust Center Settings/Programmatic Access I had to check the option button "Never warn me about ..."
Thank you again
Areti says
Oh wow - I think you have saved me hours and hours of work. Took me a bit of time to work out the adjustments I should make to the code for field names, file location etc but this is so, so helpful. Thanks for sharing your expertise.
Carl says
hello there, I’m using the code in word mail merge to change the subject already. I was wondering if that’s possible to only copy multiple people for different people as well, yours looks much complicated as I’m just sending out the same email message to & cc different people. (e.g.: A1 as the recipient, cc people in cell A2,A3,A4, second one B1 as the recipient, cc people in cell B2,B3,B4).
I would love to hearing from you.
Jasim says
Hi
i am looking for some simple vba code to send bulk emails used a word template and data from an excel work book worksheet
the mail body drafted on the templates with dynamic merge fields
the mail subject should pull form the excel work book data sheet
it is an advanced option if can be added for selecting batch emailing like 50's or 100's also at the end of the mailing a message box pop up says "number of mails sent"
the mail sending button should be assigned on excel workbook and the template can keep in the same source folder of excel.
its is much appreciated if you can help me in this
Akhilesh Sharma says
Many Thanks for this useful code and info.
One thing i am not able to do is attachments to the mail merge. Please support.
Georgios Pyrgiotakis says
Hi Diane,
this is almost working for me. It composes the emails, but the body is empty. I have created a template and pointed to the right path, but I get blank emails.
Georgios Pyrgiotakis says
Found the issue. A small typo at the path.
Bobby Yeung says
Hello, I'm really loving your explanations!
Hopefully an easy one for you - I'm trying to add bespoke attachments for each recipient. For example, the file path for the first attachment is "C:\Users\MEtheUSER\Documents\Charlotte test.pdf". I've put this into column E as per your example above. However, nothing ever comes up! I would be so grateful if you could help solve this as I've spent the past 2 hours trying different iterations (including looking at the "strAttachPath" bit of your code).
Thank you so much.
Christian says
Hello,
This is working good so far. However, when I tried to send a test message to myself, I'm still getting the [FirstName] field. I even changed the template to {FirstName} to see if that helps; it showed the [FirstName] field. I'm not sure if I did something wrong here.
Can I please have some assistance with this?
Diane Poremsky says
Copy the text inside the quotes in the macro and paste it in the template (or copy from the template and paste in the code) - the code looks for the exact match and replaces it. It needs to be identical, so to the upper and lower case characters.
Christian says
I just tried what you had suggested and it didn't work.
I think the problem I was having came from the variable where the first name is supposed to go. It picked up everything else just fine. It's like it completely skipped the first column. I had to declare the "strFirstname" variable to see if that will make a difference.
I also changed the template name on both sides.
Christian says
(My previous question got deleted somehow, so let me try this again...)
I tried what you had suggested, but it did not work.
I was able to get my other columns just fine, but just not my 1st column oddly enough. I had to declare the "strFirstname" variable to see if that helps any; it wasn't declared before. Do you have any other suggestions for me?
Rajesh says
Dim olItem As Outlook.MailItem
Compile Error: User defined type not defined, though I have set a reference to Outlook Object Library in Excel's Tools, References dialog. Is there anyother do i need look into it?
Diane Poremsky says
That should be all you need. Does It error on a specific line?
Chris v says
Hi - The formatting (links, hyperlinks, bold and color text, etc) in my template does not show up. Everything appears as unformatted richtext. How do I keep the formatting I setup in the template?
Chitra says
Hi,
How to use the Word Object Library to update the fields. Please help
Diane Poremsky says
I'm not sure exactly what you want to do or what fields you want to update, but the processs is similar - set a reference to the other object model and properly reference it. In most cases, you can use a macro from the other program with just a few tweaks.
Izzy says
Hi,
Thank you very much for the above and it is working like a charm for me! I'm trying to change this code so that I can fill up some values in a table (That I created in the email template). When I run the Macro the email loses all the formatting including the table rows and columns and just fills the data one after the other. Is there a way to fix it? Thank you!
Diane Poremsky says
You need to change body in these lines to htmlbody
.Body = Replace(.Body, "[FirstName]", strFirstname)
.Body = Replace(.Body, "[AcctMgrEmail]", AcctMgrEmail)
.Body = Replace(.Body, "[strAcctMgrName]",
Izzy says
Thank you Diane! It did work but there were a couple of things that I had to figure out. I had to spell check the email template and ignore all errors and save it before running the Macro! Apart from that need to make sure the template is saved in HTML format. That's it! Thank you again for your help!
chhavi says
Hi,I have created a button in excel which on clicking send an email .But i want to make it much more convenient .Rather than opening Excel file every time and clicking the button,I want to create a macro in outlook mail which on clicking generate this email .Could it be done?
Diane Poremsky says
Sorry l missed this earlier - yes you can create it from Outlook.
Wes says
Hi, When I use the VBA code, it doesn't use my template. Instead this displays an unrelated draft from my email account with the email addresses from the spreadsheet. Even after I deleted the draft, the macro still creates emails from that old draft.
Diane Poremsky says
Did you change the code to use a different template ?
Set olItem = olApp.CreateItemFromTemplate(appdata & "\Microsoft\Templates\macro-test.oft")
Shreekrishna says
Hi, am getting error as "user defined type not defined" - Compiler error at "Dim olItem As Outlook.MailItem".
Could you please check
Diane Poremsky says
Did you set a reference to Outlook Object Library in Excel's Tools, References dialog? (I'll add the steps to this page.)
angela says
thank you this is helpful. what do I put in the "enviro = CStr(Environ("USERPROFILE"))" is this the path on my C drive of my user profile?
Diane Poremsky says
Yes, that is the path to your user profile - c:\users\yourname - I use it in the macros because it will use the correct folder on every computer. If the workbook is saved elsewhere, you can replace this with the correct path:
strAttachPath = enviro & "\Documents\Send\"