email multiple worksheets

[Jeff]

I am using Ron de Bruin Outlook object model (body)
http://www.rondebruin.nl/sendmail.htm This works very well thanks!Ron
I would like to add a second sheet to the bottom of the emil (sheet1). How
would you add a second sheet to the bottom of the body of the email?
Thanks! Jeff

 

[Ron de Bruin]

Hi Jeff

Use it like this.
This example add sheet1 and sheet2 in the body of the mail

Sub Mail_Sheet_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim rng2 As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
Set rng2 = Nothing
Set rng = Sheets("Sheet1").UsedRange
Set rng2 = Sheets("Sheet2").UsedRange

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

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng) & "<br><br>" & RangetoHTML(rng2)
.display 'or use .Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Jeff]

That is what I was looking for. Thanks again Ron!
Jeff

 

[Ron de Bruin]

You are welcome

--

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

 

[Courtney]

Hi Ron,

I am trying to use the code above to copy a variable number of worksheets into the body of an email. So far what I have is working -- I am running into an issue with the line of code that pastes the range into the body of the email:

HTMLBody = RangetoHTML(rng) & "<br>" & RangetoHTML(rng2) & "<br>" & RangetoHTML(rng3)

It works great until there are less than three worksheets -- then it creates a blank email. This workbook could contain anywhere from one to ten worksheets, so I need it to ignore ranges that don't exist.

Any thoughts? Thanks so much in advance!!