Send Sheet as a PDF attachment in Email

.

Dear Readers,

In my previous article, you saw how to save an Excel Sheet as PDF file. Now you know, how to save an Excel Sheet as a pdf. Also, we have seen how to send the ActiveSheet as an attachment in Email. In this article, I am going to show you how to send the ActiveSheet as an Attachment in PDF format. You can also download the Excel Workbook at the end of this article and play around with the code. It’s all yours 🙂

<< Go Back to the Excel Tutorial Page


Below is the code, which works at the below logic:

1. Save the ActiveSheet as PDF in Temporary Folder of the System
2. Attach the File in Outlook New Email
3. Send the Email
4. Delete the PDF file from the Temp Folder.


Sub Email_ActiveSheet_As_PDF()
    
'Do not forget to change the email ID
'before running this code
    
    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
' Temporary file path where pdf
' file will be saved before
' sending it in email by attaching it.
    
    TempFilePath = Environ$("temp") & "\"
    
' Now append a date and time stamp
' in your pdf file name. Naming convention
' can be changed based on your requirement.
    
    TempFileName = ActiveSheet.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
    
'Complete path of the file where it is saved
    FileFullPath = TempFilePath & TempFileName
    
'Now Export the Activesshet as PDF with the given File Name and path
    
    On Error GoTo err
    With ActiveSheet
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With
    
'Now open a new mail
    
    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)
    
    On Error Resume Next
    With NewMail
        .To = "info@learnexcelmacro.com"
        .CC = "info@learnexcelmacro.com"
        .BCC = "info@learnexcelmacro.com"
        .Subject = "Type your Subject here"
        .Body = "Type the Body of your mail"
        .Attachments.Add FileFullPath '--- full path of the pdf where it is saved
        .Send   'or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0
    
'Since mail has been sent with the attachment
'Now delete the pdf file from the temp folder
    
    Kill FileFullPath
    
'set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing
    
'Now set the application properties back to true
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox ("Email has been Sent Successfully")
    Exit Sub
err:
        MsgBox err.Description

End Sub

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

30 Comments

  1. Vikram

    Receiving an error msg while running the code.

    "Invalid procedure call or argument"

    Am i doing wrong?

    Reply
  2. Ian

    I just copied this code and run play/start then received in my inbox the PDF copy of the sheet i was doing. Thanks!
    Just one more thing. How do I automatically run the macro. As soon as my file is opened, data has to be updated first before sending the PDF copy in the Email. Your expertise will be appreciated.

    Reply
  3. D R Ganesh

    i complete work at ms office because i want to learn more about macros

    will u please updated me by email address.

    thanks and regards
    ganesh

    Reply
  4. Pradeep

    Sir pls send file how to learn macro from beginning

    Reply
  5. Trevor

    Hi
    have tried the above code it sends fine but will not save

    i need it to send and save to a specific path and very long name format

    can you help

    Regards

    Reply
  6. John C

    Thank you, thank you, thank you!

    Now question – where would I insert a signature=True line so that Outlook signature appears?

    Thanks again,

    John

    Reply
  7. Nisar Aboobacker

    dear.

    can you help me how to print an excel sheet with saving as well sending email as a PDF.
    I had try many time but not.
    please.
    may I know your Email

    Reply
  8. Kia

    Help Please!!

    I have two spreadsheets, one with over 100 rows with data on it. The second is a spreadsheet that is formatted to look like a letter with vlookups and formulas. I need the macro to create a PDF for every data row on sheet one and then email letters. The email address to send it to will be on a specific cell.

    Please help

    Reply
  9. Richelle

    Thank you for your wonderful tutorials. Its been very helpful to me 🙂

    Reply
  10. Adam K

    My code I copied is working, and I had someone help me and they changed a tad. It was working then I tried changing the filename addition and now it’s not adding my file as an attachment??

    Sub Email_ActiveSheet_As_PDF()

    ‘Do not forget to change the email ID
    ‘before running this code

    Dim OlApp As Object
    Dim NewMail As Object
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    ‘ Temporary file path where pdf
    ‘ file will be saved before
    ‘ sending it in email by attaching it.

    ‘strPath = Environ$(“temp”) & “” ‘Or any other path, but include trailing “”
    strPath = Environ$(“USERPROFILE”) + “”
    ‘ Now append a date and time stamp
    ‘ in your pdf file name. Naming convention
    ‘ can be changed based on your requirement.

    TempFileName = ActiveSheet.Name & “-” & Format(Now, “dd-mmm-yy h-mm-ss”) & “.pdf”

    ‘Complete path of the file where it is saved
    FileFullPath = TempFilePath & TempFileName

    ‘Now Export the Activesshet as PDF with the given File Name and path

    On Error GoTo err
    With ActiveSheet
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FileFullPath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    End With

    ‘Now open a new mail

    Dim user As String

    ‘ user = Environment.SpecialFolder.UserProfile

    Set OlApp = CreateObject(“Outlook.Application”)
    Set NewMail = OlApp.CreateItem(0)

    On Error Resume Next
    With NewMail
    .To = “my email is here”
    .Subject = “Type your Subject here”
    .Body = “Type the Body of your mail”
    .Attachments.Add FileFullPath ‘— full path of the pdf where it is saved
    .Display ‘or use .Display to show you the email before sending it.
    End With
    On Error GoTo 0

    ‘Since mail has been sent with the attachment
    ‘Now delete the pdf file from the temp folder

    Kill FileFullPath

    ‘set nothing to the objects created
    Set NewMail = Nothing
    Set OlApp = Nothing

    ‘Now set the application properties back to true
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    MsgBox (“Email has been Sent Successfully”)
    Exit Sub
    err:
    MsgBox err.Description

    End Sub

    Reply
  11. Greg

    I have the code working….partially. When I say that, the pdf is created and is an attachment in an email. however, only the first two tabs of my spreadsheet are created in the pdf. There are 6 tabs that I leave visible before creation. Can anyone give me a clue as to where to look in the code?

    Reply
  12. rajasekar

    Dear Vishwa,
    Your e mail template is very nice in addition that you have add some features to me I have the deference datas in company wise,ex:volume,despatched,received,acknowledged,. The count may be vary on daily basis I need to send the this data to daily basis to around 600 company while body I have draft the details this table Details are not able to insert please advice

    Reply
  13. Carlos Petersen

    Hi Vishwa

    I used your code, but without success.
    Ineed the code gets the “email to” from a cell, how can I do this?

    Another: the script returned “mail sent sucessfully” but the outlook was not opened, and the email, on true was didnt sent.

    when you say on the code for change the mail ID, is the data for destination user email, ok?

    Tks!

    Reply
    • Carlos Petersen

      Vishwa, I got this, using .To = Range(“A1”).Value
      Where A1 is the cell with the mail destination.

      Your script saved my job, thankyou very much for your generosity to share this script with us.

      have a Good Week!

      Reply
  14. Sarah

    Really great. Thank you very much!

    Reply
  15. chris

    great code – thanks for saving me lots of time!

    Reply
  16. A.Maurizio

    Ciao a Tutti Tutto questo codice e molto Bello
    Però avrei una domanda da farvi che e questa:
    Se io al posto del Classico Outlook avessi la doverosità di usare ad Esempio ( Mozilla Thunderbird )
    Come posso far girare il percorso in modo che mi apra questa Applicazione al posto do Outlook Grazie

    Reply
    • A.Maurizio

      Hello everyone All this code is very nice
      But I would have a question to ask you that this:
      If I instead of the Classic Outlook had the need to use to Example (Mozilla Thunderbird)
      How can I run the path so that I can open this application in place I give Outlook Thanks

      Reply
  17. Jackie B.

    Hi Vish,

    I would like to export the Worksheet as a .pdf to a remote server or SharePoint drive permanently (no Kill command), but the code is not working with the TempFilePath = Environ$(“remote address”) . Is there some other syntax I need to be using? Or do I need to save the .pdf to my hard drive first and then save the temp file to the SharePoint?

    Please help!

    Reply
    • Vishwamitra Mishra

      Dear Jackie!!
      Thanks for stopping by.
      If you want to export your excel as pdf directly to a specific location then instead of using Environment function to get the path, you can directly use the full path.

      TempFilePath = "full remote path"

      Let me know if this answers your question.
      Cheers!

      Reply
  18. Jose

    Works perfect!!!! thanks
    Just copied code & works fine.

    Reply
  19. William

    Thank you Very Much this code really made me happy, by testing it I even send a email to you, with the template. Only challenge I have is it didn’t open in outlook, it send straight away.

    Reply
    • Vishwamitra Mishra

      All you need to do is.. replace the statement .Send with .Show.

      as you can refer my comment in the below code

      With NewMail
      .To = “info@learnexcelmacro.com”
      .CC = “info@learnexcelmacro.com”
      .BCC = “info@learnexcelmacro.com”
      .Subject = “Type your Subject here”
      .Body = “Type the Body of your mail”
      .Attachments.Add FileFullPath ‘— full path of the pdf where it is saved
      .Send ‘or use .Display to show you the email before sending it.
      End With
      On Error GoTo 0

      Reply
  20. Taylor

    How can I add my default signature to the macro?

    I have tried but no luck

    Reply
  21. Des

    You Sir are a legend. Didn’t think it would go as smoothly as this. Great coding and no problems. Just needed to set up Outlook on my PC to clear the error message…..Many thanks again for this outstanding work. Des

    Reply
  22. Dawn

    How can I combine these to email separate sheets to different individuals?

    Reply
  23. Ingrida

    How could I limit the area to columns A to R, so that everything else would not be on PDF?

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest