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@example.com" 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