Emailing with excel

[bigzos]

Hey guys,I have an excel sheet with one of the columns as name .There are many rows with same name and I wish to mail all the rows with same person name to that person(given there is a column specifying email address).I will be thankful for your suggestions.
The following is my 'sending email' code:

Imports System.Net.Mail

Public Class SendEmail

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim message As New MailMessage
        Dim smtp As New SmtpClient
        message.To.Add("EmailAddress")
        message.From = New MailAddress("[e-mail]")
        message.Subject = "Hello World"
        message.Body = "Hello world 2012"
        smtp.Host = "smtp.gmail.com"
        smtp.Port = "587"
        smtp.EnableSsl = True
        smtp.Credentials = New Net.NetworkCredential("[e-mail]", "example123")
        smtp.Send(message)


    End Sub

[Begginnerdev]

What you will need to do is:

1) Loop through the workbook, comparing and storing all values that reference that person.
2) Loop through those values, adding them to the message (body?)**

It may be easier to write the values to a text file and attach the text file to the message.

[bigzos]

Ok,so above I posted the code for sending mail and following is the code for retrieving values from excel file,,Now how do i add these values i.e. the query result to the message body or attach them with the mail( which may be better ,if possible)

            Dim MyCommand As OleDbCommand
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            MyConnection = New System.Data.OleDb.OleDbConnection( _
            "provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source=  C:\Users\Space Era\Documents\Book1.xlsx  ; " & _
            "Extended Properties=Excel 8.0")
            MyConnection.Open()

            MyCommand = New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]  ", MyConnection)

Also,there is one problem with above code that it only shows the results if the excel file is open explicitly ,otherwise shows error"external table format not supported",,any idea on how to fix this?

[Begginnerdev]

Use the command to fill a data adapter, and port that into a data table.

Then cycle through the data table and pass those values in.

Example:

    Dim da As New SqlDataAdapter
    Dim ds As New Data.DataSet
    Dim dt As New Data.DataTable

    da.SelectCommand = MyCommand
    da.Fill(ds, "myitems")
    dt = ds.Tables("myitems")

    For i = 0 To dt.Rows.Count - 1
        message.Body &= vbCrLf & dt.Rows(i).ToString
    Next

[bigzos]

Well,thanks for your reply,,I would also like to know how can i save the result of query to an excel file and then add it as an attachment

[bigzos]

I figured it out,,anyways thanx!