[RESOLVED] Query Results to e-Mail

[wftech]

I know this has probibly been asked before but I have had no luck finding an answer or example anywhere for what I want to do. Which Is.....

I have a query that returns Lname, Fname, and EMail

I want to send 1 email (that Is already set up and working) that contains the results of the query as part of the body that includes an additional message;
"The persons listed below.........." Need to know how to get them "listed below".

then I want to send a seprate e-mail to each email address the query returns, sepratly not in a group but the same message.

any help, examples or links would be appreciated.
Thanks

[GaryMazzone]

Take the result (is if in a datareader or DataTable) and go though each row.

Append the fields of each row into a string var. Start the string var with:

The persons listed below.........
then Set that result to the body text of the eamil

[wftech]

Gary,

I know this isn't right, but am I going in the right direction?

Dim strBuilder As New System.Text.StringBuilder
                strBuilder.Append("The following Persons....."(table.Rows))

[GaryMazzone]

You are starting.

Is the result set a DataTable or a DataReader?

You cycle though each row. Adding each field to the string.

[wftech]

Datatable "table" How?

[GaryMazzone]

What is the code that brings back the result set?

da.Fill?

When you have a datatable you have a method called RowCount().

You do a for loop and read each line of the resulting DataTable

[wftech]

using an OledbDataAdapter to fill dataTable

Trying something like this will it work?

 Dim strBuilder As New System.Text.StringBuilder
                For Each row As DataRow In table.Rows
                    strBuilder = New System.Text.StringBuilder

                    For Each col As DataColumn In table.Columns
                        ' Output the value of each column's data.
                        strBuilder.Append("The following Persons....."(row(col).ToString() & ", "))
                    Next
                  
                Next

Then just but the string value in the eMail body?

[GaryMazzone]

Move this:

strBuilder = New System.Text.StringBuilder

outside the loop
at the end of the the inside loop add

strBuilder.System.Enviorment.NewLine

[wftech]

strBuilder.System.Enviorment.NewLine

is not a member of System.Text.StringBuilder

[GaryMazzone]

Sorry should be strBuilder.Append (System.Evniorment.NewLine)

That will place each row on its own line.

[wftech]

Ok Great now how do I get it to the body of my email?

I know I can't just say body = strBuilder

[GaryMazzone]

Have you done email yet in an application?

You will need to set this all up to be able to use it.

You will need to say what the SMTP server name is, If there are passwords and usernames involved. What the email program is.

[wftech]

Have it all set up and it works using Outlook

[wftech]

just nee to know how to get what I just did in the body of the message

[GaryMazzone]

Then you should just be able to assign the resulting string to the body of the mail message.

[wftech]

Just tried to run it got an "InvalidCastException" Here

 strBuilder.Append("The following Persons....."(row(col).ToString() & ", "))

Conversion from String "GORDEN" to type integer is not valid

[GaryMazzone]

strBuilder.Append("The following Persons....."
strBuilder.Append(System.Enviorment.NewLine)
Dim iCols as Integer = table.Coumns.Count() -1
                For iRow as Integer = 0 to table.Rows.RowCount()-1
                    For Each iCol as Integer = 0 to iCols
                        strBuilder.Append(table.Rows(iRow).Item(iCol).ToString() & ", ")
                    Next
                  strBuilder.Append(System.Enviorment.NewLine)
                Next

[wftech]

I doesn't like the = in For Each iCol as Integer = 0 to iCols
Syntax error

[GaryMazzone]

For iCols as Integer = 0

[wftech]

That seems to be working
Thanks