A while back, I had a request to automate the way users can create a PDF file from MS Word, and attach it to an email – essentially create a Word to PDF macro, with automatic email creation. Currently their entire process would be:
- Save to PDF (File > Save As > .PDF / File > Print > Adobe PDF)
- Choose target folder & save
- Rename newly created PDF (as it creates with the source Word document’s name)
- Attach to Outlook & send
- Delete the PDF from disk
Also, part of the problem was the way that Outlook locks up when the ‘Send to Email’ function is initiated from Word, so you need to save your new message as a draft in order to switch back to the main Outlook view & find a recipient’s email address, for example. I was looking for ways around this & came up with the below VBA code with the help of a colleague, saved inside each user’s normal.dotm file thereby applying to every document they create from our CRM.
The macro was added to their quick launch toolbar in Word 2010 (with a user-friendly envelope icon) via login scripts replacing their normal.dotm, allowing them a one-click option to initiate the code & be presented with the filename prompt. I used H:\ as the target location because every user has the home drive by default, and some users would be using this within Citrix without permission to the system C:\
The process is now as follows:
1: User completes their Word document & hits the shortcut to run the EmailPDF macro:
Word 2010 Macro Button
2: Macro executes & prompts for the filename to use:
Filename Prompt for Word PDF Macro
3: Attachment is added with correct name to blank email:
Outlook Message With PDF Attachment
Full code is below, this can obviously be cleaned up a little & offer friendlier error messages, but this wasn’t a priority for me – if the user cancels the process halfway through & gets a generic error message, then so be it.
You will also need to add a reference in Word to the Microsoft Outlook 14.0 Object Library to get this to work. If you find this useful, please leave me a comment because I would love to know. Any suggestions to improve the below would also be welcomed.
Should you wish to do the same function in Excel, please see this post.
Sub EmailPDF()
Dim strData As String
Dim ola As Outlook.Application
Dim maiMessage As Outlook.MailItem
Dim fs strData = InputBox("Please Enter Filename")
strData = "h:\" & strData & ".pdf"
'Creates a PDF and stores it locally
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
strData, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
Item:=wdExportDocumentWithMarkup, IncludeDocProps:=False, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False
On Error Resume Next
'Start Outlook if it isn't running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
End If
'Create a new message
Set oItem = oOutlookApp.CreateItem(olMailItem)
oItem.Display
'Add attachment
oItem.Attachments.Add strData
'Create a file system object to delete temporary file
Set fs = CreateObject("Scripting.FileSystemObject")
fs.deletefile strData
End Sub
By Jorge Matos June 19, 2013 - 9:14 AM
I did a search and found your example, which is very good but I have to change a bit to suit my situation.. I use late binding method and also I keep the PDF for archiving reasons and use a common inbox (sent from) for my users..
Sub EmailPDF()
Dim appOutLook As Object ‘ We use late binding method as we don’t know the version of MS Word they are using but it has to be 2007 or greater.
Dim MailOutLook As Object
Dim MyFileName As String
MyFileName = ActiveDocument.Name ‘ Document name will be the same as the active document
MyFileName = Left([MyFileName], InStr(1, [MyFileName], “.”) – 1) & “.pdf” ‘ Document name doesn’t like dots (.) so they will be taken out if found
On Error Resume Next
ActiveDocument.Save ‘ save the active doc before PDF
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
ActiveDocument.Path & “\” & MyFileName, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False ‘ Word Commands to PDF and save the doc
Set appOutLook = GetObject(, “Outlook.Application”)
If Err.Number 0 Then
Err.Clear
Set appOutLook = CreateObject(“Outlook.Application”)
Set MailOutLook = appOutLook.CreateItem(0)
If Err.Number 0 Then
MsgBox “Cannot get Outlook”
Exit Sub
End If
End If
appOutLook.GetNamespace(“MAPI”).Logon
Set MailOutLook = appOutLook.CreateItem(0) ‘ Start Outlook and open a new email
With MailOutLook
.SentOnBehalfOfName = “support@mybusiness.com.au”
.To = “”
.Subject = MyFileName
.Body = “”
.Attachments.Add ActiveDocument.Path & “\” & MyFileName, olByValue, 1, MyFileName
.Display ‘ Show new Outlook email window
End With
‘ Kill ActiveDocument.Path & “\” & MyFileName ‘ Deletes the PDF, in this case we want a copy to be saved in the archive folder
Set MailOutLook = Nothing
Set appOutLook = Nothing
End Sub
Thank you for your sample… by the way this bit did not work for me
Dim fs strData = InputBox(“Please Enter Filename”)
strData = “h:” & strData & “.pdf”
Cheers
By Nick June 19, 2013 - 11:02 AM
Thank you for taking the time to post Jorge, I’m glad the code was helpful to you & appreciate your feedback
By Kristy June 28, 2013 - 10:50 AM
Hi Nick,
I have been trying to find how to do this for awhile. I have some very basic computer users who i require to attach a document in pdf version to an email and send so making it one step is going to make my life much easier. I have two questions though
1. how do i add a reference to Microsoft outlook 14.0
2. Can i create a way to pull an email address from within the word doc and that automatically populates my Send to:
Many thanks for the help and thank goodness there are other lazy people out there who want one button to do it all for them!!
By Nick June 30, 2013 - 9:59 PM
Hey Kristy,
Laziness is a fantastic motivator…
I’ve added some instructions here for you:
http://stott.asia/add-a-reference-to-the-microsoft-office-object-library/
If you add names to the ‘Requested By’ or ‘Approved By’ fields on the ‘Users’ tab & they match names in your Global Address List or contacts, it will match these when the outlook message is generated & sent – Alternatively you could list entire email addresses in here & have them added that way.
Hope this info helps
Nick
By Kristy July 1, 2013 - 11:21 AM
Nick, thank you for your response. Might i push the friendship one more time. What do i need to do if it was an excel document i was wanting to pdf then automatically attach to an email?
By Nick July 1, 2013 - 7:27 PM
I’m really sorry, it would seem I’ve gotten muddled thinking you were replying to this other post, hence my comments about adding email addresses to worksheets:
http://stott.asia/macro-enabled-purchase-order-template/
I’ve edited the code from that post down to be basically the same function as this post using Excel. The ‘CCAddress’ parts are commented out, I’ve left them in there to show you how you can use the value of a cell to drive the variables.
Edit – Removed the code into it’s own post & tidied it up a little – Please see here http://stott.asia/excel-vba-macro-create-xls-to-pdf-email/
By Veronica Lee June 29, 2014 - 11:32 AM
Hello. I was looking for code that would allow users to (1) convert all the open documents to PDF and (2) attach them to an email. Your code gives me some ideas; thank you.
You can add an icon to the Quick Access Toolbar that will send the current document to Outlook.
1. From Word 2010, click File, Options, Quick Access Toolbar,
2. Under “Choose commands from,” click the drop down and select
“All Commands.”
3. Scroll the list and select “E-mail as PDF Attachment.”
4. Click the Add button (in the middle of the screen).
5. Click OK.
Click the button to send the current document to Outlook. If Outlook is not running, it will start, open a new email message and attach the document with its current name.
There is another icon in the same command list that will attach the current document to an email; the icon exists in Excel and PowerPoint.
By Mario Adams March 6, 2015 - 12:03 PM
Hello Everyone
Could someone can help me with a macro like this but mi situation is that I have several pages in one document and I need to create independent files from each page and name the file like the first row of every page
Is that possible?
Thanks and Regards
By Ben March 15, 2015 - 10:11 PM
Hello
I’m having issues saving the files to my c drive? It says it’s read only. Is there an easy way around this?
I did get it to save once when creating a read only file in C. For example If I change the strData to “c:\Users” or something like that, if it does work it added everything after the “\” as part of the file name which is not ideal.
By Nick March 16, 2015 - 1:03 PM
Hi Ben,
I’ve only used C:\ as an example here, in our environment we actually use H:\ (user’s roaming network share), but you can substitute any file / path to the code.
Your Operating System is protecting the root of C:\, so you’ll need to pick somewhere else where the file can write out to… I’d suggest using %appdata%\Temp or something similar, where there will be no permissions issues & it will work for any logged on user.
Best regards,
Nick