Here's what I used (it works). Remember, my I'm a beginner, so be nice
----------------
Dim rUserRange As Range
'Get email recipients
Sheets("Email List").Activate
Range("A1").Select 'Column A has one email recipient per row
Selection.CurrentRegion.Select
Set rUserRange = ActiveCell.CurrentRegion 'Load region into an array
'Send out the email
dummy = SendEmail(sBodyData, sSheet_Date, rUserRange)
Application.Quit 'All done so EXIT Excel
----------------------------------------------------------------------------
-------------------
Function SendEmail(the_body As String, the_date As String, rSend_To As
Range)
Dim xloop As Integer
Dim sRecipients As String
'Code bits remarked with ! come from this web page:
http://www.rondebruin.nl/cdo.htm
Dim iMsg As Object '!
Dim iConf As Object '!
Dim Flds As Variant '!
'Create the recipient (iMsg.To) list
endloop = rSend_To.Count 'Total array number
If endloop > 1 Then 'More than one person is being emailed
For xloop = 1 To endloop
sRecipients = sRecipients & rSend_To.Value2(xloop, 1) & "; "
Next
Else 'Only one person is being emailed
sRecipients = sRecipients & rSend_To.Value2
End If
'! Starting here...
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("
http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'You fill in your SMTP server, don't use mine "our_exch_server4"
.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"our_exch_server4"
.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
25
.Update
End With
With iMsg
Set .Configuration = iConf
.To = sRecipients
.CC = ""
.BCC = ""
'Change the .From value to your own
.From = """Reporting Server"" <
[email protected]>"
.Subject = "Booking Status thru " & the_date
.TextBody = the_body
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
'! ...and ending here.
Application.DisplayAlerts = True
End Function
