sql server (alternate): Sending email (recipients) -- sql server (alternate)

Sending email (recipients)

B
6/29/2006 6:46:27 PM

sql server (alternate):

Follow-up to my original post.

Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?

At the moment, I type the email address separated by a semi-colon.

TIA~

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "send@test.com"
objEmail.To = "receive@test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

set objEmail = nothing

Re: Sending email (recipients)

Dan Guzman
6/30/2006 12:00:00 AM

[quoted text, click to view]

No, but you can easily lookup the values in your script to build the
objEmail.To string. Below is an untested example:

Set connection = CreateObject("ADODB.Connection")
connection.Open "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
Set emailAddressRs = _
connection.Execute("SELECT eMailAddress FROM dbo.Contacts")
recipientList = ""
Do While emailAddressRs.EOF = False
If recipientList = "" Then
recipientList = emailAddressRs.Fields(0).Value
Else
recipientList = recipientList & _
";" & emailAddressRs.Fields(0).Value
End If
emailAddressRs.MoveNext
Loop
emailAddressRs.Close
connection.Close

objEmail.To = recipientList

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]