DakotaKat
asked on
Include all Article Titles in a Record Set in an email that is sent using a CDOSYSMailer Script
I have a web page that is dynamically executed every day. It checks to see if there are Customer Comments that have been submitted and need to be approved for posting. If there are, it uses CDOSYSMail to generate an email to the Administrator.
I have been able to get everything to work, and I can have it include the title of the first Comment and everything executes just fine. I would however, like to be able to list each Comment submitted and I have not been able to figure out how to do that in a way that will work with this mail script. I can do it in ASP and have it show on the web page just fine, however I have not been able to figure out how to include the same results in this email.
Below is the code for my page.
<%
dim showemaildetail
If Not RsEmailDetails.EOF Or Not RsEmailDetails.BOF Then
showemaildetail = showemaildetail + RsOutstanding.Fields.Item( "Question" ).Value
End If ' end Not RsEmailDetails.EOF Or NOT RsEmailDetails.BOF
Repeat2__index=Repeat2__in dex+1
Repeat2__numRows=Repeat2__ numRows-1
RsEmailDetails.MoveNext()
%>
<%
If RsOutstanding_total <> "0" Then
' ******** E NAG ************
Response.write(" &nbs p; eNAG Notices Sent Include:<BR>************** ********** ********** ****<BR>")
If RsOutstanding_total <> "0" Then
response.write("Q"A Site<BR>")
Set objCDOSYSMail = Server.CreateObject("CDO.M essage")
Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.1.102"
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objCDOSYSCon.Fields.Update
Set objCDOSYSMail.Configuratio n = objCDOSYSCon
objCDOSYSMail.From = "webmaster@Company.com"
objCDOSYSMail.To = "Admin@Company.com"
objCDOSYSMail.BCC = "Bob@Company.com"
objCDOSYSMail.Subject = "Q&A Report"
objCDOSYSMail.HTMLBody = " Hello Mike,<BR><BR>This is your daily reminder that there are currently <B> " & RsOutstanding_total & " </B> questions in the Site that require your attention.<BR><BR>The following question(s) requires your attention.<BR><BR><B>" & showemaildetail & "</B>Please Login to the Web Site at <a href=http://www.site.com>http://www.site.com/admin</a> and reply to them as soon as possible.<BR><BR>Thank you.<BR><BR><BR>********** ********** ********** ********** ********** **<BR>"
objCDOSYSMail.Send
Set objCDOSYSMail = Nothing
Set objCDOSYSCon = Nothing
}
END IF
Else
response.write ("No Reminder emails need to be sent")
End IF
%>
I have been able to get everything to work, and I can have it include the title of the first Comment and everything executes just fine. I would however, like to be able to list each Comment submitted and I have not been able to figure out how to do that in a way that will work with this mail script. I can do it in ASP and have it show on the web page just fine, however I have not been able to figure out how to include the same results in this email.
Below is the code for my page.
<%
dim showemaildetail
If Not RsEmailDetails.EOF Or Not RsEmailDetails.BOF Then
showemaildetail = showemaildetail + RsOutstanding.Fields.Item(
End If ' end Not RsEmailDetails.EOF Or NOT RsEmailDetails.BOF
Repeat2__index=Repeat2__in
Repeat2__numRows=Repeat2__
RsEmailDetails.MoveNext()
%>
<%
If RsOutstanding_total <> "0" Then
' ******** E NAG ************
Response.write(" &nbs
If RsOutstanding_total <> "0" Then
response.write("Q"A Site<BR>")
Set objCDOSYSMail = Server.CreateObject("CDO.M
Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.1.102"
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objCDOSYSCon.Fields.Update
Set objCDOSYSMail.Configuratio
objCDOSYSMail.From = "webmaster@Company.com"
objCDOSYSMail.To = "Admin@Company.com"
objCDOSYSMail.BCC = "Bob@Company.com"
objCDOSYSMail.Subject = "Q&A Report"
objCDOSYSMail.HTMLBody = " Hello Mike,<BR><BR>This is your daily reminder that there are currently <B> " & RsOutstanding_total & " </B> questions in the Site that require your attention.<BR><BR>The following question(s) requires your attention.<BR><BR><B>" & showemaildetail & "</B>Please Login to the Web Site at <a href=http://www.site.com>http://www.site.com/admin</a> and reply to them as soon as possible.<BR><BR>Thank you.<BR><BR><BR>**********
objCDOSYSMail.Send
Set objCDOSYSMail = Nothing
Set objCDOSYSCon = Nothing
}
END IF
Else
response.write ("No Reminder emails need to be sent")
End IF
%>
ASKER
What you suggested makes perfect sense to me, however when I try it I get what you see below, for some reason it excludes printing the questions out and leaves a space. I have played with this for a few hours so i'm tossing this back in to see if you have any more suggestions. I will include a copy of the file as well.
Hello Bob,
This is your daily reminder that there are currently 3 questions in the Question and Answer Web that require your attention.
The following question(s) requires your attention.
QUESTIONS HERE
I hope this will work. we will see here in a minute.
Please Login to the Web Site at http://www.company.com/admin and reply to them as soon as possible.
Thank you.
************************** ********** ********** ******
This email was automatically generated by the Web Server.
mailproblem.txt
Hello Bob,
This is your daily reminder that there are currently 3 questions in the Question and Answer Web that require your attention.
The following question(s) requires your attention.
QUESTIONS HERE
I hope this will work. we will see here in a minute.
Please Login to the Web Site at http://www.company.com/admin and reply to them as soon as possible.
Thank you.
**************************
This email was automatically generated by the Web Server.
mailproblem.txt
are you getting any of the messages?
Or none of them?
Or none of them?
ASKER
The email processes, and I get everything I put in the post. The only thing missing is the Question titles its skipping them. There are currently 3 Questions in the system and when the email is generated what I receive is this.....
************************** ********** ********** **
Hello Bob,
This is your daily reminder that there are currently 3 questions in the Question and Answer Web that require your attention.
The following question(s) requires your attention.
/////// QUESTIONS MISSING FROM HERE ///////
I hope this will work. we will see here in a minute.
Please Login to the Web Site at http://www.company.com/admin and reply to them as soon as possible.
Thank you.
************************** ********** ********** ******
This email was automatically generated by the Web Server.
**************************
Hello Bob,
This is your daily reminder that there are currently 3 questions in the Question and Answer Web that require your attention.
The following question(s) requires your attention.
/////// QUESTIONS MISSING FROM HERE ///////
I hope this will work. we will see here in a minute.
Please Login to the Web Site at http://www.company.com/admin and reply to them as soon as possible.
Thank you.
**************************
This email was automatically generated by the Web Server.
I am going to do what I did yesterday to get it to work.
I am not going to include none of the Generated code that is at the top.
I am strickly going to work with the CDOSYS Email Code only. OK?
This way, I can run it and test it and make it work to my liking.
(Which is the way you want it to work)
Can you please provide me with a sample of your DB?
Just the table the gets the [Questions] from
Just through in about 3 dummy questions for me.
This way I am working with the correct Table and Fields
So that you will not have to do nothing once I complete the code except
But Run it.
Thanks
Carrzkiss
I am not going to include none of the Generated code that is at the top.
I am strickly going to work with the CDOSYS Email Code only. OK?
This way, I can run it and test it and make it work to my liking.
(Which is the way you want it to work)
Can you please provide me with a sample of your DB?
Just the table the gets the [Questions] from
Just through in about 3 dummy questions for me.
This way I am working with the correct Table and Fields
So that you will not have to do nothing once I complete the code except
But Run it.
Thanks
Carrzkiss
ASKER
I am attaching an excel dump of that table as well as a .gif file with is a screen capture of the Database Design. Everything oporates out of the one table dbo.tblHRQA. That table lives in a larger general application SQL Database.
Let me know if you need anything else from me and Thank you for your help.
dbo.tblHRQA.xls
tabledesign.gif
Let me know if you need anything else from me and Thank you for your help.
dbo.tblHRQA.xls
tabledesign.gif
here you go.
This is BAREBONES.
Works likes a charm.
Copy and paste and add it into the same directory as your Database.
And you are set to go.
(Or change if you are using SQL Server or other DB)
Carrzkiss
This is BAREBONES.
Works likes a charm.
Copy and paste and add it into the same directory as your Database.
And you are set to go.
(Or change if you are using SQL Server or other DB)
Carrzkiss
<%@LANGUAGE="VBSCRIPT"%>
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Q_24421372.mdb") & ";"
objConn.Open
Set RsOutstanding=CreateObject("ADODB.RecordSet")
getSQL = "SELECT * FROM dbo WHERE DisplayStatus = 2"
RsOutstanding.Open getSQL, objConn, 1, 1, 1
%>
<%
' ******** E NAG FOR HR Question and Answers Web ************
Response.write(" eNAG Notices Sent Include:<BR>**************************************<BR>")
'If RsOutstanding_total <> "0" Then
'response.write("Q"A Site<BR>")
Set objCDOSYSMail = Server.CreateObject("CDO.Message")
Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.domain.net"
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objCDOSYSCon.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 'Use SSL for the connection (True or False)
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objCDOSYSCon.Fields.Update
Set objCDOSYSMail.Configuration = objCDOSYSCon
objCDOSYSMail.From = "webmaster@domain.com"
objCDOSYSMail.To = "ee@domain.com"
'objCDOSYSMail.BCC = "webmaster@company.com"
objCDOSYSMail.Subject = "Q&A Report"
objCDOSYSMail.HTMLBody = " Hello Bob,<BR><BR>This is your daily reminder that there are currently <B> " & RsOutstanding_total & " </B> questions in the Question and Answer Web that require your attention.<BR><BR>The following question(s) requires your attention.<BR><BR><B>"
objCDOSYSMail.HTMLBody = objCDOSYSMail.HTMLBody & "QUESTIONS HERE<BR><BR>"
While Not RsOutstanding.EOF
objCDOSYSMail.HTMLBody = objCDOSYSMail.HTMLBody & (RsOutstanding("Question")) & "<br>" & "<br>"
RsOutstanding.MoveNext
Wend
objCDOSYSMail.HTMLBody = objCDOSYSMail.HTMLBody & "<br/><br/> I hope this will work. we will see here in a minute.<BR><BR>"
objCDOSYSMail.HTMLBody = objCDOSYSMail.HTMLBody & "</B>Please Login to the Web Site at <a href=http://www.site.com/admin>http://www.site.com/admin</a> and reply to them as soon as possible.<BR><BR>Thank you.<BR><BR><BR>****************************************************<BR><B><I>This email was automatically generated by the Web Server</I></B>."
objCDOSYSMail.Send
Set objCDOSYSMail = Nothing
Set objCDOSYSCon = Nothing
'Else
'response.write ("No Reminder emails need to be sent")
'End IF
%>
<%
RsOutstanding.Close()
Set RsOutstanding = Nothing
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much! I switched the connection back to SQL and it picked up the database and is running just fine. Thanks again!
You are very wecome.
Enjoy and keep on coding.
Carrzkiss
Author Comments:
Thank you so much! I switched the connection back to SQL and it picked up the database and is running just fine. Thanks again!
Enjoy and keep on coding.
Carrzkiss
Author Comments:
Thank you so much! I switched the connection back to SQL and it picked up the database and is running just fine. Thanks again!
Repalce your Varibles as needed
Carrzkiss
Open in new window