If you want to set certain Outlook fields when you do a mail merge, you need to use an ItemSend rule.
To use, open the VBA Editor using Alt+F11 and expand Project1 until you see ThisOutlookSession. Double click on ThisOutlookSession and paste the macro in the right pane.
Updated July 26 2017: Changed the macro to use a codeword in the subject and remove the codeword before sending.
This macro is not limited to mail merges; it checks the subject of every message you send to see if it meets the condition of the If statement.
To avoid running the macro on all messages you send, I'm using a codeword in the subject. If the word is found, Outlook removes it from the subject and runs the macro on the messages, adding attachments and setting other fields as needed.
If you want to change the codeword, choose something unique that you would not expect to use in an email. Remember: the codeword is case-sensitive. What you use in the macro is also what you'll use in the subject field.
If you are setting a reminder, you'll want change the reminder date and time. You can set the reminder to a specific date and time using #10/30/2012 8:00:00 AM# format or use Now + 1 for this time tomorrow (or any number of days in the future). If you use the deferred delivery option, you can use SendAt + 1 to set a reminder for 24 hours after the deferred send time.
Recipients may have rules to remove the reminder, flag, and Importance fields.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
If InStr(LCase(Item.Subject), "[merge]") Then
'Defer delivery time
SendAt = DateSerial(Year(Now), Month(Now), Day(Now) + 1) + #7:00:00 AM#
With Item
.Subject = Replace(.Subject, "[merge]", "")
.Importance = olImportanceHigh
.VotingOptions = "I accept;I decline;I don't care"
.ReminderSet = True
.ReminderTime = SendAt + 1 '#10/30/2012 8:00:00 AM#
.FlagRequest = "Please reply by " & .ReminderTime
.DeferredDeliveryTime = SendAt
' to add an attachment, enter the path & name here
.Attachments.Add "C:\Users\username\Documents\TEST.xlsx"
End With
End If
End Sub
If you want to use a different attachment for each person, you'll need to use attachments that are the same name as a message field - the To field will probably be the easiest, although prone to problems. ("To" is the display name, not the email address.)
Item.Attachments.Add "D:\For merge\" & Item.To & ".docx"
Or use one of the utilities listed at Using Mail Merge in Outlook
Video Tutorial
Hi Diane, firstly thank you the video clip made it easy to understand and the use of a keyword was quite ingenious. I am not a VBA programmer, I've cut down your original code and am trying to use the code with a different email account which I have permission for in Outlook 2016. Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean) If InStr(LCase(Item.Subject), "[merge]") Then With Item .Subject = Replace(.Subject, "[merge]", "") ' to add an attachment, enter the path & name here .Attachments.Add "C:\Users\username\Documents\TEST.xlsx" 'You can use .SentOnBehalfOfName property of outlook to send mail in the name of other user .SentOnBehalfOfName = "user@domain.com" .Display .Send End With End If End Sub At the moment the sent on behalf will only work if I take Outlook offline and physically open the email then manually send it, then go back online. Ideally, I don't want there to be any need for manual intervention. I've read up a bit and other posts say that the command SentOnBehalfOfName needs to be set first, in all the other examples it has been done in the context of creating something new, I can't work out the correct syntax for this code as it… Read more »
HI Diane - thanks for this! For some reason my subject line is still featuring [merge]. Could you help me figure out what I'm doing wrong?
.Subject = Replace(.Subject, "[merge]", "") is what I'm using in my code. Thanks!
[merge] user for mail merge when you doing mail merge
Diane: I have copied the vba text as shown above in my Outlook sessions and even put the test .xlsx file in the file you show in the video. Obviously, I am doing something incorrectly, because it sends immediately instead of going to my Outbox and the attachment is never attached. I have about 200 e-mails that need the same attachment so I it would be helpful if I could get this to work. I have Outlook 2010 and Windows 10.
This is pretty amazing. Thank you!
Quick question. Is it possible to have 2 or more macros?
So let's say I have an "instructor checkin" for one and then a "course evaluation" down the road that will have a possible attachment. Is that possible?
Thank you again!
Theresa
Yes, it is possible. Exactly how you'd do it will depend on your needs - the usual way is to use if statements to change which attachments or settings are applied.
Great idea, but it's not working for me. I'm using Outlook 2007 and Exchange 2010. When I use the script, something is placing single quotes around every email which return as undeliverable...Any thoughts? Thanks!
Are the addresses messed up if you send without using the macro? Set outlook offline or disable the internet, generate the messages then open some from the outbox to check the address. Delete before going back online.
It's not uncommon for outlook to wrap addresses in single quotes but the macro doesn't touch the address field, so its more likely outlook doing it.
The emails weren't wrapped in single quotes if I send without the macro... So I googled outlook single quotes. This post suggests a change in auto-correct (uncheck "change straight quotes to smart quotes") that seems to work!
Interesting... i don't know why the editor settings would apply to the address fields but will look into it. (I started turning that option off years ago, which could explain why i haven't had a problem with the quotes in years.)
This is amazing, thank you Diane! :)
Is there any way to change the subject line so that it can say something besides "mail merge subject?" I changed it in the code so that it said "Test" instead of "mail merge subject" and the macro didn't work anymore.
in this line of the macro:
If InStr(LCase(Item.Subject), "mail merge subject") Then
change the subject to what you are using for a subject.
This allows you to use send other mail while the macro is present and only have it apply to specific messages.
it would also be possible to do something like '[merge] my real subject' as the message subject and remove [merge] as it's sent. (will add that version to the article)
I attempted to use this in Outlook 2013 but cannot get it to work. What changes need to be made?
What happened when you tried it? (It works in 2013/2016 - all versions from 2007 and up actually).