none
Get DataGridView Selected Rows to HTML table and send by email RRS feed

  • Question

  • Good morning to all,

    I've been trying to use following code (posted in another question) to get the data inside a datagridview into HTML table and send it by email:

    Imports Outlook = Microsoft.Office.Interop.Outlook
    Imports System.Text
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            ' Create an Outlook application.
            Dim oApp As Outlook._Application
            oApp = New Outlook.Application()
    
            ' Create a new MailItem.
            Dim oMsg As Outlook._MailItem
            oMsg = oApp.CreateItem(Outlook.OlItemType.olMailItem)
            oMsg.Subject = "Send DGV Using OOM in Visual Basic .NET"
            oMsg.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
    
            Dim strB As StringBuilder = New StringBuilder()
            'create html & table
            strB.AppendLine("<html><body><center><" &
            "table border='1' cellpadding='0' cellspacing='0'>")
            strB.AppendLine("<tr>")
            'cteate table header
            For value As Integer = 0 To DataGridView1.Columns.Count - 1
                strB.AppendLine("<td align='center' valign='middle'>" & DataGridView1.Columns(value).HeaderText + "</td>")
            Next
            'create table body
            strB.AppendLine("<tr>")
            For value As Integer = 0 To DataGridView1.Rows.Count - 2
                strB.AppendLine("<tr>")
                For Each dgvc As DataGridViewCell In DataGridView1.Rows(value).Cells
                    strB.AppendLine("<td align='center' valign='middle'>" & dgvc.Value.ToString() & "</td>")
                Next
                strB.AppendLine("</tr>")
            Next
            'table footer & end of html file
            strB.AppendLine("</table></center></body></html>")
    
            oMsg.HTMLBody = strB.ToString()
    
            ' TODO: Replace with a valid e-mail address.
            oMsg.To = "name@address"
    
            '' Add an attachment
            '' TODO: Replace with a valid attachment path.
            'Dim sSource As String = "D:\Hello.txt"
            '' TODO: Replace with attachment name
            'Dim sDisplayName As String = "Hello.txt"
    
            'Dim sBodyLen As String = oMsg.Body.Length
            'Dim oAttachs As Outlook.Attachments = oMsg.Attachments
            'Dim oAttach As Outlook.Attachment
            'oAttach = oAttachs.Add(sSource, , sBodyLen + 1, sDisplayName)
    
            ' Send
            oMsg.Send()
    
            ' Clean up
            oApp = Nothing
            oMsg = Nothing
            'oAttach = Nothing
            'oAttachs = Nothing
        End Sub
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add("c1")
            dt.Columns.Add("c2")
            dt.Rows.Add("cell1", "cell2")
            dt.Rows.Add("cell3", "cell4")
            dt.Rows.Add("cell3", "cell4")
            dt.Rows.Add("cell3", "cell4")
            dt.Rows.Add("cell3", "cell4")
            dt.Rows.Add("cell3", "cell4")
            DataGridView1.DataSource = dt
        End Sub
    End Class

    It works.

    But I want to send just selected rows and specific column values, not the entire DataGridView.

    I have this code below working, I select one or more rows and the values are sent all in one line, but I want to send it with the HTML table.

    Private Sub pedirautorizacion()
            On Error Resume Next
            Dim message As New StringBuilder()
            If ProyectoCombo.Text = "A5C002" Then
                For Each row As DataGridViewRow In A5C002DGV.SelectedRows
                    Me.Cursor = Cursors.WaitCursor
                    message.Append("<b>Proyecto:</b> " & row.Cells(7).Value.ToString() & "  --  " & "<b>Proveedor:</b> " & row.Cells(2).Value.ToString() & "  --  " & "<b>Nro. Factura:</b> " & row.Cells(4).Value.ToString() & "  --  " & "<b>Concepto:</b> " & row.Cells(3).Value.ToString() & "  --  " & "<b>Monto:</b> " & row.Cells(5).Value.ToString() & "  --  " & "<b>Moneda:</b> " & row.Cells(6).Value.ToString() & "<br/>" & Environment.NewLine) '<br/>
                Next
    End If
            Call SendEmail(message.ToString())

    Anyone can help me with this please.

    Thanks!

    Wednesday, November 1, 2017 1:55 PM

Answers

  • Yes it works dbasnett, but instead to send all the columns values, I just want to send specific ones.

    For example, I have 10 columns, and with your code all the 10 columns values from selected rows are sent, but I just want to send the 1,4,6,7 columns values. That's why I said that maybe can be a combination of my code and yours, or specifies in yours the columns to be sent.

    This is my working code, it sends the specific values that I need, but not in the HTML table format

    Private Sub pedirautorizacion()
            On Error Resume Next
            Dim message As New StringBuilder()
            If ProyectoCombo.Text = "A5C002" Then
                For Each row As DataGridViewRow In A5C002DGV.SelectedRows
                    Me.Cursor = Cursors.WaitCursor
                    message.Append("<b>Proyecto:</b> " & row.Cells(7).Value.ToString() & "  --  " & "<b>Proveedor:</b> " & row.Cells(2).Value.ToString() & "  --  " & "<b>Nro. Factura:</b> " & row.Cells(4).Value.ToString() & "  --  " & "<b>Concepto:</b> " & row.Cells(3).Value.ToString() & "  --  " & "<b>Monto:</b> " & row.Cells(5).Value.ToString() & "  --  " & "<b>Moneda:</b> " & row.Cells(6).Value.ToString() & "<br/>" & Environment.NewLine) '<br/>
                Next
    End If

    Thanks again!

    one more time

            Dim colsToSend As New List(Of Integer) From {0, 1, 3, 4} 'which columns to send
    
            Dim body As XElement
            If DataGridView1.SelectedRows.Count > 0 Then 'are rows selected
                body = <body>
                           <table style="border-style:solid;border-width:1px;border-collapse:collapse;">
                               <tr>
                                   <%= From c In DataGridView1.Columns.Cast(Of DataGridViewColumn)()
                                       Where colsToSend.Contains(c.Index)
                                       Select <th style="border-style:solid;border-width:1px;border-collapse:collapse;text-align:center;vertical-align:middle;width:100px;"><%= c.HeaderText %></th>
                                   %>
                               </tr>
                               <%= From r In DataGridView1.SelectedRows.Cast(Of DataGridViewRow)() Order By r.Index
                                   Where Not r.IsNewRow
                                   Select <tr>
                                              <%= From c In r.Cells.Cast(Of DataGridViewCell)()
                                                  Where colsToSend.Contains(c.ColumnIndex)
                                                  Select <td style="border-style:solid;border-width:1px;border-collapse:collapse;text-align:left;vertical-align:middle;"><%= c.Value %></td>
                                              %>
                                          </tr>
                               %>
                           </table>
                       </body>
            End If
    
            Stop 'look at body
            If body IsNot Nothing Then
                Dim oApp As New Outlook.Application
    
                Dim oMsg As Outlook.MailItem = CType(oApp.CreateItem(Outlook.OlItemType.olMailItem), Outlook.MailItem)
                oMsg.Subject = "Send DGV Using OOM in Visual Basic .NET"
                oMsg.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
                oMsg.To = "" 'need To:
    
                'get rid of existing body
                Dim exBod As String = oMsg.HTMLBody
                Dim exBods As Integer = exBod.IndexOf("<BODY>")
                Dim exBode As Integer = exBod.IndexOf("</BODY>") - exBods + 7
                exBod = exBod.Remove(exBods, exBode)
                'add new body
                exBod = exBod.Insert(exBods, body.ToString)
                oMsg.HTMLBody = exBod
                ' oMsg.Send()
            End If
    



    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it" - MSDN User JohnWein




    • Marked as answer by joyi6388 Wednesday, November 1, 2017 5:46 PM
    • Edited by dbasnett Wednesday, November 1, 2017 6:50 PM
    Wednesday, November 1, 2017 4:23 PM

All replies

  • Try the following. In the actual building of the TR/TD elements in this example I have two columns, so you would do as many columns as you have. You can use CSS e.g. <b> or <strong> etc.

    Note the OrderBy, I did this so the ordering is not screwed up, try without the OrderBy to see the difference.

    Public Class SelectedItem
        Public Property Row As DataGridViewRow
    End Class
    

    Code

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim SelectedRowData As List(Of SelectedItem) = (DataGridView1.SelectedRows.Cast(Of DataGridViewRow)() _
            .Select(Function(row, index) New SelectedItem With {.Row = row}) _
            .OrderBy(Function(item) item.Row.Index)).ToList
    
        Dim result =
            <body>
                <table>
                    <%= From item In SelectedRowData
                        Select <tr><td><%= item.Row.Cells(0).Value %></td><td><%= item.Row.Cells(1).Value %></td></tr> %>
                </table>
            </body>
        Console.WriteLine(result.ToString)
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 1, 2017 2:19 PM
    Moderator
  • Like Karens I suppose

            Dim html As XElement
            html = <html>
                       <body>
                           <table border="1" cellpadding="0" cellspacing="0">
                               <tr>
                                   <%= From c In DataGridView1.Columns
                                       Select <td align='center' valign='middle'><%= DirectCast(c, DataGridViewColumn).HeaderText %></td>
                                   %>
                               </tr>
                               <%= From r In DataGridView1.Rows
                                   Where Not DirectCast(r, DataGridViewRow).IsNewRow
                                   Select <tr>
                                              <%= From c In DirectCast(r, DataGridViewRow).Cells
                                                  Select <td align='left' valign='middle'><%= DirectCast(c, DataGridViewCell).Value %></td>
                                              %>
                                          </tr>
                               %>
                           </table>
                       </body>
                   </html>
    
            Stop 'look at html
            Dim oMsg As Outlook.MailItem
            oMsg.HTMLBody = html.ToString

    From my sample data html looks like
    <html>
      <body>
        <table border="1" cellpadding="0" cellspacing="0">
          <tr>
            <td align="center" valign="middle">c1</td>
            <td align="center" valign="middle">c2</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell1</td>
            <td align="left" valign="middle">cell2</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell3</td>
            <td align="left" valign="middle">cell4</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell5</td>
            <td align="left" valign="middle">cell4</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell7</td>
            <td align="left" valign="middle">cell4</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell9</td>
            <td align="left" valign="middle">cell4</td>
          </tr>
          <tr>
            <td align="left" valign="middle">cell11</td>
            <td align="left" valign="middle">cell4</td>
          </tr>
        </table>
      </body>
    </html>


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it" - MSDN User JohnWein


    • Edited by dbasnett Wednesday, November 1, 2017 2:54 PM
    Wednesday, November 1, 2017 2:49 PM
  • Thanks Karen for your quick reply.

    I tried the code you provide and does "the same" as my code, or I am missing something? Maybe I made a mistake saying "the values are sent all in one line". Actually, the values are sent in List format, and it works perfect, but I've been asked to send the values in a HTLM table. That's why I tried the first posted code.

    Would be like your code, or mine, into a table.

    Wednesday, November 1, 2017 2:57 PM
  • Thank you dbasnett,

    But I need to send just specific columns values from selected rows.

    I think it can be Karen's and yours solution together. Or mine actual code and your code together but I don't know how to combine them.

    Thanks!

    Wednesday, November 1, 2017 3:17 PM
  • Thank you dbasnett,

    But I need to send just specific columns values from selected rows.

    I think it can be Karen's and yours solution together. Or mine actual code and your code together but I don't know how to combine them.

    Thanks!

    In mine change this

     <%= From r In DataGridView1.Rows

    to

     <%= From r In DataGridView1.SelectedRows

            Dim html As XElement = <html></html>
    
            If DataGridView1.SelectedRows.Count > 0 Then 'are rows selected
                Dim body As XElement
                body = <body>
                           <table border="1" cellpadding="0" cellspacing="0">
                               <tr>
                                   <%= From c In DataGridView1.Columns
                                           Select <td align='center' valign='middle'><%= DirectCast(c, DataGridViewColumn).HeaderText %></td>
                                   %>
                               </tr>
                               <%= From r In DataGridView1.SelectedRows
                                       Where Not DirectCast(r, DataGridViewRow).IsNewRow
                                       Select <tr>
                                                  <%= From c In DirectCast(r, DataGridViewRow).Cells
                                                      Select <td align='left' valign='middle'><%= DirectCast(c, DataGridViewCell).Value %></td>
                                                  %>
                                              </tr>
                               %>
                           </table>
                       </body>
                html.Add(body)
            End If
    
            Stop 'look at html
    


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it" - MSDN User JohnWein


    • Edited by dbasnett Wednesday, November 1, 2017 3:30 PM
    Wednesday, November 1, 2017 3:24 PM
  • Thanks Karen for your quick reply.

    I tried the code you provide and does "the same" as my code, or I am missing something? Maybe I made a mistake saying "the values are sent all in one line". Actually, the values are sent in List format, and it works perfect, but I've been asked to send the values in a HTLM table. That's why I tried the first posted code.

    Would be like your code, or mine, into a table.

    I'll be honest, never sent email via automation as you are but instead use the following pattern.

    http://www.systemnetmail.com/faq/3.1.2.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 1, 2017 3:42 PM
    Moderator
  • Yes it works dbasnett, but instead to send all the columns values, I just want to send specific ones.

    For example, I have 10 columns, and with your code all the 10 columns values from selected rows are sent, but I just want to send the 1,4,6,7 columns values. That's why I said that maybe can be a combination of my code and yours, or specifies in yours the columns to be sent.

    This is my working code, it sends the specific values that I need, but not in the HTML table format

    Private Sub pedirautorizacion()
            On Error Resume Next
            Dim message As New StringBuilder()
            If ProyectoCombo.Text = "A5C002" Then
                For Each row As DataGridViewRow In A5C002DGV.SelectedRows
                    Me.Cursor = Cursors.WaitCursor
                    message.Append("<b>Proyecto:</b> " & row.Cells(7).Value.ToString() & "  --  " & "<b>Proveedor:</b> " & row.Cells(2).Value.ToString() & "  --  " & "<b>Nro. Factura:</b> " & row.Cells(4).Value.ToString() & "  --  " & "<b>Concepto:</b> " & row.Cells(3).Value.ToString() & "  --  " & "<b>Monto:</b> " & row.Cells(5).Value.ToString() & "  --  " & "<b>Moneda:</b> " & row.Cells(6).Value.ToString() & "<br/>" & Environment.NewLine) '<br/>
                Next
    End If

    Thanks again!

    Wednesday, November 1, 2017 4:04 PM
  • Yes it works dbasnett, but instead to send all the columns values, I just want to send specific ones.

    For example, I have 10 columns, and with your code all the 10 columns values from selected rows are sent, but I just want to send the 1,4,6,7 columns values. That's why I said that maybe can be a combination of my code and yours, or specifies in yours the columns to be sent.

    This is my working code, it sends the specific values that I need, but not in the HTML table format

    Private Sub pedirautorizacion()
            On Error Resume Next
            Dim message As New StringBuilder()
            If ProyectoCombo.Text = "A5C002" Then
                For Each row As DataGridViewRow In A5C002DGV.SelectedRows
                    Me.Cursor = Cursors.WaitCursor
                    message.Append("<b>Proyecto:</b> " & row.Cells(7).Value.ToString() & "  --  " & "<b>Proveedor:</b> " & row.Cells(2).Value.ToString() & "  --  " & "<b>Nro. Factura:</b> " & row.Cells(4).Value.ToString() & "  --  " & "<b>Concepto:</b> " & row.Cells(3).Value.ToString() & "  --  " & "<b>Monto:</b> " & row.Cells(5).Value.ToString() & "  --  " & "<b>Moneda:</b> " & row.Cells(6).Value.ToString() & "<br/>" & Environment.NewLine) '<br/>
                Next
    End If

    Thanks again!

    one more time

            Dim colsToSend As New List(Of Integer) From {0, 1, 3, 4} 'which columns to send
    
            Dim body As XElement
            If DataGridView1.SelectedRows.Count > 0 Then 'are rows selected
                body = <body>
                           <table style="border-style:solid;border-width:1px;border-collapse:collapse;">
                               <tr>
                                   <%= From c In DataGridView1.Columns.Cast(Of DataGridViewColumn)()
                                       Where colsToSend.Contains(c.Index)
                                       Select <th style="border-style:solid;border-width:1px;border-collapse:collapse;text-align:center;vertical-align:middle;width:100px;"><%= c.HeaderText %></th>
                                   %>
                               </tr>
                               <%= From r In DataGridView1.SelectedRows.Cast(Of DataGridViewRow)() Order By r.Index
                                   Where Not r.IsNewRow
                                   Select <tr>
                                              <%= From c In r.Cells.Cast(Of DataGridViewCell)()
                                                  Where colsToSend.Contains(c.ColumnIndex)
                                                  Select <td style="border-style:solid;border-width:1px;border-collapse:collapse;text-align:left;vertical-align:middle;"><%= c.Value %></td>
                                              %>
                                          </tr>
                               %>
                           </table>
                       </body>
            End If
    
            Stop 'look at body
            If body IsNot Nothing Then
                Dim oApp As New Outlook.Application
    
                Dim oMsg As Outlook.MailItem = CType(oApp.CreateItem(Outlook.OlItemType.olMailItem), Outlook.MailItem)
                oMsg.Subject = "Send DGV Using OOM in Visual Basic .NET"
                oMsg.BodyFormat = Outlook.OlBodyFormat.olFormatHTML
                oMsg.To = "" 'need To:
    
                'get rid of existing body
                Dim exBod As String = oMsg.HTMLBody
                Dim exBods As Integer = exBod.IndexOf("<BODY>")
                Dim exBode As Integer = exBod.IndexOf("</BODY>") - exBods + 7
                exBod = exBod.Remove(exBods, exBode)
                'add new body
                exBod = exBod.Insert(exBods, body.ToString)
                oMsg.HTMLBody = exBod
                ' oMsg.Send()
            End If
    



    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it" - MSDN User JohnWein




    • Marked as answer by joyi6388 Wednesday, November 1, 2017 5:46 PM
    • Edited by dbasnett Wednesday, November 1, 2017 6:50 PM
    Wednesday, November 1, 2017 4:23 PM
  • Wow! It works, every day I learn something new here in MSDN forum.

    Thank you so much dbasnett for your patience.

    Wednesday, November 1, 2017 5:46 PM
  • Wow! It works, every day I learn something new here in MSDN forum.

    Thank you so much dbasnett for your patience.


    You are welcome.  We both owe thanks to Karen who taught me.

    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it" - MSDN User JohnWein

    Wednesday, November 1, 2017 6:23 PM
  • Hello dbasnett,

    Sorry to bother you again, can you tell me please how I add a background color to a specific column? And how can I format Dates values to "dd/MM/yyyy" because I have 3 columns that are dates but in the html table inside de new email they appear with the time also.

    Thanks!

    Wednesday, November 15, 2017 6:57 PM