Daren has attended:
Excel VBA Intro Intermediate course
Pdf seprate works sheets & add to an email
Hi wonder if you can help
I have a workbook that consist of five worksheets.
I am trying to convert them in to seperate Pdf document's and attached them to an email automatically, or at least to a folder.
I cannot find a save option within excel that save's documents with a Pdf extention.
I have been able to get a sheet to print to a Pdf file but it then prompts you for a file name, I would like it to be all done at the operation of a button
Can you help
Thank you
Daren
RE: Pdf seprate works sheets & add to an email
Hi Daren, thanks for your query. I don't have any PDF drivers on my machine at the moment so I will try to come up with some code over night if that's OK. Attaching PDF's to an email will involve access the Outlook Object model and not something easily explained here, but what I can do is give you some code to export the worksheets as separate files and possibly PDF them at the same time. If automatic PDF'ing proves difficult in code, you can at least then run a batch PDF on the folder.
Bear with me until tomorrow and check the forum then.
All the best,
Anthony
RE: Pdf seprate works sheets & add to an email
Hi Daren. I've had some success this end. Paste the following subroutine into a module attached to your workbook. Note the two comments at the start - you need to make sure Tools-References-Adobe Distiller is ticked and a second option is unticked in Adobe PDF properties.
Drop me a line back and let me know you've got it working. Here's the code:
Sub pdfme()
'need to check Reference to Acrobat Distiller in Tools --> References
'select file - print - adobe pdf - properties and untick "Do not send fonts to Adobe PDF"
Dim shtSheet As Worksheet
Dim myworksheetname As String
Dim PSFileName As String
Dim PDFFileName As String
Dim myPDF As PdfDistiller
Application.DisplayAlerts = False
Set myPDF = New PdfDistiller
For Each shtSheet In ActiveWorkbook.Worksheets
shtSheet.Select
myworksheetname = ActiveSheet.Name
PSFileName = "c:\mypdfs\" & myworksheetname & ".ps"
PDFFileName = "c:\mypdfs\" & myworksheetname & ".pdf"
'Print the Excel range to the postscript file
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
'Convert the postscript file to .pdf
DoEvents
myPDF.FileToPDF PSFileName, PDFFileName, ""
DoEvents
Next shtSheet
Application.DisplayAlerts = True
End Sub
Hope this helps,
Anthony
RE: Pdf seprate works sheets & add to an email
Hi Anthony
Sorry I have taken so long to get back to you.
I have tried the programming you have sent, it works in thatit goes through all the works sheets and sends the to the PDF printer.
It produces an arror, and put them in a print que instead of a folder.
I have now found out that most of my team only have "Cute PDF" on there PC', this is abasic version that only allow them to create a PDF document
I have created VB that creats a PDF Documents but it brings up a saveas box I would like it to pull the file name from the works sheet and save it automatically. i.e sheet1, sheet2 etc
The Printer output being called is "CutePDF Writer on CPW2"
Below is the VB programing I have created can you help
-------------------------------------------------------
Sub CreatePDF()
On Error GoTo bypass
' Creates folder on PC and saves to save the open workook
MkDir "C:\Client Reports\PDF_Test_Folder"
bypass:
ActiveWorkbook.SaveAs FileName:= _
"C:\Client Reports\PDF_Test_Folder\Book1Test PDF.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
' This makes the CutePDF output as the active printer
Application.ActivePrinter = "CutePDF Writer on CPW2:"
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
