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
Receiving an error msg while running the code.
"Invalid procedure call or argument"
Am i doing wrong?
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.
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
Sir pls send file how to learn macro from beginning
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
Thank you, thank you, thank you!
Now question – where would I insert a signature=True line so that Outlook signature appears?
Thanks again,
John
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
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
Hi Kia,
There are many articles I have written around sending emails. Please refer them all and I am sure you will be able to find the solution from them.
http://learnexcelmacro.com/wp/?s=%22send+email%22
Thank you for your wonderful tutorials. Its been very helpful to me 🙂
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
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?
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
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!
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!
Really great. Thank you very much!
great code – thanks for saving me lots of time!
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
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
Hi Maurizio,
I have not used Mozilla thunderbird but what I know is that you can not send email via thunderbird same as outlook. Just opening thunderbird instead of Outlook will not help you in sending the email. You can find answer here… [I have not tested the solution provided here]
https://stackoverflow.com/questions/39521872/automatically-sending-email-from-thunderbird-with-excel-vba
Risposta sopra 🙂
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!
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.
Let me know if this answers your question.
Cheers!
Works perfect!!!! thanks
Just copied code & works fine.
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.
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
How can I add my default signature to the macro?
I have tried but no luck
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
How can I combine these to email separate sheets to different individuals?
How could I limit the area to columns A to R, so that everything else would not be on PDF?