Как написать VBA-макрос который отправляет email прямо из Excel — настройка Outlook, готовый код, рассылка по списку получателей и кнопка запуска за один клик.
Макрос отправляет письма прямо из Excel — без копирования и вставки
Представьте: таблица с пятьюдесятью клиентами. Каждому нужно отправить письмо с его данными — суммой заказа, датой доставки, номером счёта. Вручную это выглядит так: открыть Outlook, создать письмо, переключиться в Excel, скопировать имя, вставить в письмо, скопировать сумму, вставить, отправить. Умножить на пятьдесят.
Час работы. Минимум пять ошибок — не тот получатель, не та сумма, забыли вложение.
Макрос делает то же самое за тридцать секунд. Читает таблицу строку за строкой, формирует письмо с данными конкретного клиента и отправляет через Outlook. Без вашего участия.
В этой статье напишем такой макрос с нуля, разберём как он работает и добавим защиту от случайной рассылки.
Что нужно для работы макроса
Макрос отправки писем из Excel работает через Outlook. Это значит два обязательных условия.
Outlook должен быть установлен и настроен. Макрос использует Outlook как почтовый клиент — через него фактически отправляются письма. Если Outlook не установлен или не подключён к почтовому ящику — макрос не сработает.
Макросы должны быть включены. Стандартное требование для любого VBA-кода. Файл → Параметры → Центр управления безопасностью → Параметры макросов → Включить все макросы. Файл сохраняется в формате .xlsm.
Если в вашей организации используется другой почтовый клиент — Gmail, Mail.ru, Яндекс Почта — отправка через VBA возможна, но требует другого подхода: через протокол SMTP напрямую. Этот способ сложнее и требует хранения пароля в коде, поэтому для рабочих задач Outlook — оптимальный вариант.
Структура таблицы для рассылки
Прежде чем писать код, подготовьте таблицу. Макрос будет читать данные построчно, поэтому структура должна быть чёткой.
Минимальный набор столбцов для рассылки:
Столбец A — имя получателя
Столбец B — email получателя
Столбец C — тема письма
Столбец D — текст письма или ключевые данные для подстановки
Первая строка — заголовки. Данные начиная со второй строки. Никаких объединённых ячеек, никаких пустых строк внутри таблицы.
Если тема и текст письма одинаковые для всех — столбцы C и D не нужны, пропишем их прямо в коде.
Простая версия — одно письмо для проверки
Начнём с самого простого варианта. Макрос отправляет одно тестовое письмо — чтобы убедиться что всё работает до запуска рассылки.
Откройте редактор VBA через Alt+F11. Вставьте новый модуль: правая кнопка → «Вставить» → «Модуль». Введите код:
Sub ОтправитьТестовоеПисьмо()
Dim olApp As Object*
Dim olMail As Object*
Set olApp = CreateObject("Outlook.Application")*
Set olMail = olApp.CreateItem(0)*
With olMail*
.To = "test@example.com" ' замените на свой адрес*
.Subject = "Тестовое письмо из Excel"*
.Body = "Это письмо отправлено макросом VBA из Excel." & vbNewLine & _*
"Если вы получили это сообщение — всё работает."*
.Display ' показать письмо перед отправкой*
' .Send ' раскомментируйте для автоматической отправки*
End With*
Set olMail = Nothing*
Set olApp = Nothing*
MsgBox "Письмо подготовлено.", vbInformation*
End Sub
Обратите внимание на строку .Display — она открывает письмо в Outlook для предварительного просмотра, но не отправляет. Это безопасный режим для проверки. Когда убедитесь что всё правильно — замените .Display на .Send для автоматической отправки.
Что происходит внутри кода
Set olApp = CreateObject("Outlook.Application") — создаём объект приложения Outlook. Это подключение к Outlook из VBA. Если Outlook не запущен — он запустится автоматически в фоне.
Set olMail = olApp.CreateItem(0) — создаём новый элемент почты. Число 0 означает тип «Письмо». Другие типы: 1 — встреча, 2 — контакт, 3 — задача.
With olMail ... End With — блок который позволяет обращаться к свойствам объекта без повторения его имени. Всё что написано внутри блока применяется к olMail.
.To — адрес получателя. Несколько получателей разделяются точкой с запятой: "адрес1@mail.ru; адрес2@mail.ru".
.Subject — тема письма.
.Body — текст письма. vbNewLine — перенос строки внутри текста.
.Display — открыть письмо в Outlook. .Send — отправить сразу без просмотра.
Set olMail = Nothing и Set olApp = Nothing — освобождаем память после работы с объектами. Хорошая практика которая предотвращает утечки памяти при многократном запуске макроса.
Рассылка по списку — основная версия
Теперь главный макрос. Он читает таблицу, формирует персональное письмо для каждого получателя и отправляет.
Sub РассылкаПоСписку()
Dim olApp As Object*
Dim olMail As Object*
Dim ws As Worksheet*
Dim lastRow As Long*
Dim i As Long*
Dim имя As String*
Dim email As String*
Dim сумма As String*
Dim дата As String*
Dim счёт As String*
Dim тело As String*
Dim отправлено As Long*
' Подтверждение перед запуском*
If MsgBox("Запустить рассылку?", vbYesNo + vbQuestion) = vbNo Then Exit Sub*
Set ws = ActiveSheet*
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row*
Set olApp = CreateObject("Outlook.Application")*
отправлено = 0*
Application.ScreenUpdating = False*
For i = 2 To lastRow*
' Читаем данные из таблицы*
имя = ws.Cells(i, 1).Value*
email = ws.Cells(i, 2).Value*
сумма = ws.Cells(i, 3).Value*
дата = ws.Cells(i, 4).Value*
счёт = ws.Cells(i, 5).Value*
' Пропускаем строки с пустым email*
If email = "" Then GoTo СледующаяСтрока*
' Формируем текст письма
тело = "Уважаемый(ая) " & имя & "," & vbNewLine & vbNewLine & _*
"Сообщаем вам информацию по вашему заказу:" & vbNewLine & _*
"Номер счёта: " & счёт & vbNewLine & _*
"Сумма к оплате: " & сумма & " руб." & vbNewLine & _*
"Дата доставки: " & дата & vbNewLine & vbNewLine & _*
"С уважением," & vbNewLine & _*
"Отдел продаж"*
' Создаём и отправляем письмо*
Set olMail = olApp.CreateItem(0)*
With olMail
.To = email*
.Subject = "Информация по заказу №" & счёт
.Body = тело*
.Send*
End With*
' Отмечаем в таблице что письмо отправлено
ws.Cells(i, 6).Value = "Отправлено"*
ws.Cells(i, 6).Interior.Color = RGB(200, 230, 200)*
отправлено = отправлено + 1*
СледующаяСтрока:
Set olMail = Nothing*
Next i*
Application.ScreenUpdating = True*
Set olApp = Nothing*
MsgBox "Рассылка завершена. Отправлено писем: " & отправлено, vbInformation*
End Sub
После запуска в шестом столбце таблицы появится отметка «Отправлено» с зелёной заливкой для каждой строки где письмо ушло успешно. Это удобно если рассылка прервалась — видно с какой строки продолжать.
Версия с вложением
Если к каждому письму нужно прикрепить файл — добавьте одну строку внутри блока With olMail:
.Attachments.Add "C:\Документы\Счёт_" & счёт & ".pdf"
Путь к файлу формируется динамически — к фиксированной части пути добавляется номер счёта из таблицы. Это работает если файлы названы по шаблону и лежат в одной папке.
Если у всех получателей одно и то же вложение — просто укажите полный путь к файлу без динамической части:
.Attachments.Add "C:\Документы\Прайс.pdf"
Версия с HTML-форматированием
Текстовые письма выглядят просто. Если нужно красивое письмо с жирным текстом, цветами и таблицей — используйте формат HTML.
Замените .Body на .HTMLBody:
With olMail
.To = email*
.Subject = "Информация по заказу №" & счёт*
.HTMLBody = "<html><body>" & _*
"<p>Уважаемый(ая) <b>" & имя & "</b>,</p>" & _*
"<p>Информация по вашему заказу:</p>" & _*
"<table border='1' cellpadding='5'>" & _*
"<tr><td>Номер счёта</td><td><b>" & счёт & "</b></td></tr>" & _*
"<tr><td>Сумма</td><td><b>" & сумма & " руб.</b></td></tr>" & _*
"<tr><td>Дата доставки</td><td>" & дата & "</td></tr>" & _
"</table>" & _*
"<p>С уважением,<br>Отдел продаж</p>" & _
"</body></html>"*
.Send*
End With
HTML в письме поддерживается всеми современными почтовыми клиентами. Таблица будет отображаться как обычная таблица, жирный текст — жирным.
Защита от случайной рассылки
Главный риск при работе с рассылкой — случайно запустить и отправить письма не тем людям или с неправильными данными. Несколько правил которые защищают от этого.
Всегда начинайте с .Display вместо .Send. Запустите макрос в режиме просмотра, проверьте первые два-три письма вручную. Только после этого переключайтесь на .Send.
Добавьте подтверждение перед запуском. В коде уже есть строка с MsgBox и вопросом — не удаляйте её. Одно нажатие «Нет» останавливает рассылку.
Тестируйте на своём адресе. Перед боевой рассылкой замените все email в таблице на свой адрес, запустите макрос, проверьте что письма приходят правильно. Потом верните настоящие адреса.
Делайте копию файла. Макрос записывает статус отправки прямо в таблицу. Если что-то пойдёт не так — оригинал без пометок останется.
Частые ошибки
Ошибка «ActiveX component can't create object». Outlook не установлен или не настроен. Проверьте что Outlook запускается и подключён к почтовому ящику.
Письма уходят но не доходят. Проверьте папку «Отправленные» в Outlook — письма там должны появиться. Если появились — проблема на стороне получателя или спам-фильтра.
Макрос останавливается на середине рассылки. Чаще всего причина — пустая ячейка в столбце email или некорректный адрес. В коде есть проверка If email = "" Then GoTo СледующаяСтрока — она пропускает пустые строки. Если адрес есть но некорректный — добавьте проверку на наличие символа «@»: If InStr(email, "@") = 0 Then GoTo СледующаяСтрока.
Outlook запрашивает подтверждение каждого письма. Это защита безопасности Outlook от автоматической рассылки. Решение — в настройках Outlook добавить Excel в список доверенных приложений, или использовать режим Display с ручной отправкой каждого письма.
Связанные материалы
Автоматическая рассылка — логичное продолжение работы с данными в Excel. Если хотите сначала собрать данные из нескольких листов в одну таблицу — читайте статью Кнопка, которая собирает данные из всех листов Excel в одну таблицу. А как автоматически найти ошибки в таблице перед рассылкой — в материале Найдите ошибки в Excel одной кнопкой — VBA сам подсветит проблемные ячейки.
Подписывайтесь на Telegram — там короткие гайды, готовые файлы и разборы реальных задач по Excel и VBA: t.me/macroschannel
Итог
Макрос для отправки писем из Excel экономит от получаса до нескольких часов при каждой рассылке. Он не ошибается в адресах, подставляет данные точно из таблицы и отмечает каждое отправленное письмо.
Четыре версии кода в статье закрывают разные сценарии: тестовое письмо, рассылка по списку, письма с вложением, HTML-форматирование. Начните с тестового письма — убедитесь что Outlook настроен правильно. Потом переходите к рассылке.
Главное правило — сначала .Display, потом .Send. Отправленное письмо не вернёшь.