Email one worksheet to email address on worksheet

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.

Hi Veronica

..To = ThisWorkbook.Sheets("Sheet1").Range("C1").Value



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Veronica Johnson" wrote in message ...
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.

On Jan 25, 3:05*pm, "Ron de Bruin" wrote:
Hi Veronica

.To = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Veronica Johnson" wrote in ...
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.- Hide quoted text -

- Show quoted text -

Thank you so much, Ron! BTW, your site has been very, very helpful!

your site has been very, very helpful!
You are welcome

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Veronica Johnson" wrote in message ...
On Jan 25, 3:05 pm, "Ron de Bruin" wrote:
Hi Veronica

.To = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Veronica Johnson" wrote in ...
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.- Hide quoted text -

- Show quoted text -

Thank you so much, Ron! BTW, your site has been very, very helpful!