Share via

Convert Excel to PDF before sending out automatically by email

Anonymous
2020-07-01T07:38:24+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2020-07-01T11:40:54+00:00

    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

    10 people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2020-07-01T08:35:27+00:00

    Try this version:

    Sub Email_From_Excel_Basic()

        Dim emailApplication As Object

        Dim emailItem As Object

        Dim strPath As String

        Dim lngPos As Long

        ' Build the PDF file name

        strPath = ActiveWorkbook.FullName

        lngPos = InStrRev(strPath, ".")

        strPath = Left(strPath, lngPos) & "pdf"

        ' Export workbook as PDF

        ActiveWorkbook.ExportAsFixedFormat xlTypePDF, strPath

        Set emailApplication = CreateObject("Outlook.Application")

        Set emailItem = emailApplication.CreateItem(0)

        ' Now we build the email.

        emailItem.To = "*** Email address is removed for privacy ***"

        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

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2020-07-01T12:27:33+00:00

    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

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2020-07-01T09:51:09+00:00

    Hello Hans,

    Thank you very much.

    Unfortunately, your solution has provided me with an error. Can you please assist me in fixing it?

    I am currently getting the following error: "Run-time error'-2147467259 (800004005)': Outlook does not recognize one or more names."

    When I try to debug it, it highlights the following:

    How do I fix this?

    Thank you so much for your time by the way!

    Best regards,

    David

    0 comments
  5. Anonymous
    2020-07-01T08:31:10+00:00

    Hi, I'm Elise, an independent advisor and I'd be happy to help with your issue.

    Here is an article which gives a great explanation of the code needed to achieve this:

    https://www.extendoffice.com/documents/excel/44...

    Please let me know if you need any further assistance.

    Kind Regards,

    Elise

    Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

    0 comments