Отправка писем из Outlook с помощью VBA макроса или PowerShell

Возникла задача организации рассылки писем по списку e-mail пользователей в Excel. В каждом письме будут содержаться некоторые данные, индивидуальные для каждого пользователя, и приложен персональный файл. В этой статье, мы рассмотрим, как использовать Outlook для автоматической рассылки писем по списку из Excel с помощью VBA макроса или PowerShell

Допустим, у нас есть Excel файл, содержащий следующие столбцы:

Email пользователя | ФИО | Время последней смены пароля | Статус учетной записи

В рамках моей задачи нужно каждому пользователю из списка отправить письмо вида:

VBA макрос в Excel для рассылки писем из Outlook

Сначала рассмотрим небольшой макрос отправки писем на языке VBA (Visual Basic for Applications), который можно создать прямо в документе Excel.

Создайте новый макрос: вкладка Вид -> Макросы. Укажите имя макроса send_email и нажмите кнопку Создать:

В открывшемся редакторе VBA вставьте следующий код (я снабдил его всеми необходимыми комментариями). Для автоматизации отправки писем я воспользуюсь функцией CreateObject(«Outlook.Application»), позволяющей создать и использовать в скрипте объект приложения Outlook.

Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' тема письма
strSubj = "Статус учетной записи в домене winitpro.ru"
On Error GoTo dbg
' создаем новый объект типа Outlook
Set olApp = CreateObject("Outlook.Application")
For iCounter = 2 To WorksheetFunction.CountA(Columns(1))
' создаем новый элемент (письмо) в Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 1).Value
FullUsername = Cells(iCounter, 2).Value


Данный Excel файл нужно сохранить с расширением xlsm (формат книги Excel с поддержкой макросов). Для запуска рассылки выберите созданную процедуру (макрос) и нажмите кнопку выполнить.

Макрос последовательно перебирает все строки на листе Excel, формирует и отправляет по одному письму на каждый Email из списка. Отправленные письма сохраняются в папку Sent Items Outlook.

Отправить письмо из Outlook с помощью PowerShell

В PowerShell можно использовать командлет Send-MailMessage для отправки писем, однако он требует выполнения аутентификации на почтовом сервере перед отправкой и не поддерживает современные методы проверки подлинности, такие как в том числе OAuth и Microsoft Modern Authentication. Поэтому, если на вашем компьютер есть настроенный профиль Outlook, гораздо проще использовать его для отправки писем.

Ниже приведен пример скрипта PowerShell, который получает данные из Excel файла и использует Outlook для отправки письма для каждого пользователя:

# открыть Excel файл
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\user_list.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("Sheet1")
# Получаем количество заполненных строк в xlsx файле
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Перебираем все строки в столбце 1, начиная со второй строки (в этих ячейках указано доменное имя пользователя)
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
# формируем тело письма
$strSubj = "Статус учетной записи в домене winitpro.ru"
$strBody = "Уважаемый " + $FullUsername
$strBody = $strBody + " `r`n Ваша учетная запись в домене winitpro.ru " + $Status
$strBody = $strBody + "`r`n Время последней смены пароля: " +  $pwdchange
$strfile="C:\ps\" + $useremail + ".txt"
# предполагаем, что Outlook открыт, если нет нужно запустить его командой $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
# добавить вложение (если нужно)
$email.Attachments.add($strfile)
#отправить письмо
$email.Send()
}
$ExcelWorkBook.close($true)

Данный PowerShell скрипт предполагает, что на компьютере запущен Outlook. Для каждого email адреса из XLSX файла скрипт генерирует тему и текст письма и прикрепляет файл. Затем выполняется отправка письма.