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.

Email one worksheet to email address on worksheet
 

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.

Email one worksheet to email address on worksheet
 

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!

Email one worksheet to email address on worksheet
 

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!

Ron, I have the same problem. I have added your code to my program and still cannot get it to work. I am using outlook 2003 and excel 2003. This is what I entered in my program
Sub Mail_Workbook_1()
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Flagged Order"
.Body = "New Flagged Order!"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I want this to be sent after it is saved.