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)
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
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.
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
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”
Path to your folder on your computer.
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
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.
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.
It works well, thank you very much for the video and for sharing knowledge 🙂
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.
Hi,
How to set attachment as an optional, is this possible?
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
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
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
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.
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.
How can i use html instead of PDF
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