应用场景:
女友工作因素,需要定期的与客户发送组织会议邀请,发送对象不同,内容基本相同,附件不同,每次发送时麻烦费事耽误时间,此工具的开发应用必然会深受女友的喜欢、大大减少其工作量。
设计:
Excel表对应,收件人对象信息,遍历该对象列表,组织邮件内容,选中附件列表,一键发送并展示发送结果。
运行环境:
邮件服务器:163.com stmp方式
步骤:
- 读取excel中收件人列表,转存到内存DataTable中
- 邮件正文采用html格式,可以发送图片,保存预定好的邮件格式
- 根据邀请函模板生成每个人的邀请函doc文件,转换格式为pdf文件
- 遍历DataTable收件人,添加附件发送邮件
- 个人邮箱设置,发送步骤、进度显示
程序代码:
string excelFilePath= "";string wordExFilePath = "";string wordFilePath = "";string senderEmail = null;string senderPassword = null;string emailSubject = "";string emailBody = "";bool isHaveImg = false;private void Form1_Load(object sender, EventArgs e){}private System.Data.DataTable GetExcelData(){Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Sheets sheets;Microsoft.Office.Interop.Excel.Workbook workbook = null;object oMissiong = System.Reflection.Missing.Value;System.Data.DataTable dt = new System.Data.DataTable();try{if (app == null)return null;workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);//将数据读入到DataTable中——Start sheets = workbook.Worksheets;Worksheet worksheet = (Worksheet)sheets.get_Item(1);//读取第一张表if (worksheet == null)return null;string cellContent;int iRowCount = worksheet.UsedRange.Rows.Count;int iColCount = worksheet.UsedRange.Columns.Count;Microsoft.Office.Interop.Excel.Range range;//负责列头StartDataColumn dc;int ColumnID = 1;range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];while (range.Text.ToString().Trim() != ""){dc = new DataColumn();dc.DataType = System.Type.GetType("System.String");dc.ColumnName = range.Text.ToString().Trim();dt.Columns.Add(dc);range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, ++ColumnID];}for (int iRow = 2; iRow <= iRowCount; iRow++){DataRow dr = dt.NewRow();for (int iCol = 1; iCol <= iColCount; iCol++){range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];cellContent = (range.Value2 == null) ? "" : range.Text.ToString();dr[iCol - 1] = cellContent;}dt.Rows.Add(dr);}//将数据读入到DataTable中——Endreturn dt; }catch{return null;}finally{workbook.Close(false, oMissiong, oMissiong);System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);workbook = null;app.Workbooks.Close();app.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(app);app = null;}}private bool Convert(string sourcePath, string findName, string replaceName){bool result;object paramMissing = Type.Missing;Word.ApplicationClass wordApplication = new Word.ApplicationClass();Word.Document wordDocument = null;try{object paramSourceDocPath = sourcePath;string paramExportFilePath = System.Environment.CurrentDirectory + @"\" + replaceName + @"\附件_邀请函.pdf";if (!Directory.Exists(System.Environment.CurrentDirectory + @"\" + replaceName)){Directory.CreateDirectory(System.Environment.CurrentDirectory + @"\" + replaceName);}Word.WdExportFormat paramExportFormat = Word.WdExportFormat.wdExportFormatPDF;bool paramOpenAfterExport = false;Word.WdExportOptimizeFor paramExportOptimizeFor =Word.WdExportOptimizeFor.wdExportOptimizeForPrint;Word.WdExportRange paramExportRange = Word.WdExportRange.wdExportAllDocument;int paramStartPage = 0;int paramEndPage = 0;Word.WdExportItem paramExportItem = Word.WdExportItem.wdExportDocumentWithMarkup;bool paramIncludeDocProps = true;bool paramKeepIRM = true;Word.WdExportCreateBookmarks paramCreateBookmarks =Word.WdExportCreateBookmarks.wdExportCreateWordBookmarks;bool paramDocStructureTags = true;bool paramBitmapMissingFonts = false;bool paramUseISO19005_1 = false;wordDocument = wordApplication.Documents.Open(ref paramSourceDocPath, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing);if (wordDocument != null){#region 替换收件人object findtext = findName;object replacewith = replaceName;object replace= Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll;wordApplication.Selection.Find.Execute(ref findtext, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing,ref replacewith, ref replace,ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing);wordDocument.Save();#endregion#region 导出pdfwordDocument.ExportAsFixedFormat(paramExportFilePath,paramExportFormat, paramOpenAfterExport,paramExportOptimizeFor, paramExportRange, paramStartPage,paramEndPage, paramExportItem, paramIncludeDocProps,paramKeepIRM, paramCreateBookmarks, paramDocStructureTags,paramBitmapMissingFonts, paramUseISO19005_1,ref paramMissing);//导出pdf方法二//wordApplication.ActiveDocument.SaveAs2(ref Target, ref format,// ref Unknown, ref Unknown, ref Unknown,//ref Unknown, ref Unknown, ref Unknown,//ref Unknown, ref Unknown, ref Unknown,//ref Unknown, ref Unknown, ref Unknown,//ref Unknown, ref Unknown, ref Unknown); #endregion}result = true;}catch (Exception ex){Console.WriteLine(ex.ToString());return false;}finally{if (wordDocument != null){wordDocument.Close(ref paramMissing, ref paramMissing, ref paramMissing);wordDocument = null;}if (wordApplication != null){wordApplication.Quit(ref paramMissing, ref paramMissing, ref paramMissing);wordApplication = null;}GC.Collect();GC.WaitForPendingFinalizers();GC.Collect();GC.WaitForPendingFinalizers();}return result;}private string ReadDocument(string sourcePath){object paramMissing = Type.Missing;Word.ApplicationClass wordApplication = new Word.ApplicationClass();Word.Document wordDocument = null;try{object paramSourceDocPath = sourcePath;object encoding = System.Text.Encoding.UTF8;wordDocument = wordApplication.Documents.Open(ref paramSourceDocPath, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing, ref paramMissing, ref paramMissing,ref paramMissing);return (wordDocument != null && wordDocument.Content != null && wordDocument.Content.Text != null) ? wordDocument.Content.Text : "";}catch (Exception ex){Console.WriteLine(ex.ToString());return "";}finally{if (wordDocument != null){wordDocument.Close(ref paramMissing, ref paramMissing, ref paramMissing);wordDocument = null;}if (wordApplication != null){wordApplication.Quit(ref paramMissing, ref paramMissing, ref paramMissing);wordApplication = null;}GC.Collect();GC.WaitForPendingFinalizers();GC.Collect();GC.WaitForPendingFinalizers();}}private string ReadModehtml(string sourcePath){string strhtmlContent = "";try{System.IO.StreamReader sr = new System.IO.StreamReader(sourcePath);strhtmlContent = sr.ReadToEnd();}catch (Exception ex){return "";}return strhtmlContent; }private bool SendEmail(string subject,string body,string receiveName,string receiveEmail){try{//SmtpClient下的一个对象,用以设置邮件的主题和内容System.Net.Mail.MailMessage myMail = new System.Net.Mail.MailMessage();//发送端到接收端的邮箱地址myMail = new System.Net.Mail.MailMessage(senderEmail, receiveEmail);myMail.Subject = subject;myMail.Body = body =body.Replace("张三", receiveName);myMail.IsBodyHtml = true;myMail.BodyEncoding = System.Text.Encoding.UTF8;//邮件内容编码if (!string.IsNullOrEmpty(receiveName)){//建立邮件附件类的一个对象,语法格式为System.Net.Mail.Attachment(文件名,文件格式)System.Net.Mail.Attachment myAttachment = new System.Net.Mail.Attachment(System.Environment.CurrentDirectory + @"\" + receiveName + @"\附件_邀请函.pdf", System.Net.Mime.MediaTypeNames.Application.Octet);//MIME协议下的一个对象,用以设置附件的创建时间,修改时间以及读取时间//System.Net.Mime.ContentDisposition disposition = myAttachment.ContentDisposition;//disposition.CreationDate = System.IO.File.GetCreationTime(file);//disposition.ModificationDate = System.IO.File.GetLastWriteTime(file);//disposition.ReadDate = System.IO.File.GetLastAccessTime(file);//用smtpclient对象里attachments属性,添加上面设置好的myattachmentmyMail.Attachments.Add(myAttachment);}if (isHaveImg){AlternateView avHtml = AlternateView.CreateAlternateViewFromString(body, null, MediaTypeNames.Text.Html);LinkedResource pic1 = new LinkedResource("123.jpg", MediaTypeNames.Image.Jpeg);pic1.ContentId = "Pic1";avHtml.LinkedResources.Add(pic1);myMail.AlternateViews.Add(avHtml);}//建立发送对象client,验证邮件服务器,服务器端口,用户名,以及密码System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient("smtp.163.com", 25);client.Credentials = new System.Net.NetworkCredential(senderEmail, senderPassword);client.Timeout = 1000 * 60 * 2;//2分钟//client.UseDefaultCredentials = false;client.Send(myMail);return true;}catch (Exception ex){return false;}}private void button1_Click(object sender, EventArgs e){//选择excel文件OpenFileDialog openFile = new OpenFileDialog();openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);openFile.Multiselect = false;if (openFile.ShowDialog() == DialogResult.Cancel)return;excelFilePath = openFile.FileName;string fileType = System.IO.Path.GetExtension(excelFilePath);if (string.IsNullOrEmpty(fileType))return;this.textBox1.Text = excelFilePath;}private void button2_Click(object sender, EventArgs e){OpenFileDialog openFile = new OpenFileDialog();openFile.Filter = "Word document(*.doc)|*.doc|Word document(*.docx)|*.docx";openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);openFile.Multiselect = false;if (openFile.ShowDialog() == DialogResult.Cancel)return;wordExFilePath = openFile.FileName;string fileType = System.IO.Path.GetExtension(wordExFilePath);if (string.IsNullOrEmpty(fileType))return;this.textBox2.Text = wordExFilePath;}private void button3_Click(object sender, EventArgs e){//测试用户密码是否正常,给自己发一封邮件if (string.IsNullOrEmpty(this.textBox3.Text) || string.IsNullOrEmpty(this.textBox4.Text)){MessageBox.Show("笨蛋,邮箱的账户和密码都是必填项!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}string strContentExFile = ReadDocument(wordExFilePath);string strContentFile = ReadModehtml(wordFilePath);if (string.IsNullOrEmpty(strContentExFile) || string.IsNullOrEmpty(strContentFile) || !strContentExFile.Contains("张三") || !strContentFile.Contains("张三")){MessageBox.Show("笨蛋,邮件正文和附件都是必选项,同时都必须以 [张三] 为模板!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}if (!strContentExFile.Contains("张三")){MessageBox.Show("笨蛋,邮件附件文件都必须以 [张三] 为模板!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}if (!strContentFile.Contains("张三")){MessageBox.Show("笨蛋,邮件正文文件都必须以 [张三] 为模板!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}senderEmail = this.textBox3.Text.Trim();senderPassword = this.textBox4.Text.Trim();this.button3.Enabled = false;if (SendEmail("测试邮件,不必理我", ReadModehtml(wordFilePath), "", senderEmail)){textBox5.Text = "邮件测试成功!";button4.Enabled = true;button3.Enabled = false;textBox4.Text = "******";}else{textBox5.Text = "<@.@>邮件发送测试失败,确保账户信息、网络是否正常!";this.button3.Enabled = true;}}private void button4_Click(object sender, EventArgs e){if (string.IsNullOrEmpty(excelFilePath) || string.IsNullOrEmpty(wordExFilePath) || string.IsNullOrEmpty(wordFilePath)){MessageBox.Show("笨蛋,excel和word文件都得选择完备!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}textBox5.Text += System.Environment.NewLine + "excel中收件人信息读取中......";System.Data.DataTable dt = GetExcelData();if (dt == null || dt.Rows.Count == 0){MessageBox.Show("笨蛋,excel文件是不是格式什么有问题,不要随意修改模板!", "Matin大叔提醒你", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}button4.Enabled = false;textBox5.Text += System.Environment.NewLine + "excel中收件人信息读取完成!收件人合计人数 " + dt.Rows.Count;textBox5.Text += System.Environment.NewLine + "word邀请函模板读取完成!";textBox5.Text += System.Environment.NewLine + "word邀请函按照收件人副本制作中......";string findTextName = "张三";List<string> listFailedName = new List<string>();foreach (var DataRow in dt.Rows){DataRow row = DataRow as DataRow;string sName = row["姓名"].ToString();string sEmail = row["邮箱"].ToString();if (Convert(wordExFilePath, findTextName, sName)){textBox5.Text += System.Environment.NewLine + string.Format("pdf附件邀请函 [{0}] 已生成成功!", sName);findTextName = sName;}else{textBox5.Text += System.Environment.NewLine + string.Format("<@.@>pdf附件邀请函 [{0}] 生成失败,不过,没关系等最后结束后重试!", sName);listFailedName.Add(sName);}}if (listFailedName != null && listFailedName.Count > 0){foreach (var item in listFailedName){LogHelper.CreateLog(string.Format("pdf附件邀请函 [{0}] 生成失败,不过,没关系等最后结束后重试!", item));}}textBox5.Text += System.Environment.NewLine + string.Format("pdf附件邀请函生成已结束!");textBox5.Text += System.Environment.NewLine + string.Format("邮件开始发送中......");List<string> listsendFailedName = null;listsendFailedName = new List<string>();foreach (var DataRow in dt.Rows){DataRow row = DataRow as DataRow;string sName = row["姓名"].ToString();string sEmail = row["邮箱"].ToString();if (listFailedName != null && listFailedName.Count > 0 && listFailedName.Contains(sName)) ;else{string docContent = ReadModehtml(wordFilePath);if (!string.IsNullOrEmpty(docContent)){emailSubject = "测试测试主题";emailBody = docContent;}if (SendEmail(emailSubject, emailBody, sName, sEmail)){textBox5.Text += System.Environment.NewLine + string.Format("邮件 [{0}] 发送成功!", sName);}else{textBox5.Text += System.Environment.NewLine + string.Format("<@.@>邮件 [{0}] 发送失败了,不过,没关系等最后结束后重试!", sName);listsendFailedName.Add(sName);}}}if (listsendFailedName != null && listsendFailedName.Count > 0){foreach (var item in listsendFailedName){LogHelper.CreateLog(string.Format("邮件 [{0}] 发送失败了,不过,没关系等最后结束后重试!", item));}}textBox5.Text += System.Environment.NewLine + string.Format("邮件发送已结束!");if ((listFailedName != null && listFailedName.Count > 0) || (listsendFailedName!=null && listsendFailedName.Count>0))textBox5.Text += System.Environment.NewLine + string.Format("<@.@>邮件发送过程中出现异常,查看异常日志联系Martin大叔");listFailedName = null;listsendFailedName = null;}private void button5_Click(object sender, EventArgs e){OpenFileDialog openFile = new OpenFileDialog();openFile.Filter = "Html(*.html)|*.html|Htm(*.htm)|*.htm";openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);openFile.Multiselect = false;if (openFile.ShowDialog() == DialogResult.Cancel)return;wordFilePath = openFile.FileName;string fileType = System.IO.Path.GetExtension(wordFilePath);if (string.IsNullOrEmpty(fileType))return;this.textBox6.Text = wordFilePath;}private void radioButton2_CheckedChanged(object sender, EventArgs e){if (radioButton2.Checked)isHaveImg = label5.Visible = true;elseisHaveImg = label5.Visible = false;}
参考资料:
http://www.cnblogs.com/Joetao/articles/2933941.html
http://q.cnblogs.com/q/32301/
https://social.msdn.microsoft.com/Forums/office/en-US/b81ff74a-9c22-403c-b62c-5e1aba5106bd/microsoftofficeinteropwordapplicationdocumentsopen-issue-with-filename?forum=worddev
http://www.cnblogs.com/cfcool/archive/2010/02/05/1664145.html
http://bbs.csdn.net/topics/100094640
http://blog.csdn.net/kkkkkxiaofei/article/details/7941239
http://blog.csdn.net/tolearner/article/details/6102023
http://www.jb51.net/article/63486.htm
http://www.jb51.net/article/55465.htm
程序下载