Link to home
Start Free TrialLog in
Avatar of rcowen00
rcowen00Flag for United States of America

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.  

User generated image


 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

Open in new window


User generated image
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rcowen00

ASKER

I have <li></li> in the stored procedure, I thought I could do that.  When I try to put a for each in the code behind it breaks the darn thing?  Can you give me an example?  Thank you.