Edit to CDO email (Ron deBruin)

[Steph]

Hi everyone. Thanks to Ron deBruin, I use code below to send e-mails from
excel. Thanks to Bernie D, I have the following variable:
Set var3 = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))

For i = 1 To var3.Cells.Count
'MsgBox var3(i).Value
Next i

This variable range contains data from several cells. What I am trying to
do is take the contents of each cell and put that in the body of a email. I
tried a few different ways, but can't figure it out. Any ideas? Thanks!!

Sub Email()

Dim iMsg As Object
Dim iConf As Object
Dim cell As Range
Dim var3 As Range
Dim strbody As String
Dim i As Integer

Application.ScreenUpdating = False

Set var3 = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))

For i = 1 To var3.Cells.Count
'MsgBox var3(i).Value
Next i

Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1
Set Flds = iConf.Fields
With Flds

..Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"mail.company.com"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

Set iMsg = CreateObject("CDO.Message")
With iMsg
Set .Configuration = iConf
.To = """AutoSend"" <[email protected]>"
.From = """AutoSend"" <[email protected]>"
.Subject = "Test"
'******Adjust the line below to write contents of all cells
within variable
.textBody = "Header Statement..." & vbCrLf & var3(i) &
vbCrLf
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing

Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi Steph

See
http://www.rondebruin.nl/cdo.htm#Tips

 

[Guest]

Hi,

Something like this ?

strMessg=""
For i = 1 To var3.Cells.Count
strMessg=Strmessg + var3(i).value ' Cells into single text string
Next i


.textBody = "Header Statement..." & vbCrLf & strMessg & vbCrLf

HTH

 

[Steph]

Beautiful! Thanks so much Ron!!

Hi Steph

See
http://www.rondebruin.nl/cdo.htm#Tips

 

[Guest]

I think the rest of us just try too hard. We answer qustions as they are
asked... You just anticipate all of the questions and then point people to
your web site... ;-)

P.S. I love the site.