Как отправлять электронные письма из электронной таблицы Excel с использованием сценариев VBA

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

Разблокируйте шпаргалку «Основные ярлыки Microsoft Office» прямо сейчас!

Это подпишет вас на нашу рассылку

Введите адрес электронной почты
отпереть
Прочитайте нашу политику конфиденциальности

Мы рассмотрели множество отличных макросов Excel, которые могут выполнять те же функции, что и сценарии VBA, но без необходимости знания программирования. Но есть много продвинутых вещей, которые вы можете делать только с VBA, например, создание отчета в виде электронной таблицы со всей информацией о вашем ПК.

Зачем отправлять электронную почту из Excel?

Существует множество причин, по которым вы можете отправить электронное письмо из Microsoft Excel.

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

Вы, вероятно, думаете, что составить сценарий трансляции электронной почты из Excel будет сложно. Это совсем не так.

Техника в этой статье будет использовать функцию, которая была доступна в Excel VBA в течение длительного времени, Объекты совместной работы (ЦД).

CDO — это компонент обмена сообщениями, используемый в Windows с самых ранних поколений ОС. Раньше его называли CDONTS, а затем с появлением Windows 2000 и XP его заменили на «CDO для Windows 2000». Этот компонент уже включен в вашу установку VBA в Microsoft Word или Excel и готов к использованию.

Использование компонента делает отправку электронной почты из продуктов Windows с VBA чрезвычайно простой. В этом примере вы будете использовать компонент CDO в Excel, чтобы отправить электронное письмо, которое будет доставлять результаты из определенной ячейки Excel.

Шаг 1. Создание макроса VBA

Первый шаг — перейти на вкладку Excel Developer.

На вкладке Разработчик, нажмите на Вставить в поле «Элементы управления», а затем выберите командную кнопку.

Нарисуйте его на листе, а затем создайте новый макрос для него, нажав на макрос в ленте разработчика.

Когда вы нажимаете Создайте Кнопка, это откроет редактор VBA.

Добавьте ссылку на библиотеку CDO, перейдя к инструменты > Рекомендации в редакторе.

Прокрутите список вниз, пока не найдете Microsoft CDO для библиотеки Windows 2000. Установите флажок и нажмите Хорошо.

Когда вы нажимаете Хорошо, запишите название функции, в которую вы вставляете скрипт. Тебе это понадобится позже.

Шаг 2: Настройте поля «От» и «Кому» CDO

Для этого сначала нужно создать почтовые объекты и настроить все поля, необходимые для отправки электронного письма.

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

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
strSubject = "Results from Excel Spreadsheet"
strFrom = "rdube02@gmail.com"
strTo = "rdube02@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))

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

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

Шаг 3. Настройте CDO для использования внешнего SMTP

В следующем разделе кода вы сконфигурируете CDO для использования любого внешнего SMTP-сервера для отправки электронной почты.

Этот пример — не-SSL настройка через Gmail. CDO поддерживает SSL, но это выходит за рамки этой статьи. Если вам нужно использовать SSL, этот расширенный код в Github может помочь.

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With

Шаг 4: Завершите настройку CDO

Теперь, когда вы настроили соединение с SMTP-сервером для отправки электронной почты, все, что вам нужно сделать, это заполнить соответствующие поля для CDO_Mail объект, и выдать послать команда.

Вот как вы это делаете:

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description  "" Then MsgBox Err.Description

Там не будет никаких всплывающих окон или сообщений системы безопасности, которые могут произойти, если вы прибегаете к использованию почтового объекта Outlook.

CDO просто собирает электронную почту и использует информацию о соединении вашего SMTP-сервера для запуска сообщения. Это самый простой способ включить электронную почту в сценарии Microsoft Word или Excel VBA.

Чтобы подключить командную кнопку к этому сценарию, войдите в редактор кода и нажмите Лист1 чтобы просмотреть код VBA для этого листа.

Введите имя функции, куда вы вставили скрипт выше.

Вот как выглядело это сообщение, которое я получил в своем почтовом ящике:

Заметка: Если вы получили сообщение об ошибке Транспорт не смог подключиться к серверу, убедитесь, что вы ввели правильное имя пользователя, пароль, SMTP-сервер и номер порта в строках кода, перечисленных ниже С SMTP_Config.

Продолжайте и автоматизируйте весь процесс

Хорошо, если вы можете отправлять электронную почту из Excel одним нажатием кнопки. Тем не менее, вы можете использовать эту функцию на регулярной основе, и в этом случае имеет смысл автоматизировать процесс

,

Для этого вам нужно внести изменения в макрос. Перейдите в редактор Visual Basic, скопируйте и вставьте весь код, который мы собрали.

Далее выберите ThisWorkbook от проект иерархия.

В двух раскрывающихся полях вверху окна кода выберите рабочая тетрадь и выберите открыто из раскрывающегося списка методов.

Вставьте сценарий электронной почты выше в Private Sub Workbook_Open ().

Это будет запускать макрос всякий раз, когда вы открываете файл Excel.

Далее откройте Диспетчер задач.

Вы собираетесь использовать этот инструмент, чтобы попросить Windows автоматически открывать электронную таблицу через равные промежутки времени, после чего ваш макрос будет запущен при отправке электронного письма.

Выбрать Создать основную задачу … от действие меню и работайте через мастера, пока не дойдете до действие экран.

Выбрать Запустить программу и нажмите следующий.

Использовать Просматривать чтобы найти расположение Microsoft Excel на вашем компьютере, или скопируйте и вставьте путь в Программа / скрипт поле.

Затем введите путь к документу Microsoft Excel в поле Добавить аргументы поле.

Завершите работу мастера, и ваше расписание будет на месте.

Стоит провести тест, запланировав действие

в течение пары минут в будущем, затем внесите поправки в задачу, как только сможете подтвердить, что она работает.

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

Для этого откройте электронную таблицу и перейдите к файл > Опции > Трастовый центр.

Отсюда нажмите Настройки Траст-центра, и на следующем экране установите радио-диск в Никогда не показывать информацию о заблокированном контенте.

Заставьте Microsoft Excel работать на вас

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

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

Требуется время, чтобы накопить опыт работы с VBA, но вы скоро увидите плоды своих трудов, если сможете их использовать.

Отличное место для начала — наш авторитетный учебник по использованию VBA в Excel.

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