Okay I set up a test Table with 10 email addy's in it. I will try and send it to 5 at a time.

I am not sure how to do this exactly

While not RS.EOF
   Send mail one by one.
   And store the mailId or KeyFieldValue in an array.
WEND

I am not good with array's at all :(

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Can this be done?

Couldn't I just loop through the entire record sending one email to each record and then just close the record set and have .eof = true and move on to the next page.

Or would this be way too much work for the server?

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Here's what I would do:

1. SELECT TOP 50 FROM Bla bla WHERE mailsent=0
 (assuming your database supports Top)

2. Loop through the recordset and send out your e-mail. Inside the loop, keep track of the IDs of the customer in a comma separated string:

If Len(CustomerIDs) > 0 Then
  CustomerIDs = CustomerIDs & ", "
End If
CustomerIDs = CustomerIDs & MyRecordset("CustomerID")

3. After the loop, update your database by using the CustomerIDs string. At the end of the loop, that string will hold something like this:

1, 3, 7, 12, 45

Use the statement that Vijay showed you earlier:

"UPDATE YourTable SET Mailsent=1 WHERE CustomerID IN (" & CustomerIDs & ")"

This will set the MailSent flag to 1 for all the customers you just e-mailed.

4. Repeat step 1 through 3 while the reocrdset is not EOF (that is, there are still messages left to send / MailSent = 0)

I introduced the Date column because I wasn't sure how and when you send out your e-mail. Suppose some automatic routine sets all the MailSent flags to 0 again, in the middle of your code loop. That would cause the mail to be sent again.

However, the solution Vijay proposed, where you loop your code until the recordset is EOF and then immediately set all the MailSent flags back to 0 seems to make a lot of sense. This way, you only need to run the page once, to get it all done.

Is scheduling a script instead of using an ASP page an option for you?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Have you considered using a list server. The server ought to be able to queue up whatever it can handle while crack listing the message to individual recipients. That way you only have to send to one address.

Hi Dave,

That option is ruled out in Morpheus's case, as he is sending personalised content for every recipient. Please read my 3rd previous post to this. Also read Morpheus's original post, where the code would show that he uses personalised content for every recipient. So I dont think that can help him.

Cheers!

_________________________
-Vijay G
Strive for Perfection

Sorry I didn't get back, I was away all weekend. Thanks Guys for your help, To Imar, I don't think scheduling a script is an option for us because I don't know when it will go out each day, I guess it could be set to send the newsletter at 5pm when everyone leaves for the day, but I am not sure if they would want that and also I have never done that before :(

I am going to work on the 50 at a time and see if that does the trick. I will let you know how it goes.

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Thanks for the help guys but I am getting stuck on this one line

NewsletterId = NewsletterId & RS("NewsletterId")

It says Exception occurred for this line and I am not sure why, is this the proper syntax?

Thanks

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

Can you post some more code? I can't see anything wrong with this single line. Posting the exact error message may help as well.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Okay Imar I was using your example that you and vijay helped me with.

SQLStr="SELECT top 50 * FROM ORNewsletterTest"
Set RS=Server.CreateObject("ADODB.Recordset")
RS.Open SQLStr,Application("DBConn"),1,2,&H0001


while not rs.eof

Body of the Email

If len(NewsletterId) > 0 Then
    NewsletterId = NewsletterId & ", "
end if
    NewsletterId = NewsletterId & RS("NewsletterId")

    wend

    update "ORNewsletterTest set Mailsent=1 where NewsletterId In (" & NewsletterId & ")"

Maybe I don't have this in the right order, but I left off the code to set all the mailsent back to 0 because I want to make sure that it is working, but it was getting stuck on the line above

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

What error do you get?

Imar