e-mail a range of cells as the body

[EXCELMACROS]

I want to be able to add a Range of cells to the body of my email, I'm using
the following method but I'm not getting it to work. Does anyone knows what
I'm doing wrong?

For x = 1 To 5
For y = 1 To 5
If Sheets("Values").Range("H" & x) = Date + y And
IsEmpty(Sheets("Values").Range("N" & x)) Then

email = Sheets("Values").Range("H" & x).Offset(0, 4).Select
subj = "test"

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

Set Text = Range("H" & x - 1 & ":K" & x +
4).SpecialCells(xlCellTypeVisible)

With OutMail
.To = email
.CC = ""
.Subject = subj
.body = Text
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Sheets("Values").Range("M" & x) = "e-mail sent"
End If
Next y
Next x

 

[EXCELMACROS]

fyi.. the loops work fine, I'mi getting a Run-time error "Array lower bound
must be zero"

I can make it work if I actually take the body off or just write a "something"

 

[Patrick Molloy]

TEXT looks like a range object.
try changing
.body = Text
to
.body = Text.Value

 

[EXCELMACROS]

it didn't work, it actually sent the email with no body totally blank.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/folder3/mail4.htm

 

[EXCELMACROS]

Thank you, one question, your method is asking me to create an html file, how
private is that? is that going to be published on the web? this is
sensitive information.

 

[Ron de Bruin]

It will create a html file local
Then read the html into the mail
Delete the html

So it will not be published on the web


--

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

Thank you, one question, your method is asking me to create an html file, how
private is that? is that going to be published on the web? this is
sensitive information.

 

[EXCELMACROS]

it works perfectly thank you a lot.