Word Mail Merge with Attachment

UPDATE #2:  Thanks to the comment by Tony, making 2 simple changes to the code eliminates the need for step (3) below.

I’ve commented out the line

Set oOutlookApp = GetObject(, "Outlook.Application")

and changed

Set olNS = oOutlookApp.GetNamespace("MAPI")

               to

' By following change eliminates the security access prompts!
'Set olNS = oOutlookApp.GetNamespace("MAPI")
Set olNS = ThisOutlookSession.GetNamespace("MAPI")

UPDATE: A critical line of code seems to have fallen off when I copied the code to the original post – I have added the line

Item.Save

to the code below.


For some reason, Microsoft has never supported mail merging of documents to email with an attachment.  Until now, I always managed to work around this limitation.  However, when we recently completed our Renaissance Case Studies document, I wanted to send out the PDF to our email distribution list.

Alas, a quick search for mail-merging with attachments returns a bunch of commercial tools, but there was only one good example of VBA code to do this in word – found here.  Unfortunately, for some reason I was not able to get that code to work and didn’t have/want to spend the time to debug it (it could very well have been some silly/simple issue on my part).  Instead I used it as a starting point for my own VBA function, which I will share below.

Before I get to the code, let me outline the steps to create the final emails:

(1) Create a normal email mail-merge document – without any attachment – and generate the emails to be sent.

(2) Execute the VBA function / Macro Set Attachment and enter the full path to the desired attachment when prompted by the dialog.

(3) You will normally be prompted with a security warning dialog for each outgoing message that you try to access.  If you are sending more than just a few emails at a time here, I suggest you that you download and run the free utility Express ClickYes to automatically click yes for you every time.

Here is the VBA code I used for the SetAttachment function/Macro.  It is provided “As is” – it worked well for me, but you’ll have to confirm and/or modify it for your scenarios.

 

Sub SetAttachment()
Dim i As Long
Dim OutlookApp As Outlook.Application
Dim Item As Outlook.MailItem
Dim Filepath As String, message As String, title As String

' This sub assumes that this macro is being run from within Outlook
On Error Resume Next
‘Set OutlookApp = GetObject(, "Outlook.Application")

message = "Enter the full path to the attachment."    ' Set prompt.
title = " Email Attachment Path"    ' Set title.

' Display message, title
Filepath = InputBox(message, title)

' iterate through all items in the Outlook Outbox
Dim olNS As Outlook.NameSpace
Dim MyFolder As Outlook.MAPIFolder
Dim count As Integer

' By following change eliminates the security access prompts!
'Set olNS = oOutlookApp.GetNamespace("MAPI")
Set olNS = ThisOutlookSession.GetNamespace("MAPI")


Set MyFolder = olNS.GetDefaultFolder(olFolderOutbox)
    For i = 1 To MyFolder.Items.count
        Set Item = MyFolder.Items(i)
        Item.Attachments.Add Trim(Filepath), olByValue, 1
        Item.Save 
        Item.Send
        count = count + 1
    Next i
    Set Item = Nothing

MsgBox count & " files have been attached."

'Clean up
Set OutlookApp = Nothing

End Sub

Digg This
Published Monday, April 6, 2009 10:19 AM by Jackie Goldstein
Filed under: , ,

Comments

Monday, April 6, 2009 3:25 PM by Jackie Goldstein

# re: Word Mail Merge with Attachment

JP -

(1) I generally prefer to use early binding

(2) Your GetOutlookApp() function is certainly the way to go for a general purpose method.  My code was really meant for my specific needs, where I already have Outlook open and execute the function from within Outlook.

Tuesday, April 7, 2009 5:54 PM by SK

# re: Word Mail Merge with Attachment

Thanks, Jackie.  I got the Macro to work fine, but was a little frustrated with the messages in the Outbox.  After the attachment is made, the messages are no longer designated to be sent.  This is remedied by clicking on each individual message in the Outbox and selecting send.  However, this defeats much of the automation process.  Any solutions?

Monday, April 13, 2009 9:29 AM by Tony Thampan

# re: Word Mail Merge with Attachment

Thanks for the code .. really helped me.

1) To get rid of the annoying Oks, if you are running this from outlook, you can use

Set olNS = ThisOutlookSession.GetNamespace("MAPI") instead of getting the app.

2) You could add some person in CC by using Item.CC = "x@x.com"

Wednesday, April 22, 2009 2:22 PM by Jackie Goldstein

# re: Word Mail Merge with Attachment

Tony,

Great tip - thanks!

Thursday, May 14, 2009 3:35 AM by Susanne

# re: Word Mail Merge with Attachment

I'm rather unexperienced with macros. How do I change the reference to Outlook? I see you wrote the code, but how do I use it?

Wednesday, May 20, 2009 3:50 PM by Jackie Goldstein

# re: Word Mail Merge with Attachment

Robert,

Make sure you have a Reference set to the Outlook Object Library.  In the VB Editor, go to Tool | References and make sure to check  Microsoft Outlook 12.0 Object Library.