Как отправлять письма из Excel через Gmail с помощью VBA

Зачем отправлять почту из Excel

Отправка писем из Excel полезна, когда вы хотите автоматизировать уведомления, рассылки или отчёты. Примеры случаев применения:

• Еженедельные обновления, когда сотрудники правят таблицу. Вы получаете уведомление автоматически.
• Рассылка однотипного письма списку контактов, хранящихся в таблице.
• Автоматическое отправление отчётов (файлы Excel/PDF) получателям без ручного вмешательства.

Коротко: это экономит время и снижает количество рутинных задач.

Важно: в статье показан способ через Gmail и CDO — он универсален и может быть адаптирован под другие SMTP-серверы.

Что такое CDO и почему он удобен

CDO (Collaboration Data Objects) — это компонент Windows для работы с сообщениями. Он доступен через VBA в Office и позволяет формировать и отправлять электронные письма по SMTP без необходимости устанавливать дополнительные сторонние библиотеки.

Короткое определение: CDO — библиотека для отправки писем по SMTP из приложений Windows.

TL;DR технически — ключевые шаги

1. Включите 2‑шаговую проверку и создайте пароль приложения в Gmail. Запишите 16‑значный пароль приложения.
2. В Excel сохраните книгу как .xlsm (макросы разрешены).
3. На ленте включите вкладку “Разработчик” и добавьте командную кнопку или вызов макроса.
4. В редакторе VBA подключите ссылку на “Microsoft CDO for Windows 2000 Library”.
5. Вставьте и адаптируйте макрос, указав From/To, smtpserver, порт, логин и пароль приложения.
6. Протестируйте макрос (F5). При успехе — автоматизируйте запуск (Workbook_Open или Планировщик заданий).

Шаг 1: Подготовьте аккаунт Gmail

Gmail запрещает прямой доступ устаревшим приложениям. Для работы через SMTP вам нужно:

1. Включить двухэтапную аутентификацию в настройках Google (Безопасность → Вход в аккаунт Google → Двухэтапная аутентификация).
2. Перейти в “Пароли приложений” (App passwords). В разделе “Выберите приложение” выберите “Почта”, в “Устройство” — “Windows‑компьютер“ и нажмите “Создать”.
3. Скопируйте 16‑символьный пароль приложения — он понадобится в макросе вместо обычного пароля.

Примечание: если опция “Пароли приложений” недоступна, возможно, ваш аккаунт управляется организацией или вы подключены к расширенной защите Google.

Важно: используйте пароль приложения — это безопаснее, чем хранить основной пароль Google в макросе.

Шаг 2: Создайте макрос VBA в Excel

Совет: перед началом сохраните книгу в формате “Книга с поддержкой макросов” (.xlsm).

1. Если вкладка “Разработчик” скрыта, включите её: Файл → Параметры → Настроить ленту → отметьте “Разработчик” → ОК.
2. На вкладке Разработчик: Вставка → Командная кнопка (элемент ActiveX) — разместите на листе.
3. Выберите командную кнопку и нажмите “Макросы” или в режиме разработки — нажмите “Просмотр кода”, чтобы открыть редактор VBA.

В редакторе VBA:

1. Откройте Tools → References.
2. Найдите и отметьте “Microsoft CDO for Windows 2000 Library” и нажмите ОК.

Запомните имя функции, в которую вы вставляете скрипт — оно понадобится при привязке к кнопке.

Шаг 3: Настройте макрос (вставьте код)

Ниже приведён полный пример кода. Вставьте его в окно модуля Module1 (Code) в редакторе VBA. Подчеркну поля, которые нужно изменить: .From, .To, .Subject, .TextBody, .AddAttachment (если нужно), а также параметры в блоке fields: smtpserver, smtpserverport, sendusername, sendpassword.

        `Sub Send_Emails()  
    Dim NewMail As CDO.Message  
    Dim mailConfig As CDO.Configuration  
    Dim fields As Variant  
    Dim msConfigURL As String  
    On Error GoTo Err:  
  
    'early binding  
    Set NewMail = New CDO.Message  
    Set mailConfig = New CDO.Configuration  
  
    'load all default configurations  
    mailConfig.Load -1  
  
    Set fields = mailConfig.fields  
  
    'Set All Email Properties  
    With NewMail  
        .From = "username@gmail.com"  
        .To = "username@gmail.com"  
        .CC = ""  
        .BCC = ""  
        .Subject = "Send Email From an Excel Spreadsheet"  
        .TextBody = "This is the body of your email. And here is some added data:" & Str(Sheet1.Cells(2, 1))  
        .Addattachment "c:\data\email.xlsx"             'Optional file attachment; remove if not needed.  
        .Addattachment "c:\data\email.pdf"             'Duplicate the line for a second attachment.  
    End With  
  
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"  
  
    With fields  
        .Item(msConfigURL & "/smtpusessl") = True             'Enable SSL Authentication  
        .Item(msConfigURL & "/smtpauthenticate") = 1          'SMTP authentication Enabled  
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details  
        .Item(msConfigURL & "/smtpserverport") = 465          'Set the SMTP port Details  
        .Item(msConfigURL & "/sendusing") = 2                 'Send using default setting  
        .Item(msConfigURL & "/sendusername") = "username@gmail.com" 'Your gmail address  
        .Item(msConfigURL & "/sendpassword") = "password" 'Your password or App Password  
        .Update                                               'Update the configuration fields  
    End With  
    NewMail.Configuration = mailConfig  
    NewMail.Send  
     
    MsgBox "Your email has been sent", vbInformation  
  
  Exit_Err:  
    'Release object memory  
    Set NewMail = Nothing  
    Set mailConfig = Nothing  
    End  
  
  Err:  
    Select Case Err.Number  
    Case -2147220973  'Could be because of Internet Connection  
        MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description  
    Case -2147220975  'Incorrect credentials User ID or password  
        MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description  
    Case Else   'Report other errors  
        MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description  
    End Select  
  
    Resume Exit_Err  
  
  End Sub`
    

Важно: в коде выше обратите внимание на экранирование пути файлов (двойной обратный слэш в путях Windows внутри строки).

Какие части кода менять

• With NewMail: указывайте .From, .To, .Subject и .TextBody. В .TextBody можно вставлять значения из ячеек через конкатенацию (&).
• With fields: указывайте smtpserver (smtp.gmail.com), smtpserverport (обычно 465 или 587 с другим флагом по SSL/TLS), sendusername (ваш адрес), sendpassword (пароль приложения).

Шаг 4: Тестируйте макрос

В редакторе VBA: Run → Run Sub/UserForm или нажмите F5. При успешной отправке появится сообщение об успехе.

Если появится ошибка “The transport failed to connect to the server”, проверьте:

• Наличие интернет‑соединения.
• Правильность smtpserver и smtpserverport.
• Корректность логина и пароля приложения.

Шаг 5: Привяжите кнопку к макросу

1. Откройте код листа (двойной щелчок на Sheet1 в дереве проекта VBA).
2. Вверху выберите элемент управления (например, CommandButton1) и событие Click.
3. В теле обработчика вызовите вашу процедуру, например:

Private Sub CommandButton1_Click()
    Call Send_Emails
End Sub

После этого при нажатии на кнопку на листе макрос выполнится и отправит письмо.

Автоматизация: запуск при открытии книги и через Планировщик заданий

Если нужно полностью автоматизировать отправку, выполните два шага:

1. Вставьте код в обработчик Workbook_Open:

Private Sub Workbook_Open()
    Call Send_Emails
End Sub

Это запустит макрос при открытии книги.

2. В Планировщике заданий Windows (Task Scheduler) создайте задачу, которая будет запускать Excel с аргументом — путь к вашей книге. В параметре “Действие” выберите “Запустить программу”; в поле “Программа/скрипт” укажите путь к Excel (например, C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE); в поле “Добавить аргументы” укажите путь к книге .xlsm.

Совет: сначала создайте задачу на ближайшее время и протестируйте, затем скорректируйте расписание.

Примечание по безопасности: возможно потребуется в Центре управления безопасностью (Trust Center) разрешить выполнение макросов или установить уровень безопасности, чтобы макрос запускался без дополнительных подтверждений: Файл → Параметры → Центр управления безопасностью → Параметры центра управления безопасностью → Настройки макросов.

Безопасность и конфиденциальность

• Хранение пароля: никогда не храните основной пароль Google в открытом виде в файле. Используйте пароль приложения и, по возможности, храните его вне книги или в зашифрованном виде.
• Доступ к книге: ограничьте доступ к файлу .xlsm, где лежит пароль приложения.
• Логирование: не логируйте пароли в открытом виде.
• GDPR/персональные данные: если вы отправляете персональные данные, гарантируйте соответствие требованиям конфиденциальности и получите согласие при необходимости.

Important: пароли приложений можно отозвать в любое время в настройках аккаунта Google.

Альтернативные подходы

1. Outlook Object Model (если у вас установлен Outlook): используйте объектную модель Outlook из VBA. Это проще для локальных сред, где Outlook уже настроен; не требует SMTP‑пароля в коде.
2. Power Automate (ранее Microsoft Flow): автоматизация без макросов; интегрируется с Excel Online и Outlook/Gmail через коннекторы.
3. Использовать внешние скрипты (Python, PowerShell) запускаемые через Планировщик заданий. Подходит для более сложной логики и безопасности (секреты хранятся отдельно).
4. Использовать корпоративный SMTP с авторизацией и ключами — более безопасно для массовых рассылок в организациях.

Когда альтернативы лучше:

• Если у вас централизованный Exchange/Office 365 — используйте Graph API или Outlook Object Model.
• Для облачных автоматизаций — Power Automate.

Методология внедрения (мини‑руководство для проекта)

1. Прототип: реализуйте макрос в тестовой книге, проверяйте отправку на личный почтовый ящик.
2. Безопасность: создайте отдельный аккаунт/пароль приложения для автоматизации. Ограничьте доступ.
3. Тестирование: прогоните сценарии (см. ниже раздел “Критерии приёмки”).
4. Автоматизация: внедрите Workbook_Open + Планировщик заданий.
5. Мониторинг: добавьте уведомления об ошибках (логирование, оповещения при сбоях).
6. Откат: держите резервную копию книги без макросов.

Чек‑листы по ролям

Разработчик:

• Код не содержит основных паролей Google.
• Использован пароль приложения.
• Обработаны возможные ошибки (сетевые, аутентификация).
• Добавлено логирование ошибок.

Администратор ИТ:

• Утверждён список аккаунтов, которые могут использовать автоматизацию.
• Настроены права доступа к папке с .xlsm.
• Планировщик заданий проверен и запущен от нужного пользователя.

Пользователь/владелец процесса:

• Подтверждена корректность тела письма и списка получателей.
• Протестирована отправка и формат вложений.
• Подписан план восстановления при ошибке.

Критерии приёмки

1. Письмо отправляется и приходит на указанный адрес за менее чем 2 минуты после запуска макроса.
2. Тело письма содержит актуальные данные из ячеек Excel.
3. При ошибке пользователь получает информативное сообщение (MsgBox или запись в лог).
4. При автоматическом запуске через Планировщик заданий макрос выполняется без ручного вмешательства (если доверие к макросам настроено).

Тестовые сценарии и случаи приёма

• TC1: Отправка письма на один локальный адрес — ожидаемый результат: письмо в ящике получателя.
• TC2: Отправка с вложением Excel и PDF — ожидаемый результат: вложения приходят целыми.
• TC3: Неправильный пароль — ожидаемый результат: информативная ошибка и отсутствие отправки.
• TC4: Нет интернета — ожидаемый результат: обработка ошибки и повторная попытка по расписанию (опционально).

Частые ошибки и способы устранения

• “The transport failed to connect to the server”: проверьте smtpserver, порт, SSL/TSL, доступ в интернет.
• Ошибка аутентификации: проверьте логин и пароль приложения; проверьте, не отозван ли пароль приложения в аккаунте Google.
• Макрос не запускается при открытии книги: убедитесь, что параметры безопасности макросов позволяют запуск и что макрос находится в ThisWorkbook → Workbook_Open.
• Не удаётся подключить ссылку CDO: проверьте версию Office и наличие библиотеки; альтернативно используйте позднюю привязку (CreateObject).

Пример замены ранней привязки на позднюю (если нет ссылки в References):

Dim NewMail As Object
Dim mailConfig As Object
Set NewMail = CreateObject("CDO.Message")
Set mailConfig = CreateObject("CDO.Configuration")

Практические подсказки и эвристики

• Храните только пароль приложения в среде выполнения, не в исходной ветке контроля версий.
• Если массовая рассылка — соблюдайте лимиты Gmail и правила антимассовой рассылки.
• Для корпоративных сценариев используйте выделенный SMTP с ключами и ограничениями домена.
• Регулярно отзывайте старые пароли приложений и создавайте новые.

Пример сценария развёртывания (SOP)

1. Разработчик создаёт и тестирует макрос в тестовой книге.
2. ИТ отводит служебный аккаунт Gmail и создаёт пароль приложения.
3. Файл загружают в защищённую папку на сервере или рабочей станции оператора.
4. Планировщик заданий создаёт задачу на нужного пользователя, указывая путь к Excel и к книге.
5. Запускают задачу и проверяют отправку.
6. Настраивают мониторинг и уведомления о сбоях.

Edge‑case галерея (когда метод не сработает)

• Аккаунт Gmail имеет включённую расширенную защиту Google Account Advanced Protection — пароли приложений могут быть недоступны.
• Организация блокирует SMTP‑подключения со стороны клиентов (firewall/блокировка портов).
• Ограничения на отправку писем в Gmail (ежедневные лимиты) при массовых рассылках.

Краткое резюме

Отправка почты из Excel через VBA и CDO — простой и гибкий инструмент для автоматизации уведомлений и отчётов. Для безопасной работы используйте пароль приложения Gmail, ограничьте доступ к файлу и тестируйте интеграцию на разных сценариях. Если у вас корпоративная инфраструктура, рассмотрите альтернативы вроде Outlook Object Model, Power Automate или корпоративного SMTP.

Краткий чеклист напоследок:

• Пароль приложения создан и сохранён.
• Книга сохранена как .xlsm.
• Подключена библиотека CDO или используется CreateObject.
• Макрос протестирован локально.
• Планировщик настроен и протестирован.

Спасибо — теперь вы можете превратить Excel в простой инструмент рассылки и автоматизации.

Итог

• Используйте CDO для простоты и совместимости с Windows.
• Для корпоративных задач подумайте об альтернативах с лучшей централизацией секретов.
• Тщательно тестируйте и обеспечьте безопасность хранения паролей.