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

Thread: Email completed PDF

  1. #1

    Email completed PDF



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

    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,208
    Articles
    57
    Blog Entries
    14
    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 (Excel)

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

    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,208
    Articles
    57
    Blog Entries
    14
    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 (Excel)

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

    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,208
    Articles
    57
    Blog Entries
    14
    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 (Excel)

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

    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
    Ken,
    When I added the code you just sent it worked great. I'm going to send you all the code for the module I'm using and also the user form.

    Here is the user form:
    Code:
    Option Explicit
    Option Base 0
    
    
    Private Sub CmdBrowse_Click()
    
      Dim objShell As Object
      Dim objFolder As Object
      Dim strFolderFullPath As String
    
        Set objShell = CreateObject("Shell.Application")
        Set objFolder = objShell.BrowseForFolder(0, "Please select a folder", 0, "C:\MyFolders\TestFolder")
    
          If (Not objFolder Is Nothing) Then
        
             On Error Resume Next
               If IsError(objFolder.Items.Item.Path) Then strFolderFullPath = CStr(objFolder): GoTo Here
             On Error GoTo 0
        
             If Len(objFolder.Items.Item.Path) > 3 Then
                strFolderFullPath = objFolder.Items.Item.Path & Application.PathSeparator
             Else
                strFolderFullPath = objFolder.Items.Item.Path
             End If
          Else
             MsgBox "User cancelled": End
          End If
    
    Here:
    'Loads Textbox1 with drive
    TextBox1.Value = strFolderFullPath
    
    End Sub
    
    
    Private Sub CheckBox2_Click()
    
    End Sub
    
    Private Sub CheckBox3_Click()
    
    End Sub
    
    Private Sub cmdAdd_Click()
    Dim i As Integer
        
        With Me.lstAvailable
            For i = .ListCount - 1 To 0 Step -1
                If .Selected(i) Then
                    Me.lstProcess.AddItem .List(i)
                    .RemoveItem i
                End If
            Next i
        End With
    End Sub
    
    Private Sub cmdCancel_Click()
    Unload Me
    End Sub
    
    Private Sub cmdRemove_Click()
     Dim i As Integer
        
        With Me.lstProcess
            For i = .ListCount - 1 To 0 Step -1
                If .Selected(i) Then
                    Me.lstAvailable.AddItem .List(i)
                    .RemoveItem i
                End If
            Next i
        End With
    End Sub
    
    Private Sub cmdStart_Click()
    Dim i As Integer
        
        With Me.lstProcess
            If .ListCount = 0 Then
                MsgBox "At least one sheet has to be selected for processing.", vbExclamation
            Else
            
         Dim pdfjob As PDFCreator.clsPDFCreator
         Dim sPDFName As String
         Dim sPDFPath As String
         Dim lSheet As Long
         Dim lTtlSheets As Long, jobcount As Integer
         
     
              '/// Change the output file name here! ///
              If frmPrinttoPDF.TextBox2.Value <> "" Then
             sPDFName = frmPrinttoPDF.TextBox2.Value
             Else
             sPDFName = "Consolidated"
             End If
             sPDFPath = TextBox1.Value & "\"
             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 & ".pdf"
                .cOption("AutosaveFormat") = 0    ' 0 = PDF
                .cClearCache
              End With
     
                'Print the document to PDF
              lTtlSheets = lstProcess.ListCount
               jobcount = 0
               For lSheet = 0 To lstProcess.ListCount - 1
           '              On Error Resume Next 'To deal with chart sheets
               If Not IsEmpty(Application.Sheets(lstProcess.List(lSheet)).UsedRange) Then
      
                          If Not CheckBox1.Value = True Then
                   ' set name, print and close, for each single pdf
          ' pdfjob.cOption("AutosaveFilename") = sPDFName & lstProcess.List(lSheet) & ".pdf"
          pdfjob.cOption("AutosaveFilename") = lstProcess.List(lSheet) & ".pdf"
        If Len(Dir(sPDFPath & pdfjob.cOption("AutosaveFilename"))) > 0 Then Kill sPDFPath & pdfjob.cOption("AutosaveFilename")
        Application.Sheets(lstProcess.List(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
        Loop
        pdfjob.cPrinterStop = False
        Do Until Len(Dir(sPDFPath & pdfjob.cOption("AutosaveFilename"))) > 0
        DoEvents
        Loop
        Else
        If Len(Dir(sPDFPath & pdfjob.cOption("AutosaveFilename"))) > 0 Then Kill sPDFPath & pdfjob.cOption("AutosaveFilename")
        Application.Sheets(lstProcess.List(lSheet)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        jobcount = jobcount + 1
        Do Until pdfjob.cCountOfPrintjobs = jobcount
        DoEvents
        Loop
        End If
        Else
        lTtlSheets = lTtlSheets - 1
        End If
        Next
        ' close document for combined pdf
        With pdfjob
        If CheckBox1.Value = True Then
        Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
        Loop
       '                        If Len(Dir(sPDFPath & pdfjob.cOption("AutosaveFilename"))) > 0 Then Kill sPDFPath & pdfjob.cOption("AutosaveFilename")
       .cCombineAll
       .cPrinterStop = False
       Do Until pdfjob.cCountOfPrintjobs = 0
       DoEvents
       Loop
       Do Until Len(Dir(sPDFPath & pdfjob.cOption("AutosaveFilename"))) > 0
       DoEvents
       Loop
       End If
       Sleep 250
       pdfjob.cOption("UseAutosave") = 0
      .cClose
      End With
      Set pdfjob = Nothing
      Unload Me
      End If
      End With
      
      End Sub
    
    
    Private Sub MoveDown_Click()
        Dim ItemNum As Long, TempItem
        With Me.lstProcess
            If .ListIndex = .ListCount - 1 Then Exit Sub
            ItemNum = .ListIndex
            TempItem = .List(ItemNum)
            .List(ItemNum) = .List(ItemNum + 1)
            .List(ItemNum + 1) = TempItem
            .ListIndex = ItemNum + 1
        End With
    End Sub
    
    Private Sub MoveDown_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Call MoveDown_Click
    
    End Sub
    
    
    Private Sub MoveUp_Click()
        Dim ItemNum As Long, TempItem
        With Me.lstProcess
            If .ListIndex <= 0 Then Exit Sub
            ItemNum = .ListIndex
            TempItem = .List(ItemNum)
            .List(ItemNum) = .List(ItemNum - 1)
            .List(ItemNum - 1) = TempItem
            .ListIndex = ItemNum - 1
        End With
    End Sub
     
    Private Sub MoveUp_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Call MoveUp_Click
    
    End Sub
    
    Private Sub UserForm_Initialize()
    TextBox1.Value = "C:\Users\REF4UA\Desktop\test pdf"
    
    
        Dim actualSheet As Worksheet
        
       
        For Each actualSheet In Application.ActiveWorkbook.Sheets
        If actualSheet.Visible = True Then
            With actualSheet
                
                    Me.lstAvailable.AddItem .Name
                
            End With
        End If
        Next actualSheet
        
       
    
    End Sub

  8. #8
    Thanks again for all your help!

  9. #9
    Here is the code to print to pdf:

    Code:
    'Set Reference to PDFCreator
    ' Print a Single Worksheet to a PDF File:
    Option Explicit
    
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Sub PrintToPDF_Early()
    
    
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
    
        '/// 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
    
        Set pdfjob = New PDFCreator.clsPDFCreator
    
        With pdfjob
            If .cStart("/NoProcessingAtStartup") = False Then
                MsgBox "Can't initialize PDFCreator.", vbCritical + _
                        vbOKOnly, "PrtPDFCreator"
                Exit Sub
            End If
            .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
        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 PDF creator is finished then release the objects
        Do Until pdfjob.cCountOfPrintjobs = 0
            DoEvents
        Loop
        pdfjob.cClose
        Set pdfjob = Nothing
    End Sub
    
    ' Print Multiple Worksheets to Multiple PDF Files:
    
    
    
    Sub PrintToPDF_MultiSheet_Early()
    
    
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
        Dim lSheet As Long
    
        Set pdfjob = New PDFCreator.clsPDFCreator
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        If pdfjob.cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
    
        For lSheet = 1 To ActiveWorkbook.Sheets.Count
            'Check if worksheet is empty and skip if so
            If Not IsEmpty(ActiveSheet.UsedRange) Then
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
                    .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
                Worksheets(lSheet).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 PDF creator is finished then release the objects
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            End If
        Next lSheet
        pdfjob.cClose
        Set pdfjob = Nothing
    End Sub
    
    Option Explicit
    Option Explicit
    Sub PrintToPDF_MultiSheetToOne_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 lSheet As Long
        Dim lTtlSheets As Long
        Dim bRestart As Boolean
    
        '/// Change the output file name here! ///
        sPDFName = "Consolidated.pdf"
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    
        '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
        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 EarlyExit
        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
    
        '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
        'Create the email object
        Dim oEmail As New clsOutlookEmail
        With oEmail
            'Add a recipient
            .AddToRecipient = "myemail@work.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
    End Sub

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Aha!

    So the issue is that you put the last code in the EarlyExit section... which only fires if you have an error. Since there is no error in the PDF creation part (a good thing), the email isn't firing.

    Easy to fix. Replace everything from in that last routine from Cleanup down with:

    Code:
    'Create the email object
        Dim oEmail As New clsOutlookEmail
        With oEmail
            'Add a recipient
            .AddToRecipient = "myemail@work.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
    
    Cleanup: 
       '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
        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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

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

    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
  •