Results 1 to 10 of 33

Thread: Email completed PDF

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Email completed PDF

    Ken,
    I'm working on a project to print selected worksheets to a pdf and then email it out automatically. I'm having issue with the code and thought that this might help, but it kept giving me an error. I need the code to send the pdf as soon as it is published. Maybe you can look at the code and give me some pointers. I think you may have worked on this in the past.

    Code:
    ' Print Multiple Worksheets to a Single PDF File:
    
    
    
    Sub PrintToPDF_MultiSheetToOne_Early()
    
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
        Dim lSheet As Long
        Dim lTtlSheets As Long
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        'Make sure the PDF printer can start
        If pdfjob.cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "Error!"
            Exit Sub
        End If
    
        'Set all defaults
        With pdfjob
            .cOption("UseAutosave") = 1
            .cOption("UseAutosaveDirectory") = 1
            .cOption("AutosaveDirectory") = sPDFPath
            .cOption("AutosaveFilename") = sPDFName
            .cOption("AutosaveFormat") = 0    ' 0 = PDF
            .cClearCache
        End With
    
        'Print the document to PDF
       ' lTtlSheets = Application.Sheets.Count
        'For lSheet = 1 To Application.Sheets.Count
         '   On Error Resume Next 'To deal with chart sheets
          '  If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
           '     Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            'Else
             '   lTtlSheets = lTtlSheets - 1
            'End If
           ' On Error GoTo 0
       ' Next lSheet
       
       
    'Print the document to PDF
        lTtlSheets = frmPrinttoPDF.lstProcess.ListCount - 1
        For lSheet = 0 To frmPrinttoPDF.lstProcess.ListCount - 1
            On Error Resume Next 'To deal with chart sheets
            If Not IsEmpty(Application.Sheets(frmPrinttoPDF.lstProcess.List(lSheet)).UsedRange) Then
                If Not frmPrinttoPDF.CheckBox1.Value = True Then pdfjob.cOption("AutosaveFilename") = sPDFName & "Sheetname" ' This should be the worksheet name
                Application.Sheets(frmPrinttoPDF.lstProcess.List(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
            Else
                lTtlSheets = lTtlSheets - 1
            End If
            On Error GoTo 0
        Next lSheet
    
        'Wait until all print jobs have entered the print queue
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
            DoEvents
        Loop
    
        'Combine all PDFs into a single file and stop the printer
       ' With pdfjob
        '    .cCombineAll
         '   .cPrinterStop = False
        'End With
        
        With pdfjob
            If frmPrinttoPDF.CheckBox1.Value = True Then .cCombineAll
            .cPrinterStop = False
        End With
    
        'Wait until the PDF file shows up then release the objects
    Do Until Dir(sPDFPath & sPDFName) <> ""
    DoEvents
    Loop
        'Send PDF as Email
       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 sPDFPath & sPDFName
    .Send 'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        MsgBox ("The PDF has been successfully created as " & sPDFName)
        pdfjob.cClose
        Sleep 1000
        Set pdfjob = Nothing
    End Sub

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,325
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi ref4ua, and welcome to the forum.

    I've split your question into a new thread. Although related to this one, I think it would be better suited on it's own.

    One thing you may want to do is to have a look at the most recent version of the PDF article. It's got a new method for opening that will kill off any open instance of PDFCreator.

    With regards to the issue at hand, you didn't say what the error actually was... I'm assuming it's in the attempt to send the email?Can you tell me what the error text is, and what line is highlighted when you click Debug?As a stab, you could try the following:
    • Download and unzip the attached file.
    • Go into the VBE, find your project, right click it and choose "Import"
    • Locate the download file and select it.
    This should import the class module referenced in the previous thread to make it easy to add Email functionality.

    Next, replace this:
    Code:
    'Send PDF as Email
       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 sPDFPath & sPDFName
    .Send 'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        MsgBox ("The PDF has been successfully created as " & sPDFName)
        pdfjob.cClose
        Sleep 1000
        Set pdfjob = Nothing
    With this:
    Code:
    'Create the email object
        Dim oEmail As New clsOutlookEmail
        With oEmail
            'Add a recipient
            .AddToRecipient = "test@test.com"
    
            'Set the subject
            .Subject = "Test"
    
            'Set the body
            .Body = "YYY"
    
            'Add a couple of attachments
            .AttachFile = sPDFPath & sPDFName
    
            'Preview the email (or use .Send to send it)
            .Preview
        End With
    
        'Release objects and terminate PDFCreator
        On Error Resume Next
        Set pdfjob = Nothing
        Set oEmail = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
    Let me know if that helps...
    Attached Files Attached Files
    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.

  3. #3
    Ken,
    Thanks for the help. I changed the code as you suggested and imported the class module as well. I can print the selected sheets to a pdf, but does not send the email. I looked in my outlook and it doesn't show it in the sent or outbox folders. Any suggestions?

    Randall

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,325
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Randall,

    Are you seeing any errors with the new version at all?

    What version of Office are you using?
    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.

  5. #5
    Ken,
    I'm not seeing any errors. I have a userform set up to select the pages you want to print. It will print the pdf and place it in the required folder, but will not send the email. I'm using Office 2010.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,325
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Strange...

    Try this for a second... create a new module in the same file, and drop in the following code:
    Code:
    Public Sub EmailViaOutlook()
    'Create the email object
        Dim oEmail As New clsOutlookEmail
        With oEmail
            'Add a recipient
            .AddToRecipient = "test@test.com"
            'Set the subject
            .Subject = "Test"
            'Set the body
            .Body = "YYY"
            'Preview the email (or use .Send to send it)
            .Preview
        End With
    
    End Sub
    Does it create an email at all?

    If not, can you create a new file, import the class module, and then try the code I posted here again?

    I'm just trying to figure out if it's the code or if there is something in the file messing us up here. (I'm using Office 2010 as well.)
    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.

  7. #7
    Error showing -> Dim oEmail As New clsOutlookEmail -> Compile Error: User-defined type not defined

Posting Permissions

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