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



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



"Steph" <[email protected]> wrote in message news:[email protected]...
> 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
>
>

[Toppers]

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"" <[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
>
>
>

[Steph]

Beautiful! Thanks so much Ron!!

"Ron de Bruin" <[email protected]> wrote in message
news:%[email protected]...
> Hi Steph
>
> See
> http://www.rondebruin.nl/cdo.htm#Tips
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Steph" <[email protected]> wrote in message
news:[email protected]...
> > 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
> >
> >
>
>

[Jim Thomlinson]

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
>
>
>