How to email multiple queries to multiple users

Question

I have a program that auto launches with Windows via scheduler. What it does is runs a query and then emails the results of the query. This all works. What i'd like to do is take the program to the next level. We have 10 locations. The location DM should receive this report daily (only receiving their store). So basically what I'd like to do is repeat the code in the form of a different tableadapter and email that information. My C# code is:

Imports System.Net.Mail
Imports System.Linq

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Try
            Dim SmtpServer As New SmtpClient()
            Dim mail As New MailMessage()
            Me.Paid_Out_TbTableAdapter.Fill(Me.DataSet.Paid_Out_Tb)
            Dim payouts = _
            <html>
                <body>
                    <table border="1">
                        <tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
                        <%= From paidOut In Me.DataSet.Paid_Out_Tb.AsEnumerable _
                            Select <tr><td><%= paidOut.Store_Id %></td>
                                       <td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
                                       </td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
                                       <td><%= paidOut.Update_UserName %></td>
                                       <td><%= paidOut.Paid_Out_Comment %></td></tr> %>
                    </table>
                </body>

            </html>

            If (Me.DataSet.Paid_Out_Tb.Count = 0) Then 'This cheks to see if the dataset is Null.  We do not want to email if the set is Null

                Me.Close()

            Else

                SmtpServer.Credentials = New  _
                Net.NetworkCredential("*****", "****") 'Assign the network credentials
                SmtpServer.Port = 25 'Assign the SMTP Port
                SmtpServer.Host = "10.0.*.*" 'Assign the Server IP
                mail = New MailMessage() 'Starts a mail message
                mail.From = New MailAddress("***@***.com") 'Sets the "FROM" address
                mail.To.Add("****@****.com") 'Sets the "To" address
                'mail.CC.Add("****@****.com") 'set this if you would like to CC
                mail.Subject = "Paid Out Report for 1929"
                mail.IsBodyHtml = True
                mail.Body = payouts.ToString()
                SmtpServer.Send(mail)
                'MsgBox("mail send")
            End If

        Catch ex As Exception

            MsgBox(ex.ToString)


        End Try

My Query is:

SELECT        Store_Id, Paid_Out_Amount, Paid_Out_Comment, Paid_Out_Datetime, Update_UserName, Till_Number
FROM            Paid_Out_Tb
WHERE        (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Amount > 20) OR
                         (Store_Id = 1929) AND (Paid_Out_Datetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0)) AND (Paid_Out_Datetime < DATEADD(day, DATEDIFF(day, 0, 
                         GETDATE()), 0)) AND (Paid_Out_Comment LIKE N'%' + 'Filter' + '%')

Again, this all works. But my second query will be exactly the same except I will substitue "112" for the store_ID. I will then need to email that query result to a DIFFERENT address than the 1929 id... Any suggestions on how best to accomplish this?

That looks like VB.NET code to me. Did you perhaps paste the wrong thing? — Bryan Crosby, Jul 30 '12 at 19:32
Sounds like you're trying to loop through the results of your query and email once to each related user. Is that correct? Also, are you sure that's C#? — Nick Vaccaro, Jul 30 '12 at 19:39
You are both correct, I'm sorry I forgot I switch to VB for this one... You are correct. @Norla, more or less yes. — Shmewnix, Jul 30 '12 at 19:42
Check out this question: http://stackoverflow.com/questions/5991877/vb-net-execute-a-sql-stored-procedure-and-process-the-results, specifically Brian Webster's answer. — Nick Vaccaro, Jul 30 '12 at 19:45
How would I break it apart if I ran just one query? The problem is, i'm sending the info in XML form as en email based on the Store ID... If the storeID does not have any info (the check for 0 above) then it does not email... How would I have it check for a store number and if it doesn't have results, not email and go to the next store id? — Shmewnix, Jul 30 '12 at 20:30
You'll probably need a Database that contains the store ID's and E-mail address of the person receiving the email. Then you basically just use a reader to pull back all of the rows with the ID's and e-mails, and a For Loop to dynamically go through and insert the appropriate ID in the query. If the query has results send the email to the appropriate person. If not, skip it and move on to the next row of Store_ID, Email. Hope that makes sense. — Tony318, Jul 30 '12 at 21:29
how hard would it be to use a config.xml file for this? I've never done it... — Shmewnix, Jul 31 '12 at 03:26

score 0 · Accepted Answer · answered Jul 31 '12 at 16:31

Imports System.Net.Mail
Imports System.Linq

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet.Paid_Out_Tb' table. You can move, or remove it, as needed.

        Try
            Dim SmtpServer As New SmtpClient()
            Dim mail As New MailMessage()
            Me.Paid_Out_TbTableAdapter.Fill(Me.DataSet.Paid_Out_Tb)
            Me.DataTable1TableAdapter.Fill(Me.DataSet.DataTable1)
            Dim payouts = _
            <html>
                <body>
                    <table border="1">
                        <tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
                        <%= From paidOut In Me.DataSet.Paid_Out_Tb.AsEnumerable _
                            Select <tr><td><%= paidOut.Store_Id %></td>
                                       <td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
                                       </td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
                                       <td><%= paidOut.Update_UserName %></td>
                                       <td><%= paidOut.Paid_Out_Comment %></td></tr> %>
                    </table>
                </body>

            </html>
            Dim Payouts453 = _
            <html>
                <body>
                    <table border="1">
                        <tr><th>Store #</th><th>Date</th><th>Amount</th><th>User</th><th>Comment</th></tr>
                        <%= From paidOut In Me.DataSet.DataTable1.AsEnumerable _
                            Select <tr><td><%= paidOut.Store_Id %></td>
                                       <td><%= Convert.ToDateTime(paidOut.Paid_Out_Datetime).ToString("M/d/yy") %>
                                       </td><td><%= "$" & paidOut.Paid_Out_Amount.ToString("0.00") %></td>
                                       <td><%= paidOut.Update_UserName %></td>
                                       <td><%= paidOut.Paid_Out_Comment %></td></tr> %>
                    </table>
                </body>

            </html>
            If (Me.DataSet.Paid_Out_Tb.Count = 0) Then 'This cheks to see if the dataset is Null.  We do not want to email if the set is Null

                Me.Close()


            Else

                SmtpServer.Credentials = New  _
                Net.NetworkCredential("****", "****") 'Assign the network credentials
                SmtpServer.Port = 25 'Assign the SMTP Port
                SmtpServer.Host = "10.0.*.**" 'Assign the Server IP
                mail = New MailMessage() 'Starts a mail message
                mail.From = New MailAddress("*@**.com") 'Sets the "FROM" address
                mail.To.Add("**@**.com") 'Sets the "To" address
                'mail.CC.Add("**@**.com") 'set this if you would like to CC
                mail.Subject = "Paid Out Report for 1929"
                mail.IsBodyHtml = True
                mail.Body = payouts.ToString() & Payouts453.ToString() 'this is to add another chart  You would use a seperate dataset obviously
                SmtpServer.Send(mail)
                'MsgBox("mail send")
            End If

        Catch ex As Exception

            MsgBox(ex.ToString)


        End Try






        Me.Close() 'Closes the program when it's finished.


    End Sub

The easiest way to accomplish this, is to add another tableadapter and run a seperate query... It's probably not the most efficient, but for the amount of data being transmitted... it works..

How to email multiple queries to multiple users

1 Answers1