JackInDabox Member
Posts : 10 Join date : 2011-04-08
| Subject: ASP email cdo message help Wed May 11, 2011 11:18 pm | |
| 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.comThe 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
%>
|
|
Exageration Member
Posts : 10 Join date : 2011-03-09
| Subject: Re: ASP email cdo message help Wed May 11, 2011 11:19 pm | |
| (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? |
|
JackInDabox Member
Posts : 10 Join date : 2011-04-08
| Subject: Re: ASP email cdo message help Wed May 11, 2011 11:19 pm | |
| Sorry to get you confused. What I mean is Department_ID. Student_id Dept_ID email 1 1 1@2.com2 2 2@3.com3 1 3@4.comIf 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? |
|
Exageration Member
Posts : 10 Join date : 2011-03-09
| Subject: Re: ASP email cdo message help Wed May 11, 2011 11:20 pm | |
| 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. |
|
JackInDabox Member
Posts : 10 Join date : 2011-04-08
| Subject: Re: ASP email cdo message help Wed May 11, 2011 11:21 pm | |
| (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. |
|
Exageration Member
Posts : 10 Join date : 2011-03-09
| Subject: Re: ASP email cdo message help Wed May 11, 2011 11:22 pm | |
| *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 %>
|
|
Sponsored content
| Subject: Re: ASP email cdo message help | |
| |
|