public static void SendEmail()
{
try
{
string query = "Select * from [172.16.1.42].[ISMS].[dbo].EmailPool_VPS_Notifcation where flag = 'N'";
string queryUpdate = string.Empty;
DataTable dtVPS_Notification = SQLOperations.GetTable(query);
SmtpClient smpt = new SmtpClient("192.168.1.1");
foreach (DataRow item in dtVPS_Notification.Rows)
{
try
{
MailMessage msg = new MailMessage(FromEmail, item["toemail"].ToString());
Attachment _attachment = new Attachment(item["attachment"].ToString());
msg.Subject = item["subject"].ToString();
msg.Body = item["body"].ToString();
msg.IsBodyHtml = true;
msg.Attachments.Add(_attachment);
//smpt.Send(msg);
queryUpdate = "update [172.16.1.42].[ISMS].[dbo].EmailPool_VPS_Notifcation set sentdate = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:s") + "', flag = 'Y' ,message = 'Successfully Sent' where toemail = '" + item["toemail"].ToString() + "' and flag = 'N' ";
SQLOperations.ExecuteQuery(queryUpdate);
}
catch (Exception ex)
{
queryUpdate = "update [172.16.1.42].[ISMS].[dbo].EmailPool_VPS_Notifcation set sentdate = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:s") + "' ,message = '" + ex.Message + "' where toemail = '" + item["toemail"].ToString() + "' and flag = 'N' ";
SQLOperations.ExecuteQuery(queryUpdate);
}
}
}
catch (Exception)
{
throw;
}
}
private void SetPortfolios()
{
try
{
string queryEmail = "Select * from Customer";
//queryEmail = queryEmail + " where Portfolio_ID='137683-905'";
queryEmail += " where SUBSTRING(Portfolio_ID,8,3) >= '900' AND EMAIL LIKE '%@%' AND DATEDIFF(day,GETDATE(),Ret_Date) =30 ";
string queryDispatch = "Select Portfolio_ID, ret_Date, Email from Customer ";
queryDispatch += " where SUBSTRING(Portfolio_ID,8,3) >= '900' AND EMAIL NOT LIKE '%@%' AND DATEDIFF(day,GETDATE(),Ret_Date) =30";
dtPortfoliosEmail = SQLOperations.GetTable(queryEmail);
dtPortfoliosDispatch = SQLOperations.GetTable(queryDispatch);
}
catch (Exception)
{
throw;
}
}