Word VBA Macro > Create DOC to PDF & Email

Aren’t users a high maintenance bunch? I swear my job would be so much better if it wasn’t for the users. 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. Apparently ‘Save As’ & re-attaching was too laborious for them to manage with the amount of conversion & messages they need to send. 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 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

Word 2010 Macro Button

2: Macro executes & prompts for the filename to use:

Filename Prompt for Word PDF Macro

Filename Prompt for Word PDF Macro

3: Attachment is added with correct name to blank email:

Outlook Message With PDF Attachment

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)

'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

This entry was posted in Macro, MS Word, VBA and tagged , , , . Bookmark the permalink.

10 Responses to "Word VBA Macro > Create DOC to PDF & Email"

Leave a Reply