Need to send emails to multiple users

Question

I have developed a simple alert system which will read a table in SQL and send emails accordingly. There are Usernames, Email Addresses, Messages to be sent in that SQL table. The system will read the table every 20 minutes and will send emails to the users according to their respective email address. But at the moment the system sends emails to one user only. I want to further develop this system to send emails to multiple users when one set has finished. I do not have an idea how to do this. Is there anyone who can help me with this. Code snippet would be more helpful to understand.

Below is the SQL table template

Name  | Email              | Factory| AlertTime| Description
User1 | user1@mydomain.com | FAC1   | 01:50:00 | UserMessage1
User1 | user1@mydomain.com | FAC2   | 01:50:00 | UserMessage2
User1 | user1@mydomain.com | FAC3   | 03:00:00 | UserMessage3

User2 | user2@mydomain.com | FAC1   | 01:20:00 | UserMessage1
User2 | user2@mydomain.com | FAC2   | 01:50:00 | UserMessage2
User2 | user2@mydomain.com | FAC3   | 03:00:00 | UserMessage3

User3 | user3@mydomain.com | FAC1   |  01:20:00 | UserMessage1
User3 | user3@mydomain.com | FAC2   |  01:50:00 | UserMessage2
User3 | user3@mydomain.com | FAC3   |  03:00:00 | UserMessage3

Below is my C# cord

    using System;
    using System.Timers;
    using System.Windows.Forms;
    using System.Net.Mail;
    using System.Data;
    using System.Speech.Synthesis;
    using System.Collections.Generic;

    namespace Alerts
    {
        public partial class frmAlerts : Form
        {
            SpeechSynthesizer speechSynthesizerObj;
            Common ComMsg = new Common();
            DataSet DatMsg = new DataSet();
            AlertException error = new AlertException();
            List<string> AlertList = new List<string>();
            string ToName;
            string ToEmail;
            string TotMsg;

            public frmAlerts()
            {
                InitializeComponent();
                this.WindowState = FormWindowState.Minimized;
            }
            private void frmAlerts_Load(object sender, EventArgs e)
            {
                try
                {
                    System.Timers.Timer timer = new System.Timers.Timer(20 * 60 * 1000);
                    timer.Elapsed += new ElapsedEventHandler(SendAlerts);
                    timer.Start();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error in application Load: " + ex.Message);
                }

            }
            public void SendAlerts(object source, ElapsedEventArgs e)
            {
                try
                {
                    DatMsg = ComMsg.ReturnDataSet("SELECT RptAlertRecipient.Name,  RptAlertRecipient.Email,  RptAlerts.Factory,  RptAlerts.AlertTime,  RptAlerts.Description " +
                                             "FROM RptAlerts " +
                                             "INNER JOIN RptAlertTypes ON  RptAlerts.AlertTypeID = RptAlertTypes.ID " +
                                             "INNER JOIN RptAlertType_RecipientMapping ON  RptAlertTypes.ID = RptAlertType_RecipientMapping.AlertTypeID " +
                                             "INNER JOIN RptAlertRecipient ON  RptAlertType_RecipientMapping.AlertRecipientID = RptAlertRecipient.ID " +
                                             "WHERE RptAlertRecipient.Name= 'User1' " +
                                             "ORDER BY RptAlertRecipient.Name ASC");
                    for (int j = 0; j < DatMsg.Tables[0].Rows.Count; j++)
                    {
                        ToEmail = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(1).ToString();
                        ToName = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(0).ToString();
                        AlertList.Add(DatMsg.Tables[0].Rows[j].ItemArray.GetValue(4).ToString() + "<br/>");
                        TotMsg = (j + 1).ToString();
                    }

                    string to = ToEmail;
                    string from = "helpdesk@mydomain.com";
                    string subject = "Alert In Time : You Have "+TotMsg+ " Alerts";
                    string msgBody = "Dear " + ToName + ",<br/><br/>";
                    msgBody += "<b>You Have " + TotMsg + " Alerts</b><br/><br/>";
                    msgBody += string.Join("<br/>", AlertList);
                    msgBody += "<br/><br/>Regards<br/>Sent by Alert Service<br/>(Please do not reply to this email.)";
                    MailMessage msg = new MailMessage(from, to, subject, msgBody);
                    msg.IsBodyHtml = true;
                    SmtpClient clnt = new SmtpClient("outlook.mydomain.local", 25);
                    clnt.EnableSsl = false;
                    clnt.Credentials = new System.Net.NetworkCredential("helpdesk@mydomain.com", "password");
                    clnt.Send(msg);
                }
                catch (Exception ex)
                {
                    error.ExceptionMessage = ex.ToString();//gets the exception message to a separate class
                    speechSynthesizerObj = new SpeechSynthesizer();
                    speechSynthesizerObj.SpeakAsync(ex.Message);//Speaks the Error
                }
            }

        }
    }

Answer 1

I found a solution for that. I did some changes to the code and now it is working the way I wanted it to happen. Below is my code. Thank you everyone for your help.

    using System;
    using System.Data;
    using System.Timers;
    using System.Net.Mail;
    using System.Windows.Forms;
    using System.Speech.Synthesis;
    using System.Collections.Generic;

    namespace Alerts
    {
        public partial class frmAlerts : Form
        {
            SpeechSynthesizer speechSynthesizerObj;
            Common ComMsg = new Common();
            DataSet DatMsg = new DataSet();
            DataSet DatNames = new DataSet();
            AlertException error = new AlertException();
            List<string> AlertList = new List<string>();
            string ToName;
            string ToEmail;
            string TotMsg;
            string _Name;
            public frmAlerts()
            {
                InitializeComponent();
                this.WindowState = FormWindowState.Minimized;
            }
            private void frmAlerts_Load(object sender, EventArgs e)
            {
                try
                {
                    System.Timers.Timer timer = new System.Timers.Timer(20 * 60 * 1000);
                    timer.Elapsed += new ElapsedEventHandler(SendAlerts);
                    timer.Start();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error in application Load: " + ex.Message);
                    SendtoAdmin();
                }
            }
            public void SendAlerts(object source, ElapsedEventArgs e)
            {
                try
                {
                    DatNames = ComMsg.ReturnDataSet("SELECT Name FROM RptAlertRecipient ORDER BY Name DESC");

                    for (int i = 0; i < DatNames.Tables[0].Rows.Count; i++)
                    {
                        _Name = DatNames.Tables[0].Rows[i].ItemArray.GetValue(0).ToString();

                        DatMsg = ComMsg.ReturnDataSet("SELECT RptAlertRecipient.Name,  RptAlertRecipient.Email,  RptAlerts.Factory,  RptAlerts.AlertTime,  RptAlerts.Description " +
                                           "FROM RptAlerts " +
                                           "INNER JOIN RptAlertTypes ON  RptAlerts.AlertTypeID = RptAlertTypes.ID " +
                                           "INNER JOIN RptAlertType_RecipientMapping ON  RptAlertTypes.ID = RptAlertType_RecipientMapping.AlertTypeID " +
                                           "INNER JOIN RptAlertRecipient ON  RptAlertType_RecipientMapping.AlertRecipientID = RptAlertRecipient.ID " +
                                           "WHERE RptAlertRecipient.Name ='" + _Name + "'" +
                                           "ORDER BY RptAlertRecipient.Name DESC");
                        for (int j = 0; j < DatMsg.Tables[0].Rows.Count; j++)
                        {
                            ToEmail = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(1).ToString();
                            ToName = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(0).ToString();
                            AlertList.Add(DatMsg.Tables[0].Rows[j].ItemArray.GetValue(4).ToString() + "<br/>");
                            TotMsg = (j + 1).ToString();
                        }
                        string to = ToEmail;
                        string from = "helpdesk@mydomain.com";
                        string subject = "Alert In Time : You Have " + TotMsg + " Alerts";
                        string msgBody = "Dear " + ToName + ",<br/><br/>";
                        msgBody += "<b>You Have " + TotMsg + " Alerts</b><br/><br/>";
                        msgBody += string.Join("<br/>", AlertList);
                        msgBody += "<br/><br/>Regards<br/>Sent by Alert Service<br/>(Please do not reply to this email.)";
                        MailMessage msg = new MailMessage(from, to, subject, msgBody);
                        msg.IsBodyHtml = true;
                        SmtpClient clnt = new SmtpClient("outlook.mydomain.local", 25);
                        clnt.EnableSsl = false;
                        clnt.Credentials = new System.Net.NetworkCredential("helpdesk@mydomain.com", "password");
                        clnt.Send(msg);
                        AlertList.Clear();
                    }
                }
                catch (Exception ex)
                {
                    error.ExceptionMessage = ex.ToString();
                    speechSynthesizerObj = new SpeechSynthesizer();
                    speechSynthesizerObj.SpeakAsync(ex.Message);//Speaks the Error
                    SendtoAdmin();
                }
            }
            #region SendMails
            protected void SendtoAdmin()
            {
                //Send mail to Admin
                string to = "admin@mydomain.com";
                string from = "helpdesk@mydomain.com";
                string subject = "System Failure";
                string msgBody = "Dear Admin,<br/><br/>System Failure in Alert System.<br/>Please Attend Immediately.<br/>"+ error.ExceptionMessage + "<br/><br/>Regards<br/>Sent By Alert System";
                MailMessage msg = new MailMessage(from, to, subject, msgBody);
                msg.IsBodyHtml = true;
                SmtpClient clnt = new SmtpClient("outlook.mydomain.local", 25);
                clnt.EnableSsl = false;
                clnt.Credentials = new System.Net.NetworkCredential("helpdesk@mydomain.com", "sl@ithd");
                clnt.Send(msg);
            }
            #endregion      
        }
    }

Answer 2

I suppose your current code only sends a mail to the last user in the list?

You will have to include the code that generates and sends the mail in your for loop.

for (int j = 0; j < DatMsg.Tables[0].Rows.Count; j++)
{
    ToEmail = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(1).ToString();
    ToName = DatMsg.Tables[0].Rows[j].ItemArray.GetValue(0).ToString();
    AlertList.Add(DatMsg.Tables[0].Rows[j].ItemArray.GetValue(4).ToString() + "<br/>");
    TotMsg = (j + 1).ToString();

    string to = ToEmail;
    string from = "helpdesk@mydomain.com";
    string subject = "Alert In Time : You Have "+TotMsg+ " Alerts";
    string msgBody = "Dear " + ToName + ",<br/><br/>";
    msgBody += "<b>You Have " + TotMsg + " Alerts</b><br/><br/>";
    msgBody += string.Join("<br/>", AlertList);
    msgBody += "<br/><br/>Regards<br/>Sent by Alert Service<br/>(Please do not reply to this email.)";
    MailMessage msg = new MailMessage(from, to, subject, msgBody);
    msg.IsBodyHtml = true;
    SmtpClient clnt = new SmtpClient("outlook.mydomain.local", 25);
    clnt.EnableSsl = false;
    clnt.Credentials = new System.Net.NetworkCredential("helpdesk@mydomain.com", "password");
    clnt.Send(msg);
}