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.CreateOb ject("ADODB.Con nection")
Set myCmd = Server.CreateOb ject("ADODB.Com mand")
Set myRS = Server.CreateOb ject("ADODB.Rec ordset")


Set Mailer = Server.CreateOb ject ("SMTPsvg.Maile r")
Mailer.FromName = "Joe's Widgets Corp."
Mailer.FromAddr ess = "sales@joeswidg ets.com"
Mailer.Subject = "Your Widget Order"
Mailer.BodyText = "Your order was processed."
Mailer.RemoteHo st = "mail-fwd.rapidsite.n et"

myConn.Open("ds n= ;uid= ;password= ")


strSQL = "SELECT tblselectedscho ols.fldContact, tblselectedscho ols.fldemail FROM tblschools RIGHT JOIN tblselectedscho ols ON tblschools.fldS CHOOL_ID = tblselectedscho ols.fldSCHOOL_I D WHERE tblselectedscho ols.fldUserID = " & UserID & " AND tblselectedscho ols.fldSelected = 1"

With myCmd
.ActiveConnecti on = myConn

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

With myCmd2
.ActiveConnecti on = myConn

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

strSQL2 = "UPDATE tblselectedscho ols 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.AddRecip ient myRS.Fields("fl dContact"), myRS.Fields("fl demail")
Else
Mailer.AddBCC myRS.Fields("fl dContact"), myRS.Fields("fl demail")

End If

Else
' Message send failure
'WRITE 0 TO fldmessagesent in tblselectedscho ols

fldCurrentID = myRS.Fields("fl dID")
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