email multiple worksheets

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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