Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Add attachments to PDF output

  1. #1

    Add attachments to PDF output



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

    Ken,

    I have used your code to create (and name) a pdf, attach to an email and email all with a button click. But I need to know how to add 2-5 pdfs to the same email with the code I have written....or another code. I am using Excel 2003. Anyone have any thoughts? Here is my code:

    Code:
    Sub PrintToPDF_Paysheet()
    'Author : Ken Puls (www.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 = ActiveSheet.Range("C5").Text & ".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
    Application.Wait Now + TimeValue("0:00:03")
    '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
    If ActiveSheet.Range("C3").Value = "RE-ISSUE" Then
    .To = "Activesheet.Range("C1").value="HELP"
    ElseIf ActiveSheet.Range("C3").Value = "RE-ISSUE" & Space(1) & "AUTH" Then
    .To = ActiveSheet.Range("C4").Text
    
    End If
    .CC = ""
    .BCC = ""
    .Subject = ActiveSheet.Range("C5").Text & Space(1) & "RE-ISSUE PAYMENT"
    .Body = ""
    .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-07-04 at 04:33 PM. Reason: Added code tags

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

    I've moved your post to a new thread, so that we can keep your question with it's own focus.

    Adding attachments can be done by adding a few more of these lines, right under the existing one:

    Code:
    .Attachments.Add "Full path to file goes here"
    That assumes the attachments already exist, of course. If they don't and you're creating multiple PDF files, then we should probably try and redraft your code a bit.
    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 quick reply. The code I'm using allows me to take a Excel 03 worksheet or sheets, print to pdf creator (multiple sheets if I want and combine into one pdf), name pdf, and attach it to an Outlook email. I want to create and add multiple pdfs using my code but keep getting hung up. I can only add one pdf this way. This is where I am creating the pdfs so they are not already saved to another folder (that would be easy.lol). Not sure what to manipulate in the code to make it work..HELP! Thanks again.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, we've got two issues that need to be dealt with then. I'd break this down into two pieces, and that way you can get through each of them and tie them together with a master macro to trigger them both.

    The code we'll use overall will be based off of:
    PDF Creation: http://www.excelguru.ca/content.php?161-Printing-Worksheets-To-A-PDF-File-(Using-Early-Binding)
    Email: http://www.excelguru.ca/content.php?...il-Integration

    So, the first thing we want to do is create the PDF files. Take a look at the four options that are in that first link, and choose the one that is best to start with. If you need help implementing it, then let me know. Once we've got that working, then we'll move on to the next part.
    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
    I believe this is the one I need (Multiple worksheets to multiple pdfs). I am not sure were to insert my information in the code. I don't know what Dim lSheet As Long, For lSheet = 1 To ActiveWorkbook.Sheets.Count, or Next lSheet means and what I should be doing with them. Thanks for the patience and the assistance.

    Option Explicit
    Sub PrintToPDF_MultiSheet_Early()

    Dim lSheet As Long

    For lSheet = 1 To ActiveWorkbook.Sheets.Count

    Next lSheet

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    If you want to print all worksheets in the file, we don't need to make any changes to the code except for this line:

    Code:
     sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
    This creates the file name which means that each sheet will be created with the name "testPDFsheetname.pdf" where sheetname is the name of your worksheet. Does that work, or do you only want selected worksheets and, if so, what are their names?

    Other things that would be good to know:
    -Are there any other files in the folder with the workbook
    -Are you always going to want to email all the pdfs out to someone
    -Can we just replace the existing PDF's with newer versions, or do you need to save older copies
    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
    Hey Ken,

    I do not need to print all worksheet in the workbook, only certain ones based on value criteria. I need to be able to create a pdf and then create a separate pdf then attached them both to the same email. The names I have for the worksheets are 1) Manual 2)PayStatus 3)Paysheet 4) Letter

    -Are there any other files in the folder with the workbook- No
    -Are you always going to want to email all the pdfs out to someone- Yes
    -Can we just replace the existing PDF's with newer versions, or do you need to save older copies- We can replace with newer

    This thing has been a thorn in my side.lol. Thanks for the help.

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

    Okay, so here's what you need to do...

    • Download the attached workbook
    • Copy the Distribution worksheet into your workbook
    • Go into the Visual Basic Editor and remove any of the PDF code you started with
    • Copy the modProcedures module into your project
    • Copy the clsOutlookEmail class module into your project


    At this point, if I read your sheet names correctly, you should be good to go. If you press Alt+F8 you'll find 3 macros can be run:
    • PrintToPDF_SpecifiedSheetsToMulti_Early will create the PDF's, overwriting the ones in the folder
    • EmailViaOutlook will email all the PDF's to the email addresses in the matrix, attaching the appropriate reports you've selected in the table
    • PrintAndEmail does them both. First it runs the PDF Creation, then it emails.


    As it stands, OUtlook will create drafts of the email, but will not send them for you. This will allow you to test it, the, if you're happy and confident it's doing the right thing, go and end the following line of code in the EmailViaOutlook code from:
    Code:
    .Preview
    To
    Code:
    .Send
    If you'd prefer to manually click the send buttons, then all good, don't bother with the edits.

    Some things to be aware of here...
    • The Distribution worksheet is listed in the code. If you change it, you'll need to do so at the top of the modProcedures module. Likewise the preface for the report files.
    • To clear reports or emails, use the DEL key, not the space bar.
    • There are several named ranges in this workbook that cover key areas of the distribution matrix. This is important to realize when you go to add new rows/colums to the table. Make certain you insert rows/columns in the middle, not at the ends or your new items may fall outside the named ranges and not be picked up by the macros.
    • The names of the worksheets in the table header must be spelled exactly as they appear in the worksheets.


    I think that's it. Let me know how it works for you.
    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.

  9. #9
    Hey Ken,

    I attempted to set my workbook up but the code did not run. There are also some additional things I should have elaborated on.


    1. Pdfs are created and emailed based on the value of worksheet Manual Check REQ “H7”.
      1. If H7 value = Advance Payment, my workbook needs to create a pdf for Manual Check REQ worksheet and a separate pdf for Advance Letter worksheet then attach both (individually named) to the same email and send.
      2. If H7 = “RE-ISSUE”, my workbook needs to create a pdf for Manual Check REQ worksheet and email to a specific email address
      3. If H7= “RE-ISSUE AUTH”, my workbook needs to create a pdf for Manual Check REQ worksheet and send to email address selected in “G15” (on Manual Check REQ worksheet)


    Please take a look at my workbook and let me know if this is possible. Thank againAA WORKBOOK2020.xls

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,276
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ah, okay... so...

    For scenario 1, where is the email defined? G15?
    For scenario 2, where is the email defined? Your wording makes it appear different...
    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 1 of 2 1 2 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
  •