using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net.Mail;
using System.Net.Mime;
using System.IO;
using System.Timers;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Diagnostics;
using System.Data.SQLite;
using System.Threading;
namespace zizhuTools
{
public partial class sendEmail : Form
{
public sendEmail()
{
InitializeComponent();
}
public Excel.Application Eapp;
Excel.Worksheet sht;
Excel.Range rng;
// public Word.Application msWord;
//Word.Document wd;
DateTime dtTime;//截至目前
string sAnual;//年度
int iTitleNum = 4;//标题行
int iDataNum = 6 ;//数据行
int iCols;//总列数
int iRows;//总行数
object[,] oTitle;//标题字段
Stopwatch sw = new Stopwatch();
SQLiteConnection m_dbConnection;
string sql;
string sFolderPath;
SmtpClient mSmtpClient;
private void sendEmail_Load(object sender, EventArgs e)
{
sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
sFolderPath = sFolderPath.Replace("\\", "/") + "DataAsStatics/NewDataBase.sqlite";
m_dbConnection = new SQLiteConnection("Data Source=" + sFolderPath);
m_dbConnection.Open();
sql = "select * from EmailInfo limit 1";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader rdrr = command.ExecuteReader();
if (rdrr.HasRows)
{
while (rdrr.Read())
{
tb_emailAdr.Text = rdrr["mailAddress"].ToString();
tb_emailKey.Text = rdrr["AuthorityCode"].ToString();
tb_address.Text = rdrr["addr"].ToString();
tb_tel.Text = rdrr["telphone"].ToString();
}
}
tb_lastPayday.Text = string.Format("{0}年{1}月{2}日", DateTime.Now.AddMonths(1).Year, DateTime.Now.AddMonths(1).Month, DateTime.Now.AddMonths(1).Day);//逾期日;
dtTime = DateTime.Today;//截至目前
sAnual = dtTime.Year.ToString();//X年度
tb_annual.Text = sAnual;//自动填充当年年度
//界面Load出来，显示列信息
Eapp = Globals.ThisAddIn.Application;
sht = Eapp.ActiveWorkbook.ActiveSheet;
iCols = sht.Range["A4"].CurrentRegion.Columns.Count;
//iRows = sht.UsedRange.Rows.Count;
// iRows = sht.Range["A" + 3].CurrentRegion.Rows.Count;
oTitle = sht.Range["A4"].Resize[1, iCols+1].Value;
for (int i = 1; i <= oTitle.GetLength(1); i++)
{
string values = oTitle[1, i] == null ? "Column" + i : oTitle[1, i].ToString();
cb_account.Items.Add(values);
cb_amount.Items.Add(values);
cb_isSend.Items.Add(values);
cb_name.Items.Add(values);
cb_qqnum.Items.Add(values);
cb_result.Items.Add(values);
}
cb_account.SelectedIndex = 0;
cb_amount.SelectedIndex = 0;
cb_isSend.SelectedIndex = 0;
cb_name.SelectedIndex = 0;
cb_qqnum.SelectedIndex = 0;
cb_result.SelectedIndex = oTitle.GetLength(1)-1;
iRows = sht.UsedRange.Rows.Count;
nud_data_end.Value = iRows < 200 ? iRows : int.Parse(nud_data.Value.ToString()) + 200;
}
//测试键
string sLastpaydate;
string sAdrName;
string sTel;
string sDate;
string adr;
string pwd;
private void button1_Click(object sender, EventArgs e)//测试按钮
{
sAnual = tb_annual.Text.ToString();//年度
sLastpaydate = tb_lastPayday.Text.ToString();//最迟还款日期
sAdrName = "【测试】"+ tb_address.Text.ToString();//XX县管理中心
sTel = tb_tel.Text.ToString();//联系电话
//发邮件
sDate = string.Format("{0}年{1}月{2}日", dtTime.Year, dtTime.Month, dtTime.Day);//当前日期X年X月X日
adr = tb_emailAdr.Text.ToString();//用户发送邮件的邮箱地址
pwd = tb_emailKey.Text.ToString();//用户邮箱的授权码
mSmtpClient = new SmtpClient();
mSmtpClient.Host = "smtp.qq.com";
mSmtpClient.Port = Convert.ToInt32(25);
mSmtpClient.EnableSsl = false;
mSmtpClient.UseDefaultCredentials = false;
nc = new System.Net.NetworkCredential(adr, pwd);
mSmtpClient.Credentials = nc.GetCredential(mSmtpClient.Host, mSmtpClient.Port, "NTLM");
string testResult = mSendEmail(sAdrName, adr, adr, "0.00");
MessageBox.Show("本次即将执行批量发送的测试邮件已发送，请查收,状态："+testResult);
}
public string mSendEmail(string Name,string Qmail,string Acc,string Amt)
{
try
{
MailMessage mMailMessage = new MailMessage();
mMailMessage.From = new MailAddress(adr);//发送人
mMailMessage.To.Add(Qmail);//收件人
mMailMessage.Priority = MailPriority.Normal;
mMailMessage.BodyEncoding = System.Text.Encoding.UTF8;
mMailMessage.IsBodyHtml = true;
mMailMessage.Subject = string.Format("（{0}）生源地信用助学贷款催款通知书", Name);//标题 - 变量获取姓名+标题
mMailMessage.Body = "<p style=\"font-family:宋体;font-size:14pt;line-height:36px\">";
mMailMessage.Body += string.Format("<span>亲爱的<u>{0}</u>同学：</span>", Name);
mMailMessage.Body += "<span style=\"text-indent:2em;display:block\">根据您与国家开发银行签订的《助学贷款借款合同》的约定，";
mMailMessage.Body += string.Format("<span style=\"font-family:Calibri\">{0}</span>", sAnual);
mMailMessage.Body += "年度需还款本息";
mMailMessage.Body += string.Format("<u><span style=\"text-decoration:underline;font-family:Calibri\">{0}</span></u>", Amt);
mMailMessage.Body += "元，截至";
mMailMessage.Body += string.Format("<u><span style=\"text-decoration:underline;font-family:Calibri\">{0}</span></u>", sDate);
mMailMessage.Body += "，你的贷款支付宝账户（";
mMailMessage.Body += string.Format("<u><span style=\"text-decoration:underline;font-family:Calibri \">{0}</span></u>", Acc);
mMailMessage.Body += "）中余额不足，还存在差额，请你务必于";
mMailMessage.Body += string.Format("<u><span style=\"text-decoration:underline;font-family:Calibri\">{0}</span></u>", sLastpaydate);
mMailMessage.Body += "之前登录国家开发银行生源地助学贷款学生在线系统，主动如约还款。";
mMailMessage.Body += "还款方式一：将差额资金在线还款至本人贷款支付宝帐户上；还款方式二：学生本人或共同借款人带银联银行卡和学生身份证号到办理贷款的";
mMailMessage.Body += string.Format("<u><span>{0}</span></u>", sAdrName);
mMailMessage.Body += "刷POS机还款。并注意以下事项：";
mMailMessage.Body += "</span>";
mMailMessage.Body += "<span style=\"text-indent:2em;display:block\"> 1.根据银行规定，除11月外，每月的1日-15日均可办理还款，如办理提前还款，先通过学生在线服务系统或POS机提交提前还款申请，然后在线还款至贷款支付宝账户或携带银联银行卡到";
mMailMessage.Body += string.Format("<u><span>{0}</span></u>", sAdrName);
mMailMessage.Body += "刷POS机还款。</span>";
mMailMessage.Body += "<span style=\"text-indent:2em;display:block\">2.请您收到本催收通知书后，按时还款，按照《征信管理条例》规定，如果贷款逾期，负面信息将进入个人征信系统，";
mMailMessage.Body += "将对借款人和共同借款人房贷、车贷、信用卡等金融业务办理造成重大影响。</span>";
mMailMessage.Body += "<span style=\"text-indent:2em;display:block\">3.我们不会通过任何方式向你索取银行卡号、密码和短信验证码等信息，也不会打电话要你通过ATM转账至私人账户等，";
mMailMessage.Body += "请注意信息保密，谨防网络电信诈骗！</span>";
mMailMessage.Body += "<span style=\"text-indent:2em;display:block\">4.如需帮助，请致电国家开发银行生源地信用助学贷款全国统一服务热线:<span style=\"font-family:Calibri\">95593</span>，也可致电";
mMailMessage.Body += string.Format("<u><span>{0}</span></u>", sAdrName);
mMailMessage.Body += string.Format("，电话：<span style=\"font-family:Calibri\">{0}</span></span>", sTel);
mMailMessage.Body += "<br>";
mMailMessage.Body += string.Format("<span style=\"text-indent:1em;display:block\"><u>{0}</u></span>", sAdrName);
mMailMessage.Body += string.Format("<span style=\"text-indent:1em;display:block\">{0}</span>", sDate);
mMailMessage.Body += "</p>";
mSmtpClient.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
mSmtpClient.Send(mMailMessage);
return "投递正常";
}
catch(Exception ex)
{
ErrNum += 1;
textBox1.AppendText("\r\n邮箱:" + Qmail + "尝试发送邮件时出现错误，提示："+ex.Message);
return "投递错误";
}
}
System.Net.NetworkCredential nc;
//选择所需字段所在列combox
private void nud_title_ValueChanged(object sender, EventArgs e)
{
iTitleNum = int.Parse(nud_title.Value.ToString());
nud_data.Value = iTitleNum + 1;
//重构字段列数据
//清空内容
cb_account.Items.Clear();
cb_amount.Items.Clear();
cb_isSend.Items.Clear();
cb_name.Items.Clear();
cb_qqnum.Items.Clear();
cb_result.Items.Clear();
//重新获取标题行并赋值
oTitle = sht.Range["A"+iTitleNum].Resize[1, iCols+1].Value;
for (int i = 1; i <= oTitle.GetLength(1); i++)
{
string values = oTitle[1, i] == null ? "Column" + i : oTitle[1, i].ToString();
cb_account.Items.Add(values);
cb_amount.Items.Add(values);
cb_isSend.Items.Add(values);
cb_name.Items.Add(values);
cb_qqnum.Items.Add(values);
cb_result.Items.Add(values);
}
cb_account.SelectedIndex = 0;
cb_amount.SelectedIndex = 0;
cb_isSend.SelectedIndex = 0;
cb_name.SelectedIndex = 0;
cb_qqnum.SelectedIndex = 0;
cb_result.SelectedIndex = oTitle.GetLength(1)-1;
}
int ErrNum = 0;
//发送按钮
int iDataEndNum;
private void button2_Click(object sender, EventArgs e)
{
adr = tb_emailAdr.Text.ToString();//用户发送邮件的邮箱地址
pwd = tb_emailKey.Text.ToString();//用户邮箱的授权码
sAdrName = tb_address.Text.ToString();//XX县管理中心
sTel = tb_tel.Text.ToString();//联系电话
sql = "DELETE from EmailInfo";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = string.Format("insert into EmailInfo (mailAddress,AuthorityCode,addr,telphone)values(\"{0}\",\"{1}\",\"{2}\",\"{3}\")", adr, pwd, sAdrName, sTel);
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
m_dbConnection.Close();
//再次确认是否发送邮件，取消反悔
DialogResult confirm =MessageBox.Show("确认发送邮件？确认后无法取消", "请再次确认", MessageBoxButtons.OKCancel);
if (DialogResult.OK == confirm)
{
sw.Start();
//先获取EXCEL的内容
//用字典存储相关信息
sAnual = tb_annual.Text.ToString();//年度
sLastpaydate = tb_lastPayday.Text.ToString();//最迟还款日期
//发邮件
sDate = string.Format("{0}年{1}月{2}日", dtTime.Year, dtTime.Month, dtTime.Day);//当前日期X年X月X日
//重新赋值相关变量
int iIsSend = cb_isSend.SelectedIndex + 1;//是否催收
int iAccount = cb_account.SelectedIndex + 1;//XX账户
int iAmount = cb_amount.SelectedIndex + 1;//XX金额
int iName = cb_name.SelectedIndex + 1;//姓名
int iqq = cb_qqnum.SelectedIndex + 1;//qq号
int iRes = cb_result.SelectedIndex + 1;//结果存放
// List<string> lst_SendName = new List<string>(); ;
iTitleNum = int.Parse(nud_title.Value.ToString());
iDataNum = int.Parse(nud_data.Value.ToString());
iDataEndNum = int.Parse(nud_data_end.Value.ToString());
object[,] arr = sht.Range[sht.Cells[iDataNum, 1],sht.Cells[iDataEndNum, iCols]].Value;
object[,] result = new object[arr.GetLength(0), 3];
int iXr = 0;
//result[0, 0] = "序号";
//result[0, 1] = "姓名";
//result[0, 2] = "状态";
//与qq邮箱建立连接
mSmtpClient = new SmtpClient();
mSmtpClient.Host = "smtp.qq.com";
mSmtpClient.Port = Convert.ToInt32(25);
mSmtpClient.EnableSsl = false;
mSmtpClient.UseDefaultCredentials = false;
nc = new System.Net.NetworkCredential(adr, pwd);
mSmtpClient.Credentials = nc.GetCredential(mSmtpClient.Host, mSmtpClient.Port, "NTLM");
// mSendEmail(sAdrName, adr, adr, "0.00");
for (int i = 1; i <= arr.GetLength(0); i++)
{
string sAcc = "";
string sAmt = "";
string sName = "";
string sQmail = "";
if (arr[i, iIsSend] != null && arr[i, iIsSend].ToString() == "是")
{
try
{
sName = arr[i, iName].ToString();
sQmail = arr[i, iqq].ToString() + "@qq.com";
sAmt = arr[i, iAmount].ToString();
sAcc = arr[i, iAccount].ToString();
//发送邮件
string status = mSendEmail(sName, sQmail, sAcc, sAmt);
result[iXr, 0] = status;
textBox1.AppendText("\r\n"+sName+":已发送邮件，状态："+ status);
if (ErrNum > 5)
{
MessageBox.Show("投递邮件时异常量多,停止继续投递", "异常终止");
break;
}
//lst_SendName.Add("第" + i.ToString() + "行:" + sName + ":" + status);
}
catch (Exception ex)
{
//result[iXr, 0] = i.ToString();
//result[iXr, 1] = arr[i, iName]!=null?arr[i, iName].ToString():"";
//result[iXr, 2] = "生成邮件错误";
// iXr += 1;
result[iXr, 0] = "生成邮件错误";
textBox1.AppendText("\r\n第【" + i.ToString() + "】行出错，可能关键信息缺失，提示:" + ex.Message);
ErrNum += 1;
if (ErrNum > 5)
{
MessageBox.Show("投递邮件时异常量多,停止继续投递", "异常终止");
break;
}
}
//Thread thrd = new Thread(thr_time);
}
//result[iXr, 0] = "不需要催收";
iXr += 1;
Thread.Sleep(500);
}
sht.Cells[iDataNum, iRes].Resize[result.GetLength(0), 1].Value = result;
//Excel.Workbook wb = Eapp.ActiveWorkbook;
//wb.Worksheets.Add(Type.Missing, wb.Worksheets[wb.Worksheets.Count], 1);
//sht = wb.Worksheets[wb.Worksheets.Count];
//sht.Name = "发送一览";
//sht.Range["A1"].Value = "发送一览";
//sht.Range["A"+(iCols+1)].Resize[result.GetLength(0), 3].Value = result;
//wb.Save();
sw.Stop();
textBox1.AppendText("\r\n用时：" + sw.Elapsed);
MessageBox.Show("已完成本次批量发送邮件任务，请查看面板信息是否含有异常提示","完成");
}
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
System.Diagnostics.Process.Start("https://service.mail.qq.com/cgi-bin/help?subtype=1&&no=1001256&&id=28");
}
private void nud_data_ValueChanged(object sender, EventArgs e)
{
iRows = sht.UsedRange.Rows.Count ;
nud_data_end.Value = iRows<200? iRows: int.Parse(nud_data.Value.ToString()) + 200;
}
}
}
