I am trying to set up a macro that selects certain
worksheets in a workbook, prints them to a pdf file, and
then attaches that file to an email. I have most of it
cracked, but can't seem to fathom out how to convert the
postscript file I have created into a pdf file. I am using
Excel 2000 and Adobe acrobat v.5 (full version)

The code I have so far is as follows:

'Select the sheets to send to send to adobe post script'

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Workbook1").Activate
Application.ActivePrinter = "Acrobat Distiller on
Ne00:"
ActiveWindow.SelectedSheets.PrintOut copies:=1,
ActivePrinter:= _
"Acrobat Distiller on Ne00:", printtofile:=True,
Collate:=True, PrToFileName:="FilePath\Filename.ps"
Sheets("PRINT LIST").Select

'This is where I need to convert the above to a pdf and
kill the ps file. '

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail

'.To = emailadresses
'.CC = more emailadresses
'.Subject = "subject"
'.Body = ""
Set objOutlook = CreateObject
("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
.To = "
.Subject = "Monthly Bed occupancy statistics"
.Body = "Dear recipient " & vbCrLf & " Blah Blah
Blah" & vbCrLf & vbCrLf & "Kind Regards"
.Attachments.Add "FilePath\FileName.pdf"
.display
End With


End Sub

I would hugely grateful to anybody who could help me
resolve this problem

Something along these lines may work

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
kill(PSFileName)

Dan E

"Paul Falla" wrote in message
...
I am trying to set up a macro that selects certain
worksheets in a workbook, prints them to a pdf file, and
then attaches that file to an email. I have most of it
cracked, but can't seem to fathom out how to convert the
postscript file I have created into a pdf file. I am using
Excel 2000 and Adobe acrobat v.5 (full version)

The code I have so far is as follows:

'Select the sheets to send to send to adobe post script'

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Workbook1").Activate
Application.ActivePrinter = "Acrobat Distiller on
Ne00:"
ActiveWindow.SelectedSheets.PrintOut copies:=1,
ActivePrinter:= _
"Acrobat Distiller on Ne00:", printtofile:=True,
Collate:=True, PrToFileName:="FilePath\Filename.ps"
Sheets("PRINT LIST").Select

'This is where I need to convert the above to a pdf and
kill the ps file. '

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail

'.To = emailadresses
'.CC = more emailadresses
'.Subject = "subject"
'.Body = ""
Set objOutlook = CreateObject
("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
.To = "
.Subject = "Monthly Bed occupancy statistics"
.Body = "Dear recipient " & vbCrLf & " Blah Blah
Blah" & vbCrLf & vbCrLf & "Kind Regards"
.Attachments.Add "FilePath\FileName.pdf"
.display
End With


End Sub

I would hugely grateful to anybody who could help me
resolve this problem