Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ron DeBruin Macro - Moving Sheet Name from Last Column to Column A | Excel Worksheet Functions | |||
Calendar drop down ala Ron DeBruin in Excel 2003 | Excel Discussion (Misc queries) | |||
Why can't I edit my excel document? Edit buttons shaded. | New Users to Excel | |||
Email question - Ron deBruin | Excel Programming | |||
Sending e-mail - Ron deBruin | Excel Programming |