Add attachments to PDF output

R

rockdz

New member
Joined
Jul 3, 2013
Messages
7
Reaction score
0
Points
0
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 ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'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 a moderator:
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,

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.
 
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?249-Easy-Outlook-Email-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.
 
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
 
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
 
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.
 
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.
 

Attachments

  • PDF Sel Sheet to Multi.xlsm
    49.8 KB · Views: 67
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 againView attachment AA WORKBOOK2020.xls
 
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...
 
Hey Ken,

Thanks for the help. For scenario 1 and 2 I was putting the email in the VBA code and for the 3rd I was referencing G15. I could reference G15 for all. Sorry it was confusing.
 
Ken,

I was posting back for a bit more direction. I still cant get it to work. Sorry for the confusion.
 
Ah, sorry about that.

Okay, so I took a look at the code in your file in detail, and there were a few things that went sideways on you.

I believe you were pointing the button to the wrong macro (should have been "PrintAndEmail"). Also, when you copied the class module code across, you put it in a new class module (which is good), but it needed to be named "clsOutlookEmail" as well. I've made both corrections in your file.

I also recoded a section of the routines, now that I understand what you're really after here.

Give this a go and let me know if it works. (FYI, didn't know what to use for the subject or body, so I guessed at the former, and left the latter to you.)
 

Attachments

  • AA WORKBOOK2020 (1).xlsm
    117.6 KB · Views: 46
You must log in or register to reply here.
Back
Top