Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 37

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14
    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.
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Hi Ken,

    Works perfect. Thanks a million

  5. #5
    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.
    Name:  Untitled.jpg
Views: 12000
Size:  93.9 KB

  6. #6
    Solved. References were the problem

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14
    Cool, glad it's working for you!
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    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 by tommyt61; 2012-01-24 at 09:09 PM.

  9. #9
    Nevermind .... I have got it working now. Thanks for such a nice peice of code Ken.

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    1,742
    Articles
    76
    Blog Entries
    14


    Register for a FREE account, and/
    or Log in to avoid these ads!

    You're very welcome.

    Funny though, you keep answering your own questions before I can get in here!
    Ken Puls, CPA, CMA, FCMA, MS MVP (Excel)

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •