[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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.  

SP Results

 Private Sub RevisionEmail()
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
            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")
            Dim dt As New System.Data.DataTable

            Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader


            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"))

                '(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)
            End If
        End Try
    End Sub
End Class

Open in new window

Email Example
1 Solution
You would need to break it on some character such as line break and then loop through that list and add the bullet points.
rcowen00Author Commented:
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now