系统:Windows 7
软件:Excel 2010
- 本系列讲讲如何使用VBA进行邮件自动分发
- 本文的方法借助于Outlook软件实现
Part 1:示例
- 有两封邮件需要分发,如下图所示
- 使用VBA实现邮件自动分发功能
邮件清单
邮件分发结果1
邮件分发结果2
Part 2:代码
Sub 邮件预警()
Set sht = ThisWorkbook.Worksheets("示例")
maxRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To maxRow Step 1
Set outlookApp = CreateObject("Outlook.Application")
Set mail = outlookApp.CreateItem(0)
emailReceiver = sht.Cells(i, "A")
eSubject = sht.Cells(i, "B")
eContent = sht.Cells(i, "C")
mail.To = emailReceiver
mail.Subject = eSubject
mail.Body = eContent
mail.Send
Set outlookApp = Nothing
Set mail = Nothing
Next
MsgBox "已分发" & maxRow - 1 & "封邮件"
End Sub
代码截图
Part 3:部分代码解读
Set outlookApp = CreateObject("Outlook.Application")
,这句话的作用是引用outlook工具包mail = outlookApp.CreateItem(0)
,创建一封邮件mail.To = emailReceiver
,设置邮件收件人mail.Subject = eSubject
,设置邮件主题mail.Body = eContent
,设置邮件内容mail.Send
,发送
下节精彩:如果想在邮件中带上附件咋整?
- 更多学习交流,可加小编微信号
learningBin
更多精彩,请关注微信公众号
扫描二维码,关注本公众号