rcowen00
asked on
Email body with dynamic bullet points
I have a stored procedure that produces the following results and puts it into an email. As you can see most of the data is repeated except for the last column (Rev) that can have multiple records. I want the email to bullet point Rev, but I am not sure how to have the code loop through just the Rev. Right now it is reflecting the first bullet and that is it.
Private Sub RevisionEmail()
Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
Try
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = cnn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.GetOrderRevisionDetails_Email"
cmd.Parameters.Add("@OrdersProductKey", Data.SqlDbType.Int).Value = Session("OrdersProductKey")
cnn.Open()
Dim dt As New System.Data.DataTable
Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
dr.Read()
If dr.HasRows Then
Const ToAddress As String = "provaltesting@tcvaluations.com"
'(1) Create the MailMessage instance
Dim mm As System.Net.Mail.MailMessage = New System.Net.Mail.MailMessage()
'(2) Assign the MailMessage's properties
mm.To.Add(New System.Net.Mail.MailAddress(ToAddress))
mm.From = New System.Net.Mail.MailAddress(System.Configuration.ConfigurationManager.AppSettings("FromEmailAddress"))
mm.Subject = "***Please address QC Request(s) ASAP <%Address%>"
mm.Body = "<table style='width:100%;'><tr><td>" +
"Hi <%Name%>,<br /><br />" +
" Please return to our web site, address the following QC requests and resubmit this report by <%DueDay%> - <%DueDate%> Pacific Time <%DueTime%>. Thank you! <br />" +
"</td> </tr>" +
"<tr style='background-color: #006699'><td style='color: #FFFFFF; font-weight: bold; font-size: medium valign='bottom'>Revisions</td></tr></tr> </table>" +
" <ul><%Rev%></ul>" +
"<br />" +
" <br />" +
" <br />" +
" <br />" +
" <br />" +
" <br />" +
" <p style='font-size: 9px; font-weight: normal; text-transform: uppercase; color: #000000'>FormID: MA-Ext</p>"
mm.IsBodyHtml = True
mm.Body = mm.Body.Replace("<%OPK%>", dr("OrdersProductKey"))
mm.Body = mm.Body.Replace("<%Name%>", dr("VendorFirstName"))
mm.Body = mm.Body.Replace("<%Address%>", dr("Address"))
mm.Body = mm.Body.Replace("<%DueTime%>", dr("DueTime"))
mm.Body = mm.Body.Replace("<%DueDate%>", dr("DueDate"))
mm.Body = mm.Body.Replace("<%Report%>", dr("ReportTypeName"))
mm.Body = mm.Body.Replace("<%City%>", dr("City"))
mm.Body = mm.Body.Replace("<%State%>", dr("StateAbbr"))
mm.Body = mm.Body.Replace("<%Zip%>", dr("Zip"))
mm.Body = mm.Body.Replace("<%Rev%>", dr("Rev"))
dr.Close()
'(3) Create the SmtpClient object
Dim smtp As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient()
'(4) Send the MailMessage (will use the Web.config settings)
smtp.Send(mm)
End If
Finally
cnn.Close()
End Try
End Sub
End Class
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER