Convert Excel to PDF before sending out automatically by email

Question

Hello Everybody,

I am currently trying to create an Excel macro that converts my excel sheet into a PDF file and then sends it out by email. My current macro can do this, but it cannot convert it to a PDF file. I tried to merge my current VBA formula with the one here:

to convert my excel sheet into a PDF file before sending it out. However, this did not work (I couldn't get this formula to work singlehandedly either).

Can somebody please help me in improving my formula so it converts my file into a PDF before it sends it out by email?

My current VBA looks like this:

Sub Email_From_Excel_Basic()

Dim emailApplication As Object

Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")

Set emailItem = emailApplication.CreateItem(0)

' Now we build the email.

emailItem.To = "******@gmail.com"

emailItem.Subject = "Subject line for the email."

emailItem.Body = "The message for the email."

' Send the Email

' Use this OR .Display, but not both together.

emailItem.Send

' Display the Email so the user can change it as desired before sending it

' Use this OR .Send, but not both together.

'emailItem.Display

Set emailItme = Nothing

Set emailApplication = Nothing

End Sub

If any of you should require further information, more photos or anything else, please let me know and I will respond immediately!

Thank you so much :)

Best regards,

David Hansen

Answer 1

Like this:

Sub Email_From_Excel_Basic()

    Dim emailApplication As Object

    Dim emailItem As Object

    Dim strPath As String

    ' Build the PDF file name

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

    ' Export workbook as PDF

    Worksheets("Sheet1").ExportAsFixedFormat xlTypePDF, strPath

    Set emailApplication = CreateObject("Outlook.Application")

    Set emailItem = emailApplication.CreateItem(0)

    ' Now we build the email.

    emailItem.To = "******@gmail.com"

    emailItem.Subject = "Subject line for the email."

    emailItem.Body = "The message for the email."

    ' Attach the PDF file

    emailItem.Attachments.Add strPath

    ' Send the Email

    ' Use this OR .Display, but not both together.

    emailItem.Send

    ' Display the Email so the user can change it as desired before sending it

    ' Use this OR .Send, but not both together.

    'emailItem.Display

    Set emailItem = Nothing

    Set emailApplication = Nothing

    ' Delete the PDF file

    Kill strPath

End Sub

Answer 2

Amazing!

Thank you so much Hans. Your assistance has truly been appreciated, I won't forget this! Please have a fantastic day :)

Best regards,

David