PDF several ranges and email together

This is a newer and more efficient way of creating a PDF from a specific range on a worksheet, saving it and emailing it out.

** It’s important to note that you can’t select ranges from different worksheets with this code. All ranges need to be on the same worksheet. If you have different structures and it is difficult to add the range from another sheet, a way of doing this is to use diagonal pages – ie one page down and one page to the right so the columns and rows can be sized as required for your PDF creation.

Sub pdf_and_email()
FName = "[email protected]"
DDate = Format(Now(), "mm-dd-yyyy-hh-mm-ss")
Subj = "Subject of email here"


Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & FName & ".pdf", Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Send the PDF out here


emailname = FName
Set Mail_Object = CreateObject("Outlook.Application")
Set OMail = Mail_Object.Createitem(0)
With OMail
Signature = OMail.htmlbody
.Close False
End With


With Mail_Object.Createitem(o)
.Subject = Subj ' CHANGE TO SUIT
.To = emailname 'CHANGE TO SUIT
.htmlbody = "


Put the text for the email in here

Kind Regards

.Attachments.Add ThisWorkbook.Path & "\" & FName & ".pdf"
End With

Set Mail_Object = Nothing
Set OMail = Nothing


End Sub

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us