macro for excel addin (pdf maker) to send via OUTLOOK 1

[jackeroo75]

I have a adobe PDFMAKER icon for office 2003. I open a excel document in EXCEL 2003 then I use the adobe PDF (an icon placed in excel or add-in) to covert the excel document and then send as attachment to outlook 2003. I can do this manually, but would like to automate using a macro in EXCEL. I tried using a recorded macro but it doesn't capture that.

 

[yooneek]

I found this thread thread707-1249943 and took it a step further...

Function WrkbkNm()
'Trim the ActiveWorkbook Name to remove file extensions
'wrbkName Declared variable
With ActiveWorkbook
If InStr(1, .Name, ".") > 0 Then
wrbkName = Left(.Name, WorksheetFunction.Search(".", .Name) - 1)
Else
wrbkName = .Name
End If
End With
End Function

Sub Print2PDF()
'Adapted from macropod's post in thread707-1249943
'tempPath is a defined Constant in the module "D:\"
Dim oSheet As Worksheet
Dim oPDF As PdfDistiller
Dim TmpPSFile, PDFFile, LogFile As String

Set oSheet = ActiveSheet
Set oPDF = New PdfDistiller

TmpPSFile = tempPath & "TmpPSFile.ps"

WrkbkNm

PDFFile = tempPath & wrbkName & ".pdf"
LogFile = tempPath & wrbkName & ".log"

oSheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, _
collate:=True, PrToFileName:=TmpPSFile
oPDF.FileToPDF TmpPSFile, PDFFile, ""

Kill TmpPSFile
Kill LogFile
End Sub

Sub PDF_Email()
'
Dim attName as String

Print2PDF

'Assign attachment name
attName = wrbkName & ".pdf"

'Clean up the Workbook Name for the Subject line
'This was specific for my case
wrbkName = Replace(wrbkName, "_", " ")
wrbkName = Replace(wrbkName, "+", "")

Set OutMail = CreateObject("Outlook.Application").CreateItem(0)

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = wrbkName & " — Your Message"
.Attachments.Add tempPath & attName
.Recipients.ResolveAll
.Display
End With

wrbkName = ""

End Sub


*** Must have Acrobat Distiller selected in VBA->Refferences


Actually, I've been wanting to do this for a while, just hadn't taken the time to do it.