利用 vba 批量发送邮件

使用 vba 批量发送邮件, 以及公邮(on behalf of nmae)的使用.

缘起

小伙伴有群发邮件的需求, 但是他又不太会编程, 勉强写两行 vba.

历程

作为一个 golang 开发者第一时间想到了支持 smtp 协议的库, 比如go-simple-mail, 然后用 go 几分钟写了个 demo 出来, 但是操作形式不太友好, 而且难以解决登录的问题, 普通用户填写账号密码就行, 但是公司用户可能有二次认证, 很明显这样不太行, 要找一个支持二次认证的方案。

然后想 outlook 有没有提供相关 api, 搜了一下, 确实有, 不过二次认证需要 app key, 很明显这超出了普通员工的权限, 这条路也不好走.

二次认证也是一个麻烦的交互, 所以退而求其次, 我们也可以用 excel 的 vba, 一方面解决二次认证问题, 另一方面他代码不熟但是对 excel 也比较熟. 少了理解配置文件的成本.

如何发一个简单的邮件?

vba 省去了登录这个步骤(只要 outlook 登录即可), 这里只要指定发邮件必须的信息即可:

' 创建 Outlook 应用实例
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0) ' 0 代表邮件项

    ' 配置邮件
With OutlookMail
    .To = recipient
    .subject = subject
    .cc = cc ' 添加多个抄送人，地址用分号分隔
    .HTMLBody = xbody ' 设置 HTML 格式的邮件正文
    
    ' 添加图片附件 固定格式勿动
    If img001 <> "" Then
        Set attachment = .Attachments.Add(img001, 2, , "Attachment")
        attachment.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001F", "001.jpg" ' 设置 Content-ID
    End If
.Send ' 发送邮件

当然, 如果你要添加 html 和附件, 切记设置 Content-ID.

如何自定义邮件内容?

还有一个问题就是不同的发送人可能收到的邮件内容是不一样的, 这个时候只要把每个邮件不同的内容写入 excel 即可:

那如何取值呢？

• 首先是取 sheet

Set ws = ThisWorkbook.Sheets("Sheet2") ' 替换为你的工作表名称

• 然后取 cell

ws.Cells(i, 1).Value

• 如何按行循环？

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row // 注意这是真实行数
For i = 2 To lastRow
    ' do something
End

到这里基本就差不多了, 细心的小伙伴已经可以批量操控邮件了, 但是我的小伙伴还有一个小问题耗费了最多的时间, 如何使用公邮？

公邮 (只登录一个账号的同学不用关心)

大多数人可能有多个邮箱这个时候如何指定发件人呢？

' 查找匹配的账户
For Each Account In OutlookApp.Session.Accounts
    Debug.Print "Original String: " & Account.SmtpAddress
    If LCase(Account.SmtpAddress) = LCase(from) Then
        Set OutlookMail.SendUsingAccount = Account
        Exit For
    End If
Next Account

这里已经能解决大部分人的问题了, 不过如果你要使用公邮的话, 很遗憾, 这样是找不到公邮邮箱地址的, 这也是耗费我时间最久的问题, 公邮的正确姿势如下:

OutlookMail.SentOnBehalfOfName = behalfOfName

结尾

搜索了半天公邮相关, 发现并没有什么相关结果, 当时就想到了是不是不叫公邮, 这个英文应该是behalf of name.

坑.