Macro to create PDF from Excel 2003 and send as attachment in email

Paulmer

New member
Joined
Nov 25, 2011
Messages
5
Reaction score
0
Points
0
I am completely new to VBA and dont have the slightest idea how it works. I need to create a PDF from an Excel 2003 worksheet and send it automatically by email as attachment.

Thanks to this site and to Ken Puls, I managed to find a macro to create PDF using PDFCreator which works absolutely fine. I have also found code to send emails. Can somebody help me to join them into one macro please?

Below are the codes:
Code:
Sub PrintToPDF_Early()
'Author : Ken Puls ()
'Macro Purpose: Print to PDF file using PDFCreator
' Designed for early bind, set reference to PDFCreator
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim bRestart As Boolean
'/// Change the output file name here! ///
sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
'Activate error handling and turn off screen updates
On Error GoTo EarlyExit
Application.ScreenUpdating = False
Set pdfjob = New PDFCreator.clsPDFCreator
'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False
'Assign settings for PDF job
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Delete the PDF if it already exists
If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub

The following is the code to send email:

Code:
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Dest
.SaveAs sPDFPath & sPDFName
On Error Resume Next
With OutMail
.To = Cells(4, 2)
.Subject = "TIL Record Sheet"
.Body = "Dear " & Cells(4, 4) & "," & vbNewLine & _
"" & vbNewLine & _
"Attached please find copy of your TIL record sheet." & vbNewLine & _
"" & vbNewLine & _
"Regards," & vbNewLine & _
"" & vbNewLine & _
"HR Office" & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"THIS IS A COMPUTER GENERATED MESSAGE. PLEASE CALL ON 1641 IF YOU DISAGREE WITH CONTENTS."
.Attachments.Add Dest.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Kill sPDFPath & sPDFName
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End Sub
 
I figured out something myself about how to create PDF and send email (as per below code). The problem is that for some reason the pdf is not being attached with the email in excel 2003. I would really appreciate any help how to solve this matter. Thanks alot.


Code:
Sub PrintToPDF_Early()
'Author : Ken Puls (yyy.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from hxxp://sourceforge.net/projects/pdfcreator/)
' Designed for early bind, set reference to PDFCreator

Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim bRestart As Boolean
Dim StrTo As String
Dim StrSubject As String
Dim StrBody As String
Dim Send As Boolean

Dim OutApp As Object
Dim OutMail As Object

'/// Change the output file name here! ///
sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

'Activate error handling and turn off screen updates
On Error GoTo EarlyExit
Application.ScreenUpdating = False
Set pdfjob = New PDFCreator.clsPDFCreator

'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False

'Assign settings for PDF job
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Delete the PDF if it already exists
If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)

'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "test@test.com"
.CC = ""
.BCC = ""
.Subject = "Test"
.Body = "YYY"
.Attachments.Add ("C:\testPDF.pdf")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub

EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub
 
Hi Paulmer,

Welcome to the forum! I'm glad you found this article useful!

With regards to adding the email portion in, change the following line from this:

Code:
.Attachments.Add ("C:\testPDF.pdf")

To this:

Code:
.Attachments.Add sPDFPath & sPDFName

Let me know if that works for you. :)
 
Hi Ken,

Works perfect. Thanks a million :clap2:
 
Hi Ken,

Please bear with me. The above code works perfectly in the test workbook however when I transfer the same coding to my real project I'm having this error. Would really appreciate your help. Thanks alot.
Error
 
Ken,

I have got your code working but needed to make a slight adjustment to it to fit my environment. I need to be able to create a filename based on a value it finds in Cell f4. I also need it to save the file to a folder different than the folder the workbook was opened from. so i need to change the path variable. I have tried several ways to make it work but have had no luck. Weird thing is ... it will save correctly but i can't do anyhing on my PC after the code runs. say when i click on a cell in my worksheet nothing happens. If i click on a menu item nothing happens. i have to do a CTRL+ALT+DEl to end Excel task to break out . It does run the email portion of the code after the changes ,but does run fine with your code. I will list the changes below that i am trying.


sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator


This is what i need:
sPDFName = "Inv" & Range("f4").Value
sPDFPath = "C:\My Reports" & "\" & Range("H4") .Value
 
Last edited:
Nevermind .... I have got it working now. Thanks for such a nice peice of code Ken.
 
Hello Ken

Started to apply your code and the 1st problem is with the following line. I get the response 'Compile error' User defined type not defined. As I am a novice I have no clue as to what is wrong. Please assist.


Dim pdfjob As PDFCreator.clsPDFCreator
 
Kas,

This is how that code should read

Dim pdfjob As Object
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
 
PDF 2 Email

Here is an example for you to study.
 

Attachments

  • PDF2EMAIL.xlsm
    23.3 KB · Views: 159
2003 file

here is .xls file for 2003
 

Attachments

  • PDF2EMAIL.xls
    50 KB · Views: 174
...I get the response 'Compile error' User defined type not defined...

You haven't set the reference to the PDFCreator library for your code. Unless you change the whole thing to late bound (which is going to be above you right now), it's not going to work. Check the article I linked you to above. That will show you how to set the reference.
 
Hi Ken

Thank you for the input. Yes, things are definitely 'above me right now', as when I first read this I started to look at previous posts(literally above) and then twigged to what you were saying. I read the article and did what was required as will now proceed with testing. As the list of 'set references' is quite extensive and they are not automatically set I can only assume that setting them all would have a devastating impact. Could you lead me through this so I can better understand what I am doing and why?
 
Hi,

I don't think setting all references would bother you too much, to be honest, but if you sent your workbook to someone else it could screw them up right royally.

So... computers talk, in their barest form, in binary. Being that we can't really understand that, we built languages to convert all the 1's and 0'2 into something human readable. Using those languages, people build programs. And in many programs people build libraries that other coders can use to talk to their programs. Typically these libraries get rolled into a DLL file (Dynamic Link Library), and when you install the program on your computer, the libraries are registered for use.

The references list that you see in the VBE is a listing of all the DLL files that are registered on your machine. And yes, there are LOTS of them.

Each DLL has it's own code pieces in it. Classes, Objects, Properties, Methods, Functions, etc... The complexity depends, of course, upon how complex the original program was and how much the developer decided to expose in the DLL file. The nice thing for us is that we can set a reference to these files and use the object, properties and methods contained within.

Now, there are actually two ways to use these libraries... you can connect to them and have full access to the object model that goes with them, or you can declare all of your objects as "Object" and manifest them dynamically at runtime. The difference is what we call Early Binding (setting a reference) vs Late Binding (manifest at runtime). You can read a bit more about the difference here. The issue you were facing is that the code posted on my site is Early Bound code, and you did not set a reference. Without that reference, Excel doesn't have a clue what you're talking about. :)

Now, just because you set a reference doesn't mean that you have to use anything from the library. Hence my comment that you could set all the references. BUT... if you do set a reference to a library that I don't have, then email me your workbook, I get an error. Usually it manifests in a valid VBA function throwing an error. After finding that, I would need to go into the references list and remove any "MISSING:" references in order to let the code run.

At any rate, I'd avoid setting references unless you're told you need to. But if you ever see the "User Defined Type Not Defined" error, the first thing I'd check is if the code is Early Bound and a reference hasn't been set.

Does that help?
 
Back
Top