This topic is locked

Send email (and include all records, not just one)

10/5/2006 2:44:33 PM
ASPRunnerPro General questions
cagleyleslie author

I'm using Events to send an email. I would like all the records displayed to be sent. As my code is now... I just get two rows of data sent. How can I add some sort of Loop to my code? Thanks!
Sub ExportOnLoad(strSQL)
Dim objCDOSYSCon

Set objCDOSYSMail = Server.CreateObject("CDO.Message")

Set objCDOSYSCon = Server.CreateObject ("CDO.Configuration")

objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver";) = "server"

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 = "user@company.com"

objCDOSYSMail.Bcc = ""

objCDOSYSMail.ReplyTo = ""

objCDOSYSMail.Subject = "Backup Status for " & FormatDateTime(rs("BackupDate"),1)

objCDOSYSMail.HTMLBody =

"<table cellpadding=6 border=1 bordercolor=black>

<tr><td>Server</td>

<td>Result</td>

<td>Posted By</td>

<td>Backup Date</td>

<td>FFB</td>

<td>Error Cause</td>

<td>Errors</td>

<td>Warnings</td>

<td>JobNo</td>

<td>Comments</td>

</tr><tr><td>" & rs("ServerName") & "</td>

<td>" & rs("Result") & "</td>

<td>" & rs("PostedBy") & "</td>

<td>" & rs("BackupDate") & "</td>

<td>" & rs("FFB") & "</td>

<td>" & rs("ErrorCause") & "</td>

<td>" & rs("Errors") & "</td>

<td>" & rs("Warnings") & "</td>

<td>" & rs("JobNo") & "</td>

<td>" & rs("Comments") & "</td></tr>

<tr><td>&nbsp;</td>

<td>" & rs("FollowupResult") & "</td>

<td>" & rs("FollowupPostedBy") & "</td>

<td>" & rs("FollowupBackupDate") & "</td>

<td>" & rs("FollowupFFB") & "</td>

<td>" & rs("FollowupErrorCause") & "</td>

<td>" & rs("FollowupErrors") & "</td>

<td>" & rs("FollowupWarnings") & "</td>

<td>" & rs("FollowupJobNo") & "</td>

<td>" & rs("FollowupComments") & "</td>

</tr></table>"

objCDOSYSMail.Send

Set objCDOSYSMail = Nothing

Set objCDOSYSCon = Nothing
End Sub

J
Jane 10/6/2006

Hi,
to send more then one record in the email use following code:

set rs = CreateObject("ADODB.Recordset")

rs.Open "select count() from TableName", dbConnection
set rsOld = CreateObject("ADODB.Recordset")

email="jane@xlinesoft.com"

message=""

subject="Sample subject"

strSQL = "select
from TableName"

rsOld.Open strSQL, dbConnection
NumberOfRecords=0

while not rsOld.eof and NumberOfRecords<rs(0)

for i=0 to rsOld.Fields.Count-1

if not IsBinaryField(rsOld.Fields(i)) then _

message = message & rsOld.Fields(i).Name & " : " & rsOld(rsOld.Fields(i).Name) & vbcrlf

next

NumberOfRecords = NumberOfRecords+1

wend

rsOld.Close

set rsOld = Nothing
sendmail email, subject, message
End Sub

cagleyleslie author 10/6/2006

THANK you for your help! Is there any way I can make this send as html email? I've been trying to figure it out, but so far am having no luck. Thanks again!

Sergey Kornilov admin 10/8/2006