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!
|