This is the Excel version of a previous post, Word VBA Macro > Create DOC to PDF & Email, to allow you to automatically generate a PDF attachment to a draft message in Outlook with a custom filename, from the click of a button in Excel. The current method to generate a PDF (with a custom file name) is to:

• 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 Excel document’s name)
• Attach to Outlook & send
• Delete the PDF from disk

The process is now as follows:

1: User completes their Excel document & hits the shortcut to run the EmailPDF macro:

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

3: Email message with the renamed attachment:

You will 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.

You can swap out any of the target folders etc as required, and I’ve left examples in the code to use cell references to input CC: addresses etc.

Sub EmailPDF()
 Sub EmailPDF()
 fdir = "c:\\temp\\"
 fname = InputBox("Please Enter Filename")
 fpath = fdir & fname & ".pdf"
 With Application
.EnableEvents = True
.ScreenUpdating = False
 End With

ToAddress = "email@stott.asia"
CCAddress = Range("B22")
CCAddress2 = Range("i10")
CCAddress3 = CCAddress & "; " & CCAddress2
MailSub = "Emailing " & fname

'Generate PDF document to c:\. Substitute ActiveSheet for ActiveWorkbook to PDF the entire document
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fpath, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Create Mail & attach PDF
 Set oOutlookApp = CreateObject("Outlook.Application")

'Create a new message
 Set oItem = oOutlookApp.CreateItem(olMailItem)
 With oItem
 .To = ToAddress
 .CC = CCAddress3
 .Subject = MailSub

'Bring up new mail window
 oItem.Display

'Add Attachment
oItem.Attachments.Add fpath

'Cleanup , baby
 Set OutMail = Nothing
 Set OutApp = Nothing
 Set fs = CreateObject("Scripting.FileSystemObject")

fs.deletefile fpath

End With
 End Sub