Automate Excel to PDF and Email PDF Document using VBA

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 = “”
.Subject = “Data”
.Body = “The Excel data is attached in PDF format.”
myAttachments.Add “C:\Users\takyar\Desktop\test-save.pdf”
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)

Object Variables

13 thoughts on “Automate Excel to PDF and Email PDF Document using VBA

  1. Valerio Colla

    Hello Dinesh,

    That was excellent! Simple and effective code. Thank you for another excellent tutorial. On the subject of saving PDF files, I have the following query which is driving me nuts:-

    When I generate an invoice from my invoice template, it is automatically saved to an “Invoice” folder as a pdf.
    However, I have noticed that if the invoice has exactly the same client name (I have a number of repeat customers), the newest invoice overwrites the previous one in the same name once saved as a pdf. I have tried to create a Rename File message box giving me the option of renaming the newest invoice file from, say, “aaa.pdf” to “aaax.pdf” prior to saving it.
    I tried to create a message box with the question: “Do you want to rename “aaa.pdf” to “aaax.pdf”? and the notification “There already is a file with the same name in this location” with a YES/NO option button.
    However, I have not succeeded with the message box and I’m stumped.

    Do you have any ideas?

    Thanks in advance.

    1. Dinesh Kumar Takyar Post author

      You can use the following code to get the appropriate names for your Excel files and PDF files using the following 2 lines of code:
      Fname = Application.DefaultFilePath & “\” & ActiveWorkbook.Name & “.pdf”

      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname


    Dear Sir,

    I am still working on establishing a program for my dental clinic. Can I have an idea of a simple database for a patient registration that linked to patient daily visit and then finished with areport and payment info. Apprecuate your kind assistance.

  3. Cothish

    I love your work and must admit that this tutorial explains a lot. But I do have a question then. I try to send dozen of emails from my excel mailing list file. I already have the code to execute all email addresses’ in the file but have no idea how to attached a simple PDF file to each email? Can you help me with it?
    Below is a full code that I do have by now:

    Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

    Dim olApp As Outlook.Application

    Set olApp = CreateObject(“Outlook.Application”)

    Dim olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)

    olMail.To = what_address
    olMail.Subject = subject_line
    olMail.BodyFormat = olFormatHTML
    olMail.HTMLBody = mail_body

    End Sub
    Sub SendMassEmail()

    row_number = 1

    row_number = row_number + 1
    Dim mail_body_message As String
    Dim full_name As String
    Dim add_info As String

    mail_body_message = Arkusz1.Range(“J2”)
    full_name = Arkusz1.Range(“B” & row_number) & ” ” & Arkusz1.Range(“C” & row_number)
    add_info = Arkusz1.Range(“D” & row_number)
    mail_body_message = Replace(mail_body_message, “replace_name_here”, full_name)
    mail_body_message = Replace(mail_body_message, “add_info_here”, add_info)
    Call SendEmail(Arkusz1.Range(“A” & row_number), “This is a test e-mail”, mail_body_message)
    Loop Until row_number = 25
    MsgBox “Complete!”
    End Sub

  4. Pingback: VBA to automaticall email pdf files

  5. Jason

    Hello Dinesh,

    I’m not experienced in using VBA and I was trying to use your VBA code above.

    I’m using Excel on a Mac computer and it appears that the filing name is different, what should this line of code be changed to ChDir “C:\Users\takyar\Desktop”

  6. Jose Luis

    Hi Dinesh, firstable many thanks for your unvaluable experience that share with us.
    I have a question, can I give the name of the pdf file tru a textbox in the userform.. ? tan ks in advance for your comments and all the best

  7. SAM

    Mr Dineh Thanks for above video, I have one question, how to automatic convert excel active sheet data in pdf in sequence and send by Outlook app.

    Please help me to learn.

  8. Tracy Bower

    I am receiving an Object required error on the Set OutlookApp = CreateObject(“Outlook.application”) line. I have the variable set as an Object so I do not know what is going on.

  9. Pingback: Automate Excel To PDF & Send PDF Document As Mail Attachment | Email Marketing Com.munity

  10. Rwasibo Joseph

    Hello My Teacher Dinesh.
    According to the Lessons I learn from you. I arleady made invoice system for our Company I created invoice that can create report as you thought me. I even created reminder notification for over dued invoices.
    What I want now I want you to help me to know if there is a way that all created invoice Can be sent automatically to clients on planned date. E.g. instead of Sending Reminder ,

    I’m waiting to hearing from you.


Leave a Reply

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