Edit to CDO email (Ron deBruin)
 

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"" "
.From = """AutoSend"" "
.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

Edit to CDO email (Ron deBruin)
 

Hi Steph

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steph" wrote in message ...
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"" "
.From = """AutoSend"" "
.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

Edit to CDO email (Ron deBruin)
 

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" wrote:

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"" "
.From = """AutoSend"" "
.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

Edit to CDO email (Ron deBruin)
 

Beautiful! Thanks so much Ron!!

"Ron de Bruin" wrote in message
...
Hi Steph

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steph" wrote in message
...
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"" "
.From = """AutoSend"" "
.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

Edit to CDO email (Ron deBruin)
 

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.

"Ron de Bruin" wrote:

Hi Steph

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steph" wrote in message ...
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"" "
.From = """AutoSend"" "
.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