Let’s learn how to automate the process of converting an Excel document to a PDF document and then email the PDF document automatically via Outlook using VBA. We have earlier learnt how to send emails automatically via Hotmail and Gmail.
The VBA code to automate the process is given below:
Sub sendReminderMail()
ChDir “C:\Users\takyar\Desktop”
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
“C:\Users\takyar\Desktop\test-save.pdf”, OpenAfterPublish:=True
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject(“Outlook.application”)
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = “[email protected]”
.Subject = “Data”
.Body = “The Excel data is attached in PDF format.”
myAttachments.Add “C:\Users\takyar\Desktop\test-save.pdf”
‘.send
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Watch the video to learn quickly and easily how to automate the Excel to PDF document creation and also email the PDF document automatically via Outlook using VBA:
Watch the video on YouTube.
Download a sample file by clicking on the Excel icon:
Further reading:
Attachments.Add Method (Outlook)