Pdf seprate works sheets & add to an email | Excel forum

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

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