[Last Call] Learn how to a build a cloud-first strategyRegister Now


Sending multiple emails by iterating through recordset

Posted on 2007-10-03
Medium Priority
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 = "mailA28.webcontrolcenter.com"

'Specify the From Address and Name
Mail.From = "matthewo'gorman@lifecharity.org.uk"
'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

golfDoctor earned 2000 total points
ID: 20009578
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

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are like me and like multiple layers of protection, read on!
Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

831 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