Три способа отправить письмо из Excel с помощю VBA

Excel позволяет создавать диаграммы высокого качества, работать с огромным количеством данных, обрабатывать картинки, блок-схемы и многое другое. И даже если вам и этого не достаточно, можно использовать Excel для автоматической отправки писем с помощью встроенного VBA редактора.

Данная статья описывает три способа отправки писем с помощью VBA в Excel. Вы можете скачать файл с примером отправки email с помощью VBA в Excel.

Отправить письмо в Excel с помощью VBA

Один из самых простых способов для автоматизации отправки почты с Excel заключается в вызове функции Create («ObjectOutlook.Application»). Данная функция возвращающает ссылку на ActiveX объект (в данном случает приложение Outlook), которое затем используется для создания и отправки электронной почты.

Чтобы проверить данный способ в работе, скопируйте и вставьте код ниже в VB редактор.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Попытка отправить письмо с помощью VBA"
Email_Send_From = "exceltipmail@gmail.com"
Email_Send_To = " exceltipmail@gmail.com "
Email_Cc = " exceltipmail@gmail.com "
Email_Bcc = " exceltipmail@gmail.com "
Email_Body = "Поздравляем!!!! Ваше письмо успешно отправлено !!!!"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

В качестве напоминания: Когда вы пытаетесь отправить письмо вышеуказанным способом, система безопасности будет выдавать каждый раз предупреждающее окно, в котором будет говориться о том, что Программа пытается отправить сообщение от вашего имени… и возможности обойти этот шаг нет.

предупреждение об отправке письма

К счастью, существует еще два способа, с помощью которых данный вопрос может быть решен: первый – через использование CDO, второй – имитирующий использование событий нажатий клавиш клавиатуры.

Отправить письмо в Excel с помощью CDO

Что такое CDO? CDO является библиотекой объектов, которая предоставляет интерфейс Messaging Application Programming Interface (MAPI). CDO позволяет манипулировать обменом данных, и отправлять и получать сообщения.

Использование CDO может быть предпочтительно в случаях, когда вы хотите предотвратить появление вплывающих окон безопасности Программа пытается отправить сообщение от вашего имени… и следовательно, предотвратить задержку отправки сообщения.

В нашем примере мы используем функцию CreateObject («CDO.Message»). Важно отметить, что необходимо правильно установить конфигурацию SMTP сервера, чтобы не допустить появления ошибок Run-time error 2147220973(80040213) или sendUsing configuration value is invalid. Пример ниже настроен на отправку сообщений через почту Google (Gmail). Для других почтовых серверов, вам потребуется ввести свои значения SMTP-сервера и SMTP-порта.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub CDO_Mail_Small_Text_2()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Полный адрес вашего GMail ящика"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail пароль"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Update
End With
strbody = "Поздравляем!!!! Ваше письмо успешно отправлено !!!!"
With iMsg
Set .Configuration = iConf
.To = "Почтовый адрес получателя"
.CC = ""
.BCC = ""
.From = """ВашеИмя"" <Reply@something.nl>"
.Subject = "Попытка отправить письмо с помощью CDO"
.TextBody = strbody
.Send
End With
End Sub

Обратите внимание, чтобы воспользоваться данным методом вам необходимо подключить библиотеку CDO в редакторе макросов Tool –> References.

Отправить письмо с помощью CDO

Отправить письмо в Excel с помощью Send Keys

Другой способ отправки email с помощью Excel – использование команды ShellExecute, которая выполняет любую программу в VBA. Команда ShellExecute используется для загрузки документа с соответствующей программой. По сути, вы создаете объект String (текстовые данные) и передаете его в качестве параметра для функции ShellExecute. Остальная часть операций выполняется в окнах. Автоматически определяется, какая программа связана с данным типом документа и используется для загрузки документа. Вы можете использовать функцию ShellExecute, чтобы открыть Internet Explorer, Word, Paint и множество других приложений. В коде ниже используется задержка в три секунды, чтобы убедиться, что отправляемое письмо корректно и для возможности предотвратить отправку, если вы вдруг нашли какие-нибудь недочеты.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Send_Email_Using_Keys()
Dim Mail_Object As String
Dim Email_Subject, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
Email_Subject = "Попытка отправить письмо с помощью SendKeys"
Email_Send_To = "exceltipmail@gmail.com "
Email_Cc = "exceltipmail@gmail.com "
Email_Bcc = "exceltipmail@gmail.com "
Email_Body = "Поздравляем!!!! Ваше письмо успешно отправлено !!!!"
Mail_Object = "mailto:" & Email_Send_To & "?subject=" & Email_Subject & "&body=" & Email_Body & "&cc=" & Email_Cc & "&bcc=" & Email_Bcc
On Error GoTo debugs
ShellExecute 0&, vbNullString, Mail_Object, vbNullString, vbNullString, vbNormalFocus
Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "^({ENTER})"
Application.SendKeys ("{ENTER}")
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Cкачать файл с примером отправки email с помощью VBA в Excel


31 комментариев

    • Добрый день, Дмитрий.
      Чтобы отправить письмо с вложением из Excel с помощью VBA, необходимо добавить адрес прикрепляемого файла. Команда будет выглядеть так .Attachments.Add "C:\temp\Test.xlsx". Данный код необходимо поместить в блоке With Mail_Single, перед командой .Send

  1. Здравствуйте!
    А не могли бы Вы привести способы отправки из Excel с помощью VBA только определенной информации (например только выбранную строку или несколько ячеек)?
    Спасибо!

          • Здравствуйте, Ренат

            При запуске макроса появляется ошибка в строчке
            If Err.Description <> «» Then MsgBox Err.Description

            Compile error:
            Syntax error

          • Марат, попробуйте воспользоваться кодом в файле, который прикреплен к статье. Я перепроверил код, все работает. Такая ошибка возникает обычно, когда присутствует ошибка в написании синтаксиса кода

      • Здравствуйте!
        Я пытаюсь отправить диапазон значений из Вашего примера
        Email_Body = Range("A4").Value
        и ставлю диапазон, например:
        Email_Body = Range("A20:С30").Value
        появляется ошибка Run-time.
        Что я делаю не так?

  2. Ренат, добрый день!

    При использовании метода SendKeys почему-то отправка происходит через один раз: то отправляется, то просто останавливается на окне с письмом, но отправка не происходит. Причём периодичность отправки-неотправки строго держится. С чем это может быть связано? Я просто скачал Ваш файл, поправил в VBA адрес почты для отправки писем и запустил метод.

    • Добрый день, Веталь

      Я подряд отправил 5 писем с помощью SendKeys на трех разных компьютерах и проблем с макросом не обнаружил. Думаю, проблема связана с почтовой программой (я использую Microsoft Outlook). Возможно, у вас в настройках где-то стоит блокировка дублирующих писем.

  3. Спасибо вам за статью! интересные способы! Кстати, а если нужно произвести рассылку email, то лучше всего использовать специальный сервис по типу Unisender’a (кому надо, вот ссылочка -http://www.unisender.com/ru/)

  4. Здравствуйте, Ренат!
    Спасибо Вам за эту статью!
    Пытаюсь с ее помощью сделать массовую рассылку с вложениями по подразделениям, на тестах пока все работает. Пользовался пока только первым способом, есть такой вопрос:
    Можно ли использовать настройки подписи из Outlook, если она там забита?
    Кстати, на несколько адресов пока не посылал (в плане загнать этот код в цикл), а на один адрес у меня никаких запросов на подтверждение отправки не появляется.

      • Ок, спасибо!
        Я так и сделал, просто переменная получается страшненькая — там и тело письма, и подпись, и куча переводов строки :)
        Еще вопрос: если несколько адресов или несколько вложений — какой должен быть синтаксис? Через запятую или как?

        • Методом проб и ошибок дошел :)
          Если кому нужно — все просто:
          With Mail_Single
          .Subject = Email_Subject
          .To = Email_Send_To
          .CC = Email_Cc
          .BCC = Email_Bcc
          .Body = Email_Body
          .Attachments.Add fn1
          .Attachments.Add fn2
          .Attachments.Add fn3
          .Send
          End With

  5. Ренат, здравствуйте.
    Небольшое замечание: поскольку вы CDO объекты создаёте не в операторе dim, а через CreateObject, то необходимости подключать библиотеку в References нет.

  6. Спасибо автору!
    Подскажите пжл как прописать код таким образом, чтобы инфорация в теле письма шли не одной строчкой а на нескольких например:
    1) Ваня
    2) Таня
    3) Гена
    а не 1) Ваня 2) Таня 3) Гена

  7. Мне нужно отправить анкету с моими данными В каждую строку я внес свои данные, а как теперь мне отправить сообщение через эксель? У меня в семье есть компьютерщик, но он в отъезде, а мне нужно отправить через эксель письмо Посоветйуте с чего нужно начинать ?

  8. Здравствуйте. При попытке отправить письмо через gmail с помощью SendKeys работа макроса заканчивается на открытии и формировании письма. Но кнопку «отправить» сам макрос не нажимает. Как настроить так, чтобы не надо было нажимать кнопку отправить вручную? Спасибо

  9. Добрый день, Ренат!
    Возможно ли сделать рассылку получателям из списка на несколько адресов? В первом варианте адрес получателя прописан в строке Email_Send_To = » exceltipmail@gmail.com » и он один. Спасибо за Ваш ответ.

    Николай

  10. Ренат, приветствую!
    решились мы на небольшую оптимизацию в работе с нашими клиентами, а именно, все входящие обращения от потребителей, для учета, «заводим» в Ексель. Так удобнее контролировать сроки предоставления ответов.
    Так вот, во вложении пример реестра входящих обращений, напротив каждого обращения были созданы кнопки ActiveX при нажатии на которую автоматом формируется письмо в Outlook’е для отправки (шаблон нашего ответа из ячейки и ниже текст обращения потребителя). Это всё для оперативности работы с потребителями, так как в день принимаем до 40-50 обращений.
    Единственный минус этого реестра — необходимость каждый раз создавать новую кнопку с корректировкой кода (ссылок на ячейки):

    Private Sub CommandButton2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim OutlookApp As Object, SM As Object
    Set OutlookApp = CreateObject(«Outlook.Application»)
    Set SM = OutlookApp.CreateItem(olMailItem)
    SM.To = Range(«D4»).Value
    SM.Subject = «ОТВЕТ НА ОБРАЩЕНИЕ»
    On Error Resume Next
    SM.Body = Range(«J4″).Value & » __________________________ » & Range(«E4»).Value
    SM.Display
    Set SM = Nothing
    Set OutlookApp = Nothing
    End Sub

    В этом случае оптимизации никакой…

    Вопрос: есть ли возможность копирования кнопки ActiveX в новой строке без переписывания кода???

    Очень нам поможете!!!

    Файл:  .xlsx

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *