sending records of certain id value

[derwent]

I have to grab data out of a DB and email it to someone, but only if the ID's of the records are between 250 and 300 (so I will end up sending 50 emails).

I have written the email script using cdosys but this will send only one email, how do I get it to check through DB then send seperate emails for those of certain ID values?

 

[monksnake]

Do you have your connection to the database established?

If so, can you show what you have so far?

<.

 

[DotNetGnat]

can you show us your code?

-DNG

 

[derwent]

Sure

<!--#include virtual="/include/dbconn.asp"-->
<%
dim emailAddress

   strTo = "emailofstaff@company.com"
   strFrom = "website application"
   strBCC = "myemail@company.com"
   strSubject = "Emails from database"

   counter = rs("ID")
			
			dim strContent
			dim myMail

   strContent = "this bit cut cos the email is BIG"
			

   Set myMail = CreateObject("CDONTS.NewMail")

    myMail.From = emailAddress
    myMail.To = strTo
    myMail.Cc = strCC
    myMail.Subject = strSubject
    myMail.BodyFormat = 0
    myMail.MailFormat = 0
    myMail.Body = strContent
            
    myMail.Send
            
    Set myMail = Nothing
%>

My thought would be to set a counter, when the counter is between 250 and 300 send the email?!?!?!

 

[monksnake]

The question can be answered more easily if we knew what
dbconn.asp contains. You don't have to keep your password and login info on the page, but I have no idea what information is being supplied from your SSI.

<.

 

[derwent]

It is just a standard connection script

<%

Dim cs, objConn
	Set objConn=Server.CreateObject("ADODB.Connection")
	cs = "DRIVER={SQL Server}; SERVER=servername; UID=usename; PWD=password; DATABASE=dbname; REGIONAL=NO'"
	objConn.open cs

%>

 

[DotNetGnat]

I am not sure if I totally understood your requirements..but try something like this:

Note: the below code is free hand written...check out of errors...

<%

Dim cs, objConn, sql, rs, arrResultSet 
    Set objConn=Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.RecordSet")
    cs = "DRIVER={SQL Server}; SERVER=servername; UID=usename; PWD=password; DATABASE=dbname; REGIONAL=NO'"
    objConn.open cs
sql = "Select email from mytable where ID BETWEEN 250 AND 300"
rs.Open sql, objConn
If Not rs.EOF
arrResultSet = rs.GetRows()
End If

Set rs= Nothing
objConn.Close
Set objConn= Nothing

Dim iCounter, emailids
emailids=""
For iCounter = 0 to ubound(arrResultSet, 2)
emailids = emailids & arrResultSet(1, iCounter) & ";"
Next

%>

now you have yourr string emailids ready that holds 50 email addresses which you can pass it to the mail function...

hope this gives you a starting point...

post back with any questions...

-DNG

 

[derwent]

so do I put the email script in between the for and next lines to send my boss an email with the content of each row?

 

[DotNetGnat]

no you need to put the email script after the whole above code that I posted...

you can just say...

myMail.To = emailids

please check for errors...also use response.write statements for debugging purposes...

-DNG

 

[derwent]

The to address has to stay as my boss as al emails need to go to her.

This is what I have

<!--#include virtual="/include/dbconn.asp"-->
<%

sql = "Select email from mytable where ID BETWEEN 250 AND 300"
rs.Open sql, objConn
If Not rs.EOF
arrResultSet = rs.GetRows()
End If

Set rs= Nothing
objConn.Close
Set objConn= Nothing

Dim iCounter, emailids
emailids=""
For iCounter = 0 to ubound(arrResultSet, 2)
emailids = emailids & arrResultSet(1, iCounter) & ";"
Next

dim emailAddress

   strTo = "emailofstaff@company.com"
   strFrom = "website application"
   strBCC = "myemail@company.com"
   strSubject = "Emails from database"
            
            dim strContent
            dim myMail

   strContent = "this bit cut cos the email is BIG"
            

   Set myMail = CreateObject("CDONTS.NewMail")

    myMail.From = emailAddress
    myMail.To = strTo
    myMail.Cc = strCC
    myMail.Subject = strSubject
    myMail.BodyFormat = 0
    myMail.MailFormat = 0
    myMail.Body = strContent
            
    myMail.Send
            
    Set myMail = Nothing
%>