Page 4 of 4 FirstFirst ... 2 3 4
Results 31 to 37 of 37

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

  1. #31


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

    Hi Ken,
    I would like to use the same code but i do not know where to paste this code and i believe i have the same problem with reference. Could pls help me step by step how to paste this code and where to paste it?

    Thanks in advance..

  2. #32
    Hi Paulmer,

    How did you solve the reference problem. I have the same problem could you please help me with that.


    Thanks in advance

  3. #33
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by aaron de sin View Post
    I would like to use the same code but i do not know where to paste this code and i believe i have the same problem with reference. Could pls help me step by step how to paste this code and where to paste it?
    Aaron,

    In my signature line there is a big link to click to tell you where to put your code. Follow the steps there and you'll have it in the right place. Then the article I linked to in my previous post in this thread tells you how to set the reference issues.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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. #34
    Dear Ken,

    Thank alot for your time but i have tried as wat instructed in the article. I manage to find out about pasting the code but i have a new problem regarding the reference. The reference i have to choose is pdfcreator but i dont have this reference in my list. Could me help me??

    Thank you again

  5. #35
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    It sounds like you haven't downloaded and installed PDFCreator yet. You can get it here: http://sourceforge.net/projects/pdfcreator/

    After you've installed it, then you should be able too (and will need to) set that reference.

  6. #36
    Dear Ken,

    Thank for your assitance. I have solved the reference problem. Has u know, I have to convert this excel activesheet into pdf and email it. I have combined the two set of code in the previous post but i don't why when i test it using my own email address i did not receive any email of the pdf. could you please check the code for me. the code is as below:

    Code:
    Sub PrintToPDF_Early()
    'Author : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to PDF file using PDFCreator
    ' (Download from http://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 = "xxx"
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Body = "YYY"
    .Attachments.Add sPDFPath & sPDFName
    .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
    Last edited by Ken Puls; 2013-06-17 at 11:36 PM. Reason: Added code tags

  7. #37
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hmm... not sure actually. Don't have time to troubleshoot right now, but try changing the .Send line to read .Display. That should pop the email up, and you can click the send button. Not ideal, but the code seems to be having issues resolving the email address for some reason.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    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 4 of 4 FirstFirst ... 2 3 4

Posting Permissions

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