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__index+1
Repeat2__numRows=Repeat2__numRows-1
RsEmailDetails.MoveNext()

%>

<%

If RsOutstanding_total <> "0" Then

' ******** E NAG ************

Response.write("    eNAG Notices Sent Include: ************************************** ")

 If RsOutstanding_total <> "0" Then
 response.write("Q"A Site ")

Set objCDOSYSMail = Server.CreateObject("CDO.Message")
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.Configuration = 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, This is your daily reminder that there are currently " & RsOutstanding_total & " questions in the Site that require your attention. The following question(s) requires your attention. " & showemaildetail & "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. Thank you. **************************************************** "

objCDOSYSMail.Send

Set objCDOSYSMail = Nothing
Set objCDOSYSCon = Nothing
}

 END IF

Else
response.write ("No Reminder emails need to be sent")
End IF
 %>

Wayne Barron

This was a fun one.
Repalce your Varibles as needed

Carrzkiss


 
 
While Not objRs1.EOF
myMail.HTMLBody = myMail.HTMLBody & (objRS1("MyName"))  & "<br>" & "<br>"
objRs1.MoveNext
    Wend
myMail.HTMLBody = myMail.HTMLBody & "<br/><br/> I hope this will work. we will see here in a minute."

Open in new window

DakotaKat

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

Wayne Barron

are you getting any of the messages?
Or none of them?

DakotaKat

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.

Wayne Barron

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

DakotaKat

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

Wayne Barron

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

<%@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("&nbsp;&nbsp;&nbsp; eNAG Notices Sent Include:<BR>**************************************<BR>")
 
	'If RsOutstanding_total <> "0" Then
	'response.write("Q&quot;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 =  "&nbsp;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
%>

Open in new window

ASKER CERTIFIED SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DakotaKat

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!

Wayne Barron

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!