Emailing to schools on our database

[bogorman]

Am trying to write some vbscript coding to send emails to records on our schools database. This is how far I've got. Am not an expert!! :

<%

Dim myConn, myCmd, myRS, firstRecord
dim strSQL2, fldCurrentID, myCmd2 'NEW


Set myConn = Server.CreateObject("ADODB.Connection")
Set myCmd = Server.CreateObject("ADODB.Command")
Set myRS = Server.CreateObject("ADODB.Recordset")


Set Mailer = Server.CreateObject ("SMTPsvg.Mailer")
Mailer.FromName = "Joe's Widgets Corp."
Mailer.FromAddress = "[e-mail]"
Mailer.Subject = "Your Widget Order"
Mailer.BodyText = "Your order was processed."
Mailer.RemoteHost = "mail-fwd.rapidsite.net"

myConn.Open("dsn= ;uid= ;password= ")


strSQL = "SELECT tblselectedschools.fldContact, tblselectedschools.fldemail FROM tblschools RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID WHERE tblselectedschools.fldUserID = " & UserID & " AND tblselectedschools.fldSelected = 1"

With myCmd
.ActiveConnection = myConn

.CommandText = strSQL ' This should be your SQL select statement
.CommandType = 1 'adCmdText
End With

With myCmd2
.ActiveConnection = myConn

.CommandText = strSQL ' This is the SQL statement for UPDATE query to change the fldmessagesent field
.CommandType = 1 'adCmdText
End With

strSQL2 = "UPDATE tblselectedschools SET fldmessagesent=0 WHERE fldID = " & fldCurrentID



myRS.Open myCmd

firstRecord=1

Do While Not myRS.EOF

if Mailer.SendMail then
' Message sent sucessfully

If firstRecord = 1 Then
firstRecord = 0
Mailer.AddRecipient myRS.Fields("fldContact"), myRS.Fields("fldemail")
Else
Mailer.AddBCC myRS.Fields("fldContact"), myRS.Fields("fldemail")

End If

Else
' Message send failure
'WRITE 0 TO fldmessagesent in tblselectedschools

fldCurrentID = myRS.Fields("fldID")
myRS.Open myCmd2
myRS.Close myCmd2


end if


myRS.MoveNext()
Loop

MyFile.Close()
myConn.Close()



%>


The idea is to set fldmessagesent=1 by default and only set it to false(0) if the message is not sent, but having the two myRS.OpenMyCmd... statements nested - will this work, or is there a better way of doing it.

Also, as there may be 1000 emails or more, should they be sent in "blocks" of say 50 at a time. If so, how can I do that?

Anyone's help would be much appreciated

Brian