ASP email cdo message help

Member Posts : 10 Join date : 2011-04-08

Hello,

I posted a similar message like this last week. This is something new that I can't figure out.

I created a script to remind students to turn in their reports. Whenever I open this page, the script runs and students will receive an email to remind them to turn in their reports.

On this script, I got the emails in this format, a@b.com c@d.com, e@d.com

The strMail should store these emails in this format: a@b.com, c@d.com, e@d.com so that when I open this page, it will send to students who met the criteria.

Currently, the email message send each student a separate email. However, it should send an email to students who have the same ID

For example, the email should send to a@b.com and e@d.com on ONLY one message and the email should send to c@d.com one message.

ID Email Date

1 a@b.com 5/4/2011

2 c@d.com 5/4/2011 1

e@d.com 5/4/2011

Can anyone please help?

Code:: <%

Set oRs = Server.CreateObject("adodb.recordset")

strSQL = " select Student_ID, Student_Email + '@yahoo.com' AS Email from Students Where dueDate='5/4/2011' Group By Student_ID, Student_Email, dueDate; "

oRs.Open strSQL, myConn

%>

<br /><h2>Student_Report Reminder</h2>

<%

Dim NewMailObj, strBody, sMail, aEmail

aEmail = aEmail & sEmail & ","

if not oRs.eof then

do until oRs.eof

sID = oRs("Student_ID")

sEmail = oRs("Email")

sDate = oRs("dueDate")

aEmail = aEmail & sMail & ","

Set NewMailObj = CreateObject("CDO.message")

NewMailObj.From = "Student_Report"

NewMailObj.To = aEmail

NewMailObj.Subject = "Student Report"

NewMailObj.HTMLBody = "Reminder for the following students:<br><br><table><tr><td>ID</td><td>Email</td><td>Date</td></tr><tr><td>"&sID&"</td><td>"&sEmail&"</td><td>"&sDate&"</td></tr></table>" & vbCrLf

NewMailObj.Send

end if

oRs.MoveNext

loop

End If

%>

Member Posts : 10 Join date : 2011-03-09

(1) You don't show what the id is for e@d.com

(2) Your SQL statement code says that *ALL* email addresses will be "@yahoo.com".

(3) What is the purpose of the GROUP BY in your SQL statement? Almost surely that is wrong. And you are MISSING the ORDER BY.

(4) WHY would you have a system where more than one student has the same Student_ID????
Surely that is a terrible, terrible mistake.

(5) Why is it important to group students together, instead of just sending one mail per student?

Member Posts : 10 Join date : 2011-04-08

Sorry to get you confused. What I mean is Department_ID.

Student_id Dept_ID email
1 1 1@2.com
2 2 2@3.com
3 1 3@4.com

If there are 3 records that meet the query, then the message will send to students who have the same Dept_ID.

1@2.com and 3@4.com have the same Dept_ID so they receive ONLY one message together, instead of each message separately. For 2@3.com is the only one so he/she will receive one message.

Currently, the code sends out an email for each return record. How do I fix the code so that the email sends out for each department. For example, Dept 1 that has 2 students, there is ONLY one message sends out.

If A and B are on the same Department, then the code should send ONE email to A and B.
Currently, the code sends 2 email messages to each of them separately.

If 5 departments with 3 students each:

It should send 5 emails total with the emails going to the groups of the three student addresses in each department?

Member Posts : 10 Join date : 2011-03-09

Okay but you didn't answer two major questions:

(2) Your SQL statement code says that *ALL* email addresses will be "@yahoo.com". Is this really correct? Your school only allows students to use Yahoo for their email server?

(3) What is the purpose of the GROUP BY in your SQL statement? Almost surely that is wrong. And you are MISSING the ORDER BY.

Member Posts : 10 Join date : 2011-04-08

(2) This is just an example of emails.

(3) Oh, I don't need Group By.
select Student_ID, Student_Email + '@yahoo.com' AS Email from Students Where dueDate='5/4/2011';

Thanks.

Member Posts : 10 Join date : 2011-03-09

*sigh* WHY would you *need* an "example" of emails? Why wouldn't you store the *REAL* email address in the DB?

Ah, well.

Code:: <%
DueDate = #5/4/2011# ' or get this from Request.Form or or or

SQL = "SELECT Student_ID, Dept_ID, Student_Email AS email " _
& " FROM Students " _
& " WHERE dueDate='" & DueDate & "' " _
& " ORDER BY Dept_ID, Student_ID"

Set RS = myConn.Execute(SQL)

' initialize for the email accumulation:
priorDept = RS("Dept_ID")
toList = ""
html = ""

Do While True
needDump = RS.EOF
If Not needDump Then needDump = priorDept <> RS("Dept_ID")
If needDump
Set mail = CreateObject("CDO.message")
mail.Subject = "Student Report"
mail.From = "...this MUST be a valid email address..."
mail.To = Mid(toList,2)
mail.HTMLBody = "Reminder for " & DueDate _
& " for the following students in department " & RS("Dept_ID") _
& ":<br><br>" & vbNewLine
& "<table border=""1""><tr><td>ID</td><td>Email</td></tr>" & vbNewLine _
& html _
& "</table>" & vbNewLine
mail.Send
Set mail = Nothing
' reinitialize the two accumulators:
toList = ""
html = ""
End If

If RS.EOF Then Exit Do ' we just dumped out the last email and are done

' more records, so add another student to the email list and html table data:
sEmail = RS("email")
sID = RS("Student_ID")

toList = toList & ";" & sEmail ' or comma...but I think semicolon is right
html = html & "<tr><td>" & sID & "</td><td>" & sEmail & "</td></tr>" & vbCrLf

RS.MoveNext

Loop
%>

» How should i reply to this hate message

ASP email cdo message help

ASP email cdo message help

Similar topics

Similar topics