November 22, 2014

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 = “[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)

Object Variables

34 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

      1. Your postings are excellent. I am watching your you tube videos regularly. Could you please explain how to send mail to different person with different attachment through outlook mail?
        I have 300 persons to whom I have to send individual mail with different attachment. I request your coding for the same

  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. Pingback: VBA to automaticall email pdf files
  5. 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”

    1. yes bro u can do it
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname

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

  10. Pingback: Automate Excel To PDF & Send PDF Document As Mail Attachment | I Am Roles
    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

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

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

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

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

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

  16. Dear Mr. Takyar,

    Your videos are amazing and very informative. I can got many ideas from your video…can you help me in automatically email pdf excel format use outlook?
    I already can export excel sheet in pdf. Name of the pdf file is from 2 cell. But to automatically email i got wrong scripts. I do not know how to call the file that save in desktop? i use this code to save to pdf. It is worked. (i got the idea from your video)

    Sub SavePDF()
    Dim Path As String

    Path = “C:\Users\User\Desktop\”
    Worksheets(“PantunSaya”).Activate
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & Range(“D6”) & Range(“D8”), OpenAfterPublish:=False

    But i can call the file to automatically email?
    thank you

    I’m from Malaysia
    Lin

  17. Great tool, thanks for sharing!

    Is there a way to generate multiple PDF and Email windows at the same time if you had multiple sheets on the excel file?

    Thanks!
    Nikola

  18. Most Respected Sir,
    Good job = good respect!
    Great job = Most Respect !!

    I tried this code exactly and it works great!
    But two problems:
    1.The page breaks makes the data in pdf insensible. How to get all the columns in pdf without break(I have hardly 20 columns of minimal width)
    2.There are empty rows in my excel i purposely left which i dont want in my pdf

  19. Please, A repeat Question:
    Your postings are excellent. I am watching your you tube videos regularly. Could you please explain how to send mail to different person with different attachment through outlook mail?
    I have 300 persons to whom I have to send individual mail with different attachment. I request your coding for the same.
    I will be very happy if a code for the above question is proffered!
    Best Regards Daddy

  20. Good afternoon,
    I really enjoy your videos, I tried to complete the VBA for an excel timecard my employees need to complete weekly. I want the sheet to save as a pdf in a specified folder and then email the attachment at the same time.

    Sub TimeSheet_PDF()

    ChDir “Y:\Production\TechnicianForms”

    s = Range(“AB9”).Value

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    s, Quality:=xlQualityStandard, IncludeDocProperties _
    :=True, ignorePrintAreas:=False, OpenAfterPublish:=False

    PDF_File = “Y:\Production\TechnicianForms\” & Range(“AB9”).Value & “.pdf”

    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 = Worksheets(“Table 1”).Range(“AB9”).Value
    .Body = “My Time Sheet is attached in PDF”
    .myAttachments “PDF_File”

    ‘.Send
    .Display

    End With

  21. Hi, I use exactly same method but getting error
    Runtime error ;91′:
    Object Variable or With block variable not set

    Sub CreatePDF()
    ChDir “O:\Payroll Services Centre\Payroll\_9. Workers Compensation\Timesheets\Timesheet WRS\WRS Ack”
    Sheet2.ExportAsFixedFormat xlTypePDF, Filename:= _
    “O:\Payroll Services Centre\Payroll\_9. Workers Compensation\Timesheets\Timesheet WRS\WRS Ack\” & Sheet2.Range(“A58”)

    Dim OutLookapp As Object
    Dim OutLookMailItem As Object
    Dim myAttachments As Object

    Set OutLookapp = CreateObject(“Outlook.application”)
    Set OutLookMailItem = OutLookapp.CreateItem(0)
    Set myAttachment = OutLookMailItem.Attachments

    With OutLookMailItem
    .To = “[email protected]
    .Subject = Sheet2.Range(“A58”)
    .Body = “The Excel data is attached in PDF format.”
    myAttachments.Add “O:\Payroll Services Centre\Payroll\_9. Workers Compensation\Timesheets\Timesheet WRS\WRS Ack\” & Sheet2.Range(“A58”)
    ‘.send
    .Display
    End With

    Set OutLookMailItem = Nothing
    Set OutLookapp = Nothing

    End Sub

  22. Hello Mr.Dinesh,
    Thank you for excellent video tutorial.
    I need something more. I have an excel sheet with different data. One portion of the sheet should send to one email id and another portion to send to another email id and so on. How can you help me to solve this problem?

  23. Thank you for this great tutorial.

    How do I achieve this same process using GMAIL instead of Outlook.

    Thanks

Comments are closed.