Sending multiple emails by iterating through recordset

Posted on 2007-10-03
Last Modified: 2012-06-21
Am trying to write a vbscript to send multiple emails but cannot get it to work. Here it is:

Dim Mail
Dim objCDOSYSCon , objCDOSYSMail, BodyString, ResponseString, myRS, db, strSQL, strSQL2
Dim mySQLWhere, MonthString, Connection, FullLoginName, Prompt, UserID, myCmd, myCmd2, RecordID  

Set Connection = New clsDBConnection1
UserID = Connection.ToSQL(CCGetUserID(), ccsInteger)

BodyString = "This is a test email sent via LIFEnet"

'This prevents the form from being Cached.
Response.Expires = 0
Response.Expiresabsolute = Now-1

'Create the MailSender Object
Set Mail = Server.CreateObject("Persits.MailSender")
Set myCmd = Server.CreateObject("ADODB.Command")
Set myCmd2 = Server.CreateObject("ADODB.Command")
Set myRS = Server.CreateObject("ADODB.Recordset")

'Specify your SMTP Mail Server
Mail.Host = ""

'Specify the From Address and Name
Mail.From = "matthewo'"
'Mail.FromName = FullLoginName
Mail.FromName = "Matthew O'Gorman, LIFE London"

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

With myCmd
'.ActiveConnection = Connection

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

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

With myCmd2
'.ActiveConnection = Connection

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

myRS.Open myCmd


Do While Not myRS.EOF

RecordID = myRS.Fields("fldRecordID")

if Mail.Send then
' Message sent sucessfully

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

   End If

' Message send failure
'WRITE 0 TO fldmessagesent in tblselectedschools

fldCurrentID = myRS.Fields("fldID")
myRS.Open myCmd2
myRS.Close myCmd2
Set Connection = Nothing
On Error Goto 0

end if



When opening the page, I get:
The connection cannot be used to perform this operation. It is either closed or invalid in this context
on the firstRecord=1  line.
Possibly the problem is due to trying to use the mail connection to open the recordset, and I should define two connections - I am far from being an expert here.

The coding should iterate through the recordset and, where emails can't be sent write to the fldmessagesent field in tblselectedschools. I am not sure whether this is valid. If an email cannot be sent, would this be detected immediately?
Anyway, would appreciate help on this.
Question by:bogorman
    LVL 16

    Accepted Solution

    firstRecord=1 is not the error line.  That's our first mistake.  Check the code again.

    And, "myRS.Open myCmd" -> you are trying to use a command object with recordset object, rather than a connection.  Change to a connection.

    Author Comment

    Sorry for the delay in responding.
    You were quite right. Have altered the coding and it works.
    Have assigned the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    If your app took Google’s lash recently, here are the 5 most likely reasons.
    Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
    This video shows how use content aware, what it’s used for, and when to use it over other tools.
    This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now