使用 Excel VBA 宏或 PowerShell 发送 Outlook 电子邮件

有一项任务是根据 Excel 电子表格中的用户电子邮件列表来组织邮件。每封电子邮件都应包含特定于每个用户的一些数据，并且还应附加个人文件。在本文中，我们将了解如何使用 Outlook 配置文件使用 VBA 宏或 PowerShell 脚本自动将电子邮件从 Excel 文件发送到收件人列表。

重要。必须在您的计算机上为两种发送电子邮件的方法配置 Outlook 邮件配置文件。该邮箱（以及该电子邮件地址）将用于发送消息

假设您有一个包含以下列的 Excel 文件：

Email | Full Name | Last Password Change Date | Account status

我的任务是使用此模板向 Excel 列表中的每个人发送电子邮件：

主题：您在 a-d.site 域中的帐户状态
正文：亲爱的 %FullUsername%，
您在 a-d.site 域中的帐户处于 %status% 状态
最后一次更改密码的日期和时间是 %pwdchange%

Excel VBA 宏通过 Outlook 发送电子邮件

这是一个小型 VBA（Visual Basic for Applications）邮件宏，可以直接在 Excel 文档中创建。

创建一个新宏：查看 -> 宏。指定宏的名称 (send_email) 并单击创建：

将以下代码复制并粘贴到出现的 VBA 编辑器中（我已向其中添加了所有必要的注释）。为了自动发送电子邮件，我将使用

CreateObject (“Outlook.Application”)

函数，它允许在 VBA 脚本中创建和使用 Outlook 对象。

Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' Subject
strSubj = "Your account status on a-d.site domain"
On Error GoTo dbg
' Create a new Outlook object
Set olApp = CreateObject("Outlook.Application")
For iCounter = 2 To WorksheetFunction.CountA(Columns(1))
' Create a new item (email) in Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 1).Value
FullUsername = Cells(iCounter, 2).Value
Status = Cells(iCounter, 4).Value
pwdchange = Cells(iCounter, 3).Value
'Make the body of an email
strBody = "Dear " & FullUsername & vbCrLf
strBody = strBody & " Your account in a-d.site domain is in" & Status & “ state” & vbCrLf
strBody = strBody & "The date and time of the last password change is" & pwdchange & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 1
' 1 - text format of an email, 2 -  HTML format
olMailItm.Body = strBody
'Add an attachment (filename format is [email protected]). Comment out the following line if you do not need the attachments
olMailItm.Attachments.Add ("C:\ps\" & useremail & ".txt")
olMailItm.Send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description
End Sub

将此 Excel 文件另存为 .xlsm（一种支持宏的 Excel 工作簿格式）。要发送电子邮件，请选择您创建的过程（宏），然后单击运行。

VBA 宏循环访问 Excel 电子表格中的所有行，生成一条消息并将其发送给列表中的每个收件人。已发送的电子邮件存储在 Outlook 的已发送邮件文件夹中。

如果您想要代表其他用户或共享邮箱（如果您使用的是 Exchange）发送电子邮件，则必须授予 SendAs/Sent 代表权限，并将以下代码添加到脚本中（在

olMailItm.Send

）。

olMailItm.SentOnBehalfOfName = "[email protected]"

使用 PowerShell 从 Outlook 发送电子邮件

在 PowerShell 中，您可以使用 Send-MailMessage cmdlet 发送电子邮件。但是，它要求您对邮件服务器进行身份验证，并且不支持现代身份验证方法，例如 OAuth 和 Microsoft 现代身份验证。因此，如果您的计算机上配置了 Outlook 配置文件，则发送电子邮件会更加容易。

以下是一个 PowerShell 脚本示例，该脚本从 Excel 文件读取数据并使用 Outlook 配置文件向每个用户发送电子邮件：

# open the Excel file
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\user_list.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Get the number of filled rows in an xlsx file
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all the rows in column 1, starting from the second row (these cells contain the usernames and e-mails).
for($i=2;$i -le $rowcount;$i++){
$useremail = $ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
$FullUsername =  $ExcelWorkSheet.Columns.Item(2).Rows.Item($i).Text
$Status =  $ExcelWorkSheet.Columns.Item(4).Rows.Item($i).Text
$pwdchange = $ExcelWorkSheet.Columns.Item(3).Rows.Item($i).Text
# Generate message body text
$strSubj = " Your account status on a-d.site domain "
$strBody = "Dear " + $FullUsername
$strBody = $strBody + " `r`n Your account in a-d.site domain is in " + $Status
$strBody = $strBody + "`r`n The date and time of the last password change is : " +  $pwdchange
$strfile="C:\ps\" + $useremail + ".txt"
# We assume that Outlook is running, if it is not you will need to start it with the command $outlook = new-object -comobject outlook.application
$outlook = [Runtime.InteropServices.Marshal]::GetActiveObject("Outlook.Application")
$email = $outlook.CreateItem(0)
$email.To = $useremail
$email.Subject = $strSubj
$email.Body =  $strBody
# Attach a file (if necessary)
$email.Attachments.add($strfile)
#send the e-mailmessage
$email.Send()
}
$ExcelWorkBook.close($true)

此 PowerShell 脚本假定 Outlook 正在您的计算机上运行。该脚本为 XLSX 文件中的每个收件人 SMTP 地址生成电子邮件的主题和正文，并附加该文件。然后发送电子邮件。