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 = “takyar@hotmail.com”
.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)

Object Variables

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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

  1. 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.
    VC

    1. 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

  2. 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. Hi
    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
    olMail.Send

    End Sub
    Sub SendMassEmail()

    row_number = 1

    Do
    DoEvents
    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. 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”

  5. 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

  6. 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.

  7. 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.

  8. 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.

    1. Dear Mr. Dinesh,

      First of all thank you very much for this brilliant explanation. But could you please explain us how to set up a VBA code in the case when user doesn’t have Outlook or any similar applications. In my case I don’t have permission to install any application like Outlook or similar so I have to use only hotmail. Please I would be grateful if you might be able to help me write a VBA code for this case.

      Thank you very much

      Kind regards

  9. In the tool menu, reference key not accessable in my excel vba page, then all the codes shows error…. can you pls help in this

  10. I was wondering how to add multiple people on the email portion. If I want to add multiple people to send, what characters or setups do I need to do? I tried, commas, multiple quotes, etc. I’m not a solid programmer but I’m figuring this is simple.
    Thanks,
    Marc

  11. Sir Thanks for The wonderful Video,It’s very Useful for every one.
    But my system give some error
    “Compile Error :
    Only comments may apper after End Sub, End Fuction, or End Property ”

    So sir can u fix it.

  12. Thank you for the great video. It works great. So much so that I want to share my excel creation with a couple of friends. The issue I have is that the macro coding is specific to my user name (i.e. C:\Users\Sstillone\Desktop), so if you were going to run this on your computer with your user name, it won’t work.

    Is there some way that the coding can be generic to pick up the user name of anyone that I may share the file with? This would also allow me to share update versions of my file with others without having to have specific coding for each user on each update.

    Thanks again for your knowledge and advice.

  13. Dear Mr. Takyar,

    Your videos are amazing and very informative. I am having problem with the code below… i am getting a printing error

    Sub PrintInvoiceVBA()

    Dim code As Long
    Dim Name As String
    Dim path As String
    Dim r As Long
    ‘etc
    lastrow = Sheets(“Invoice Link 1”).Range(“A” & Rows.Count).End(xlUp).Row
    r = 2
    For r = 2 To lastrow
    If Cells(r, 23).Value = “Yes” Then GoTo nextrow

    path = “/Users/gautamanand/VBA2/”

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=path & code & LastName & ” ” & FirstName & ” ” & code & “.pdf”, _
    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    myfilename = ActiveWorkbook.FullName

    Application.DisplayAlerts = True
    ActiveWorkbook.PrintOut copies = 0
    ActiveWorkbook.Close SaveChanges = False

    nextrow:
    Next r

    End Sub

    there are more DIm but i have skipped to keep it easy to read. would really appreciate your guidance on how to fix this. the error
    Many thanks

Leave a Reply

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