V
Veronica Johnson
Hi all,
I am using Excel 2007. I have a worksheet that I want to email via a
PDF file (I already downloaded the add-in) to the customer that is
listed in cell C5 of this worksheet. I took a look at Ron de Bruin's
site, but I don't know how to tailor the code specifically to email
the sheet to the customer listed on it.
Here is my code:
Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or
range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE"
_
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <>
"" Then
FilenameStr = Application.DefaultFilePath & "\" & _
Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there," & vbNewLine & vbNewLine & _
"Please see the attached PDF file for quote" & vbNewLine &
_
vbNewLine & "Thank you"
On Error Resume Next
With OutMail
.To = ???? 'don't know what goes here
.CC = ""
.BCC = ""
.Subject = "Quote from TKM"
.Body = strbody
.Attachments.Add FilenameStr
.Display
End With
On Error GoTo 0
'Delete the pdf you send
Kill FilenameStr
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "PDF add-in Not Installed"
End If
End Sub
Any help would be appreciated.
I am using Excel 2007. I have a worksheet that I want to email via a
PDF file (I already downloaded the add-in) to the customer that is
listed in cell C5 of this worksheet. I took a look at Ron de Bruin's
site, but I don't know how to tailor the code specifically to email
the sheet to the customer listed on it.
Here is my code:
Sub Mail_ActiveSheet_PDF_Outlook()
'Note: It is easy to change the code to send a workbook, selection or
range.
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE"
_
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <>
"" Then
FilenameStr = Application.DefaultFilePath & "\" & _
Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there," & vbNewLine & vbNewLine & _
"Please see the attached PDF file for quote" & vbNewLine &
_
vbNewLine & "Thank you"
On Error Resume Next
With OutMail
.To = ???? 'don't know what goes here
.CC = ""
.BCC = ""
.Subject = "Quote from TKM"
.Body = strbody
.Attachments.Add FilenameStr
.Display
End With
On Error GoTo 0
'Delete the pdf you send
Kill FilenameStr
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "PDF add-in Not Installed"
End If
End Sub
Any help would be appreciated.