Send a PDF file from several Excel worksheets by e-mail

Question

I am still somewhat new to VBA, so I wanted to ask for a little tip.

I would specify the individual worksheets ("Sheet1", "Sheet2", "Sheet3") by name and send them as a single file. Currently, I can only send "Sheet1" as a PDF by e-mail. Does anyone have a tip for me?

Sub Email_From_Excel()
    Dim emailApplication As Object
    Dim EmailItem As Object
    Dim strPath As String

    strPath = ActiveWorkbook.Path & Application.PathSeparator & "Sheet1.pdf"

    Worksheets("Sheet1").ExportAsFixedFormat xlTypePDF, strPath
    Set emailApplication = CreateObject("Outlook.Application")
    Set EmailItem = emailApplication.CreateItem(0)

    EmailItem.To = Range("C8")
    EmailItem.CC = Range("C9")
    EmailItem.Subject = "XXX"
    EmailItem.Body = "Please find attached XX"


    EmailItem.Attachments.Add strPath

    EmailItem.Display

    Set EmailItem = Nothing
    Set emailApplication = Nothing
 
End Sub

Answer 1

The Outlook object model doesn't provide any method for sending Excel sheets without saving them to the disk prior. Whatever you would like to be sent via Outlook must be saved to the disk as a file. Only then you can attach such files to the email and send it out.

If you need to split worksheets and send them separately from the whole workbook you can copy them into a new workbook which can be saved to the disk and attached to the email.The Copy method the worksheet (creates a new workbook) and then save the ActiveWorkbook.

Worksheets("Sheet1").Copy

For example, the following example first copies Sheet1 to a new blank workbook, and then saves and closes the new workbook.

Worksheets("Sheet1").Copy
With ActiveWorkbook 
     .SaveAs Filename:=Environ("TEMP") & "\New1.xlsx", FileFormat:=xlOpenXMLWorkbook
     .Close SaveChanges:=False
End With

I would specify the individual worksheets ("Sheet1", "Sheet2", "Sheet3") by name and send them as a single file. Currently

If you need to send individual worksheets as PDF files you need to iterate over all worksheets in the workbook.

Or just export the workbook in the PDF format to send them all at once in the single file. The Workbook.ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format.

Sub Email_From_Excel()
    Dim emailApplication As Object
    Dim EmailItem As Object
    Dim strPath As String

    strPath = ActiveWorkbook.Path & Application.PathSeparator & "MyWorkbook.pdf"

    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, strPath
    Set emailApplication = CreateObject("Outlook.Application")
    Set EmailItem = emailApplication.CreateItem(0)

    EmailItem.To = Range("C8")
    EmailItem.CC = Range("C9")
    EmailItem.Subject = "XXX"
    EmailItem.Body = "Please find attached XX"


    EmailItem.Attachments.Add strPath

    EmailItem.Display

    Set EmailItem = Nothing
    Set emailApplication = Nothing
 
End Sub