Hi gersen123,
For this use Windows Service which will execute the send email code every hour.
Set the IntervalMinutes to 60 in the appSettings.
Then in the SchedularCallback function write code for sending email.
Add another column in the table to main the status for sent email.
Then in the query select top 50 records from the table with adding another condition where status is null.
So that it will check the record to which the email to be send.
For set status you need to update each record after sending email.
Refer below code.
C#
private void SchedularCallback(object e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
con.Open();
string str = "select TOP 50 * from tbluser where role='ASC' AND ststus IS NULL";
SqlCommand cmd = new SqlCommand(str, con);
SqlDataReader reader = cmd.ExecuteReader();
ArrayList emailArray = new ArrayList(1);
while (reader.Read())
{
emailArray.Add(reader["email"]);
}
reader.Close();
con.Close();
foreach (string email in emailArray)
{
SmtpClient smtpclient = new SmtpClient();
MailMessage mail = new MailMessage();
if (fuAttachment.HasFile)
{
string FileName = Path.GetFileName(fuAttachment.PostedFile.FileName);
mail.Attachments.Add(new Attachment(fuAttachment.PostedFile.InputStream, FileName));
}
MailAddress fromaddress = new MailAddress("email");
smtpclient.Host = "smtp.gmail.com";
smtpclient.Port = 587;
mail.From = fromaddress;
mail.To.Add(email);
mail.Subject = txtsubject.Text;
mail.IsBodyHtml = true;
string Body = txtmessage.Text;
mail.Body = Body;
smtpclient.EnableSsl = true;
smtpclient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpclient.Credentials = new System.Net.NetworkCredential("email", "password");
smtpclient.Send(mail);
// Update sent status in table.
UpdateEmailStatus(email);
}
}
private void UpdateEmailStatus(string email)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "UPDATE tbluser SET status = 'sent' WHERE email = @email";
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@email", email);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
Private Sub SchedularCallback(ByVal e As Object)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim con As SqlConnection = New SqlConnection(constr)
con.Open()
Dim str As String = "select TOP 50 * from tbluser where role='ASC' AND ststus IS NULL"
Dim cmd As SqlCommand = New SqlCommand(str, con)
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim emailArray As ArrayList = New ArrayList(1)
While reader.Read()
emailArray.Add(reader("email"))
End While
reader.Close()
con.Close()
For Each email As String In emailArray
Dim smtpclient As SmtpClient = New SmtpClient()
Dim mail As MailMessage = New MailMessage()
If fuAttachment.HasFile Then
Dim FileName As String = Path.GetFileName(fuAttachment.PostedFile.FileName)
mail.Attachments.Add(New Attachment(fuAttachment.PostedFile.InputStream, FileName))
End If
Dim fromaddress As MailAddress = New MailAddress("email")
smtpclient.Host = "smtp.gmail.com"
smtpclient.Port = 587
mail.From = fromaddress
mail.[To].Add(email)
mail.Subject = txtsubject.Text
mail.IsBodyHtml = True
Dim Body As String = txtmessage.Text
mail.Body = Body
smtpclient.EnableSsl = True
smtpclient.DeliveryMethod = SmtpDeliveryMethod.Network
smtpclient.Credentials = New System.Net.NetworkCredential("email", "password")
smtpclient.Send(mail)
UpdateEmailStatus(email)
Next
End Sub
Private Sub UpdateEmailStatus(ByVal email As String)
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "UPDATE tbluser SET status = 'sent' WHERE email = @email"
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand(query)
cmd.Connection = con
cmd.Parameters.AddWithValue("@email", email)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub