Email body with dynamic bullet points

Posted on 2012-08-29
Last Modified: 2012-09-01
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 = ""
                '(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
Question by:rcowen00
    LVL 83

    Accepted 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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now