If you want to quickly convert an Excel worksheet to a PDF file and then attach this PDF file to an Outlook email to send it, you can utilize the method introduced in this article. It is using VBA to accomplish this task within seconds.
To be honest, to attach an entire Excel workbook as a PDF attachment in an email is pretty easy. You can first save the workbook as a PDF file and then create a new Outlook email and attach the PDF file to this mail. However, in Excel, there is no such a direct function to convert a single worksheet to a PDF file and mail it. Thus, if you want to quickly attach an Excel worksheet as a PDF attachment in an email, you can utilize the following means.
Attach an Excel Worksheet as a PDF Attachment in Your Email
- In the first place, you need to open the source Excel worksheet as normal.
- Then in the Excel window, you should press “Alt + F11” key buttons.
- Subsequently, you will get access to the Excel VBA editor window in success.
- After that, you ought to open the “ThisWorkbook” project.
- Next copy and paste the following VBA code into this project window.
Sub SendWorksheet_AsPDFAttachment_OutlookEmail()
Dim objFileSystem As Object
Dim strTempFile As String
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
'Specify the worksheet name
Sheets("DataNumen Staff").Activate
ActiveSheet.UsedRange.Select
ThisWorkbook.Sheets(Array("DataNumen Staff")).Select
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
strTempFile = objFileSystem.GetSpecialFolder(2).Path & "\" & ActiveSheet.Name & " in " & ThisWorkbook.Name & ".pdf"
'export the specific worksheet as PDF
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'Create a new email
Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)
'Attach the PDF file
objMail.Attachments.Add strTempFile
objMail.Display '==>display this email
'Delete the temp PDF file
objFileSystem.DeleteFile (strTempFile)
End Sub
- Eventually, to trigger the new macro, you can hit the “Run” icon in the toolbar or straightly press the “F5” key button.
- After the macro finishes running, a new Outlook email will display at once. In it, you can see a PDF file in the “Attached” line, like the following screenshot:
Protect Outlook File from Getting Damaged
As we all know, Outlook is prone to corruption, similar to Word documents and Excel workbooks. Therefore, we should understand how to protect Outlook from corruption. Due to the fact that viruses have become more and more complex and rampant, Outlook is frequently corrupt due to virus infection and malware attack. In this situation, you have to install reliable antivirus software and disable auto downloading external files. Furthermore, you’d better get hold of anther robust Outlook fix tool, like DataNumen Outlook Repair. It’s able to provide you with the most effective remedy in the event of Outlook corruption.
Author Introduction:
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix sql and outlook repair software products. For more information visit www.datanumen.com
Hi,
Would there be a way to add this vba to my current workings.
I’m trying to get pdf to attach to my email through vba created for my worksheet.
Here is my vba for contacts that automatically appear when I click graphics button created:
‘VBA Sub Procedure to Start Email
Sub Start_Email()
Dim iConfirmation As VbMsgBoxResult
iConfirmation = MsgBox(“Send STP drafting task to assigned – Yes?”, vbYesNo + vbQuestion, “Confirmation”)
If iConfirmation = vbNo Then Exit Sub
‘Declaring Variables to refer the ‘Email List’ worksheet
Dim sh As Worksheet
Dim iRow As Integer
Set sh = ThisWorkbook.Sheets(“Email List”)
iRow = 2
Do While sh.Range(“A” & iRow).Value “”
‘check whether email has already sent or not
If sh.Range(“C” & iRow).Value = “” Then
‘Call SendEmail(S_UserName As String, S_EmailID As String)
Call SendEmail(sh.Range(“A” & iRow).Value, sh.Range(“B” & iRow).Value)
sh.Range(“C” & iRow).Value = “Yes – Emailed task. Unlock to send revision”
End If
iRow = iRow + 1
Loop
End Sub
….and here is my efforts so far but the pdf won’t attach when selected:
‘VBA Procedure to Prepare and Send Email
Sub SendEmail(S_UserName As String, S_EmailID As String)
‘Declaring variables as object type to refer Outlook Application & Mail Item
Dim OutApp As Object ‘ Outlook Application
Dim OutMail As Object ‘Outlook Mail Item
Dim sImgName As String
Dim result As Variant
result = Application.GetOpenFilename(“All files,*.*,Excel-files,*.xls,Word Files,*.doc,PDF Files,*.pdf,”)
PathFileName = ThisWorkbook.path & “\” & FileName & “.pdf”
‘add path and file to variables
Dim aPath As String: aPath = Cells(1, 1).Value
‘Set the reference of Outlook Application
Set OutApp = CreateObject(“Outlook.Application”)
‘Set the reference of Mail Item
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = S_EmailID
.From = “”
.CC = S_UserName
.BCC = “”
.attachments.Add ThisWorkbook.path & “\STP_DTask_instructions.pdf”
.attachments.Add ThisWorkbook.path & “\STPLogo.jpg”
.attachments.Add aPath
.attachments.Add ActiveWorkbook.FullName
myattachments.Add “FullPath\FileName.pdf”
sImgName = “STPLogo.jpg”
.Subject = Cells(8, 2).Value
.HTMLBody = “”
.Display
‘.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
‘VBA Sub Procedure to Start Email
Sub Start_Email()
Dim iConfirmation As VbMsgBoxResult
iConfirmation = MsgBox(“Send STP drafting task to assigned – Yes?”, vbYesNo + vbQuestion, “Confirmation”)
If iConfirmation = vbNo Then Exit Sub
‘Declaring Variables to refer the ‘Email List’ worksheet
Dim sh As Worksheet
Dim iRow As Integer
Set sh = ThisWorkbook.Sheets(“Email List”)
iRow = 2
Do While sh.Range(“A” & iRow).Value “”
‘check whether email has already sent or not
If sh.Range(“C” & iRow).Value = “” Then
‘Call SendEmail(S_UserName As String, S_EmailID As String)
Call SendEmail(sh.Range(“A” & iRow).Value, sh.Range(“B” & iRow).Value)
sh.Range(“C” & iRow).Value = “Yes – Emailed task. Unlock to send revision”
End If
iRow = iRow + 1
Loop
End Sub