Рассылка почты

Автоматизированная рассылка почты (писем со вложениями)

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

 

Представим себе, что у нас в Excel есть таблица с адресами почты и прочей информацией,
и наша цель - разослать каждому человеку из списка по письму.

исходная таблица Excel с адресами почты для рассылки писем

Причем, надо разослать не одинаковые письма (мы же не спамеры какие-то), а персонифицированные (текст писем будет различаться).

Кроме того, к каждому письму надо прикрепить несколько файлов - часть из этих файлов «статичная» (файлы одни и те же, для всех писем), а другая часть - это документы Word, Excel или PDF, содержащие информацию из нашей таблицы Excel (в каждом документе - свои данные).

Программа заполнения документов данными из Excel (из Excel в Word) + рассылка почты

Заполнение документов Word данными из таблицы Excel

Надстройка FillDocuments предназначена для формирования (подготовки) документов по шаблонам, с заполнением созданных файлов данными из текущей книги Excel, с возможностью отправки писем.

В качестве шаблонов могут выступать следующие типы файлов:

  • шаблоны и документы Word (расширения DOC, DOCX, DOCM, DOT, DOTX, DOTM)
  • шаблоны и книги Excel (расширения XLS, XLSX, XLSM, XLSB, XLT, XLTX, XLTM)
  • текстовые документы (расширения TXT, DAT, XML и т.д.)

В качестве исходных данных для заполнения, используется открытый в Excel файл (с таблицей, содержащей строку заголовка)

Количество шаблонов документов неограничено (хотя в программе предусмотрен вывод предупреждения, если количество используемых шаблонов превышает 50 - поскольку время формирования документов напрямую зависит от количества файлов)

Рассылка писем выполняется средствами почтовых программ TheBAT! или Outlook (на ваш выбор)

 

Разобраться в назначении, и способах использования программы FillDocuments, вам поможет видеоинструкция,
в которой я рассказываю об основном назначении программы, и показываю, как она работает:

Макрос для создания списка случайных адресов email

Макрос выводит на активный лист (в первый столбец) случайные email

Я использовал этот макрос для тестирования программы рассылки писем.

Sub GenerateEmails()
    On Error Resume Next: Err.Clear
    Dim ra As Range: Set ra = Range([A2], Range("A" & Rows.Count).End(IIf(Len(Range("A" & Rows.Count)), xlDown, xlUp)))
    Dim cell As Range
    txt = "abcdefghijklmnopqrstuvwxyz_1234567890"
    For Each cell In ra.Cells
        n = n + 1: dom$ = Choose(n Mod 3 + 1, "@mail.ru", "@yandex.ru", "@gmail.com")
        Randomize: Nam$ = ""
        For i = 1 To Rnd(n) * 5 + 4
            Nam$ = Nam$ & Mid(txt, Fix(Rnd(i) * Len(txt) + 1), 1)
        Next
        cell = Nam$ & dom$
    Next cell
End Sub

Макрос отправки письма из Excel через Outlook

Пример макроса, отправляющего письма со вложениями из Excel через почтовый клиент Outlook:

Sub Отправить_Письмо_из_Outlook()
 
    'отправляем письмо без вложений
    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 1", "Тема письма 1")
    If res Then Debug.Print "Письмо 1 отправлено успешно" Else Debug.Print "Ошибка отправки"
 
 
    'отправляем письмо с 1 вложением
    attach$ = ThisWorkbook.FullName    ' прикрепляем текущий файл Excel
    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 2", "Тема письма 2", attach$)
    If res Then Debug.Print "Письмо 2 отправлено успешно" Else Debug.Print "Ошибка отправки"
 
 
    'отправляем письмо с несколькими вложениями
    Dim coll As New Collection    ' заносим в коллекцию список прикрепляемых файлов
    coll.Add "C:\Documents and Settings\Admin\Рабочий стол\Tyres.jpg"
    coll.Add "C:\Documents and Settings\Admin\Рабочий стол\calc.xls"
    coll.Add ThisWorkbook.FullName    ' прикрепляем текущий файл Excel

    res = SendEmailUsingOutlook("name@domain.ru", "Текст письма 3", "Тема письма 3", coll)
    If res Then Debug.Print "Письмо 3 отправлено успешно" Else Debug.Print "Ошибка отправки"
End Sub

Макрос использует функцию SendEmailUsingOutlook, которая:

  • принимает в качестве параметров адрес получателя письма, тему и текст письма, список вложений
  • запускает Outlook, формирует письмо, и отправляет его
  • возвращает TRUE, если отправка прошла успешно, или FALSE, если с отправкой почты вызникли проблемы

Чтение значений из реестра Windows на VBA

Чтение и запись в реестр Windows в произвольную ветку можно произвести при помощи функций объекта WScript.Shell:

RegRead и RegWrite

Первая функция возвращает значение, считанное из реестра, вторая - записывает заданное значение в реестр.

При попытке считать несуществующий параметр возникает ошибка,
обойти которую нам поможет директива On Error Resume Next 

 

Пара функций для примера:

(получаем настройки программы из реестра Windows)

Обработка заказов из интернет-магазина (анализ писем в Outlook)

Скриншот заголовка таблицы заказов из интернет-магазина, загруженных из писем в Outlook

Программа анализирует поступившие заказы с интернет-магазина (письма в программе Outlook), извлекает необходимые данные из писем (в формате HTML), и формирует сводную таблицу заказов

 

Файл программы состоит из 2 листов - на первом листе находятся кнопки запуска, на втором - шаблон создаваемой сводной таблицы заказов.

 

Формирование и рассылка случайного списка торговых точек

Программа предназначена для еженедельного формирования плана проверок торговых точек.

 

Функции программы:

  • выборка из базы данных заданного числа случайных торговых точек, в соответствии с настройками для каждой группы
  • формирование таблицы (файл Excel) со списком выбранных объектов по шаблону
  • рассылка созданного файла (в архиве ZIP) по заданному списку адресов электронной почты

 

Сохранение настроек почтового аккаунта в реестре

Форма редактирования настроек почты в Excel

Макрос предназначен для сохранения в реестре (и изменения через форму) настроек почтового аккаунта.

Эти настройки используются макросом рассылки почты Send_Mail
(для отправки почты из Excel без применения специализированных почтовых программ)

При запуске формы, она считывает из реестра Windows ранее сохранённые настройки.

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

После редактирования логина, пароля или адреса SMTP-сервера,
нажмите кнопку «Сохранить и проверить», чтобы сохранить в реестре изменённые данные.

После сохранения настроек, макрос попытается отправить тестовое письмо, используя сохранённые настройки.
(для этого, помимо формы, в книге Excel в стандартном модуде должен присутствовать макрос Send_Mail)

Если введённые настройки корректны, на компьютере есть доступ к интернету, и в системе установлена библиотека CDO for Windows,
будет выведено сообщение об успешной отправке тестового письма.

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

Программа загрузки базы данных с сайта АвтоТрансИнфо

Описанная в этой статье программа уже не работает (на сайте веели ограничение на просмотр данных)

Посмотрите видеоинструкцию по работающей нынче программе, — парсер сайта грузоперевозок ati.su

Программа предназначена для загрузки базы данных сайта autotransinfo.ru (АТИ) в книгу Excel.

Результатом работы программы является книга Excel, состоящая из 2 листов: «Организации» и «Контактные лица».

На листе «Организации» собрана вся информация об организациях, зарегистрированных на сайте autotransinfo.ru,
а на листе «Контактные лица» - подробные сведения о сотрудниках каждой из организаций, и их контактные данные
(имя, номер телефона и факса, ник в Скайпе, адрес электронной почты, номер ICQ)

 

Загрузка данных с сайта осуществляется в 3 этапа: