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

x
?
Solved

Sending multiple emails by iterating through recordset

Posted on 2007-10-03
2
Medium Priority
?
475 Views
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
Connection.Open
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

firstRecord=1

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")
    Else
     Mail.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
Connection.Close
Set Connection = Nothing
On Error Goto 0


end if


myRS.MoveNext()
Loop

MyFile.Close()
myConn.Close()

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.
0
Comment
Question by:bogorman
2 Comments
 
LVL 16

Accepted Solution

by:
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.
0
 

Author Comment

by:bogorman
ID: 20079105
Hi,
Sorry for the delay in responding.
You were quite right. Have altered the coding and it works.
Have assigned the points.
Thanks
Brian
0

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