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 06, 2009 10:19 AM by Jackie Goldstein
Filed under: , ,

Comments

Monday, April 06, 2009 10:54 AM by Word Mail Merge with Attachment - Jackie Goldstein's Weblog

# Word Mail Merge with Attachment - Jackie Goldstein's Weblog

Pingback from  Word Mail Merge with Attachment - Jackie Goldstein's Weblog

Monday, April 06, 2009 1:05 PM by JP

# re: Word Mail Merge with Attachment

You'll need to set a reference to the Outlook object library for your code to work. Here's a way to do it with late binding:

Dim OutlookApp As Object

Set OutlookApp = GetOutlookApp

If OutlookApp Is Nothing Then Exit Sub

And here's the GetOutlookApp function. It returns Nothing if the code is unable to get or start Outlook:

Function GetOutlookApp() As Object

On Error Resume Next

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

 If Err.Number <> 0 Then

   Set GetOutlookApp = CreateObject("Outlook.Application")

 End If

On Error GoTo 0

End Function

I also like to set a global boolean flag if Outlook was started with CreateObject, so we can quit it when the code ends.

Monday, April 06, 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 07, 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?

Wednesday, April 08, 2009 2:55 AM by Jackie Goldstein

# re: Word Mail Merge with Attachment

SK,

THANK YOU! I line of code seems to have fallen off when I pasted the code into the post.  After the line Item.Save there is a line Item.Send

I have updated the code in the original post.

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 2:35 PM by Robert Lowe

# re: Word Mail Merge with Attachment

When I try using this--or the code that inspired it (from word.mvps.org/.../MergeWithAttachments.htm), the VBE throws an error: "Compile Error: user-defined type not defined" at the line:

Dim OutlookApp As Outlook.Application

And so it doesn't work. I'm using Office 2007 apps, currently patched (SP2). I'd be grateful for any insight. I'm not VB trained.

Thanks!

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.