Автоматическая генерация сообщений электронной почты
Разберемся, какие возможности в части рассылки электронных писем представляет Excel. Дополним наш список дебиторов двумя графами (рис. 1): в графе «e-mail» содержатся адреса электронной почты, а графа «Письмо» содержит формулу:
=ГИПЕРССЫЛКА("mailto:"&E2&"&subject=Просрочка!&body=
Уважаемый "&C2&"!%0A%0AСообщаю Вам о том, что "&B2&"
имеет просроченную задолженность перед ООО "Ромашка"
в размере "&D2&" руб. Просим срочно погасить долг.%0A%0AПушкин А.С.";"Отправить")
Рисунок 1. Доработанный список дебиторов
Функция ГИПЕРССЫЛКА предназначена для вставки в Excel ссылок на веб-сайты. Ее первый параметр – адрес веб-сайта, второй – текст. В качестве адреса мы указали строку, начинающуюся со слова «mailto:». Оно говорит о том, что адрес является адресом электронной почты, и при щелчке на ячейку будет открываться почтовая программа с открытым окном создания нового письма. Но мы пошли дальше. Параметр subject содержит строку, которая должна быть вставлена в поле «Тема» письма, а параметр body – текст письма, который реализован в виде формулы. В результате первый параметр функции ГИПЕРССЫЛКА для первого дебитора фактически выглядит так:
- Уважаемый Сергеев Сергей Сергеевич!
- Сообщаю Вам о том, что ООО «Юпитер» имеет просроченную задолженность перед
- ООО «Ромашка» в размере 9823423 руб. Просим срочно погасить долг.
- Пушкин А.С.
Текст сформирован из нескольких ячеек и разбит на абзацы. Этот эффект достигнут за счет использования спецсимволов %0A%0A, которые по таблице ASCII-кодов означают перевод строки. Так, мы вместили в один параметр полноценное письмо из трех абзацев. Теперь посмотрим, как работает наша функция. Щелкнем на строку «Отправить», расположенную напротив дебитора ООО «Юпитер». В результате откроется окно создания нового письма в Outlook (разумеется, если он установлен на вашем компьютере и является почтовой программой по умолчанию) и заполнится так, как показано на рис. 2. Нам остается лишь внести какие-либо правки в текст письма (если это необходимо) и отправить его нажатием кнопки «Отправить».
Рисунок 2. Электронное письмо о просрочке
Данный механизм позволяет гибко управлять темой, текстом письма, составом его получателей и т.д. Единственный его недостаток в том, что длина первого параметра функции ГИПЕРССЫЛКА не может превышать 255 символов, что вносит ограничения в наши возможности по донесению наших мыслей до контрагентов. Соответственно, механизм идеально подходит для коротких писем, но в более сложных случаях надо искать какие-то другие решения.
Рассылка расчетных листков
Расчетный листок – достаточно сложный по своей структуре документ, в котором указываются суммы начислений, удержаний, фактических выплат сотрудникам и т.д. Лимит в 255 символов для подобных документов вряд ли является приемлемым. Кроме того, в компаниях с большой численностью сотрудников (сотни и тысячи человек) необходимо иметь такой механизм, который бы с помощью всего лишь одного действия генерировал и отправлял нужное количество писем. А поэтому функция ГИПЕРССЫЛКА, которая создает только одно письмо, нам в данном случае уже не подходит. Самым удобным, простым и технологичным способом решения задачи рассылки расчетных листков является создание специализированного макроса.
Сформируем расчетную ведомость, как показано на рис. 1.
Рисунок 1. Зарплатная ведомость
Нам необходимо создать макрос, который для каждого сотрудника из этого списка создаст расчетный листок на основе имеющейся в таблице информации и вышлет на адрес электронной почты, указанный в графе e-mail. Текст этого макроса представлен ниже.
Private Function FForm(Num)
If (Num = "") Or (Num = "0") Then FForm = "0 руб." Else FForm = Format(Num, "# ###.00") & "руб."
End Function
Public Sub MakeTextReg()
Dim SheetTable As Worksheet
Dim Line As Integer
Set SheetTable = ActiveWorkbook.Worksheets("Ведомость")
Open "d:\textreg.txt" For Output As #1
Line = 4
Do While SheetTable.Cells(Line, 1).Value <> ""
Print #1, "start"
Print #1, SheetTable.Cells(Line, 2).Value
Print #1, SheetTable.Cells(Line, 3).Value
Print #1, "Расчётный листок за" & SheetTable.Cells(1, 9).Value
Print #1, "<b>Оклад: </b>" & FForm(SheetTable.Cells(Line, 5).Value)
Print #1, "<b>Бонусы: </b>" & FForm(SheetTable.Cells(Line, 6).Value)
Print #1, "<b>Итого начислено: </b>" & FForm(SheetTable.Cells(Line, 7).Value)
Print #1, "<b>Удержан НДФЛ: </b>" & FForm(SheetTable.Cells(Line, 8).Value)
Print #1, "<b>К выдаче: </b>" & FForm(SheetTable.Cells(Line, 9).Value)
Print #1, "stop"
Print #1, ""
Line = Line + 1
Loop
Close #1
MsgBox ("Формирование текстового файла с данными для р асчётных листков" & "завершено.")
End Sub
Макрос организован достаточно просто: он проходит в цикле по всем строкам зарплатной ведомости и по каждому сотруднику формирует текст расчетного листка на основе данных, находящихся в графах «Оклад», «Бонусы», «Итого начислено» и т.д. Результаты работы макроса сохраняются в текстовом файле d:\textreg.txt, причем данные по каждому сотруднику компании отделены служебными словами start и stop.
Откроем этот текстовый файл и посмотрим на результаты работы макроса (см. рис. 2). Обратите внимание на использование HTML-тэгов <b> и </b>. Применять их необязательно (так как письма можно рассылать в формате Plain Text, а не HTML), но это добавляет привлекательности и профессионализма полученным письмам, что мы увидим чуть позже.
Рисунок 2. Текстовый файл с данными для расчетных листков сотрудников
Теперь нам необходимо каким-то образом передать эту информацию в Outlook для того, чтобы он сформировал и отправил сотрудникам письма с этими расчетными листками.
Потрясающей особенностью пакета приложений Microsoft Office является то, что все они поддерживают язык VBA и, соответственно, макросы на этом языке. Не является исключением и Outlook, поэтому для формирования писем с расчетными листками мы создадим еще один макрос, на этот раз в среде Outlook, и используем его для осуществления рассылки. Текст макроса представлен ниже.
Public Sub SendHTMLEmails()
Dim EName, EMail, ETitle, S, MsgText As String
Dim Msg As MailItem
Dim C As Integer
C = 0
Open "d:\textreg.txt" For Input As #1
Do Until EOF(1)
Line Input #1, S
C = C + 1
If S <> "start" Then
MsgBox ("Ожидается слово start в строке" & C)
Exit Do
End If
Line Input #1, EName: C = C + 1
Line Input #1, EMail: C = C + 1
Line Input #1, ETitle: C = C + 1
MsgText = "<p><b>" & EName & "</b></p><p><b><u>" & ETitle & "</u></b></p>"
Line Input #1, S: C = C + 1
Do While S <> "stop"
MsgText = MsgText & "<p>" & S & "</p>"
Line Input #1, S
C = C + 1
Loop
Line Input #1, S
C = C + 1
Set Msg = CreateItem(olMailItem)
Msg.BodyFormat = olFormatHTML
Msg.To = EMail
Msg.Subject = EName & "-" & ETitle
Msg.HTMLBody = "<HTML><BODY>" & MsgText & "</BODY></HTML>"
Msg.Save
Msg.Send
Loop
Close #1
End Sub
Макрос выполняет в некотором смысле обратную работу по отношению к предыдущему: загружает данные из файла d:\textreg.txt и формирует на их основе электронные письма, которые отправляет получателям. На рис. 3 представлен пример электронного письма, сформированного данным макросом.
Рисунок 3. Расчетный листок сотрудника в виде электронного письма в Outlook
С помощью такого простого механизма можно организовывать почтовые рассылки абсолютно любых масштабов, ограниченных лишь вашей фантазией и производственной необходимостью. Он удобен для компаний, которые осуществляют расчет заработной платы сотрудников в Excel, однако не очень подходит для компаний, использующих программу «1С: Зарплата и управление персоналом» («1С: ЗУП») и нуждающихся в удобном механизме рассылки расчетных листков, формируемых этой программой. Так что теперь посмотрим, как Excel может нам помочь в решении этой задачи.
Выгрузим расчетные листки сотрудников из «1С: ЗУП» единым списком в файл Excel и разместим их на отдельном листе нашего файла, как показано на рис. 4. Как мы видим, в нашей книге теперь два листа: «Ведомость» (который мы обсуждали ранее) и «Листки» (который выгружен из «1С»). Лист «Ведомость» нам будет необходим для решения нашей задачи, так как в расчетных листках из «1С» нет адресов электронной почты сотрудников, поэтому их мы будем брать из листа «Ведомость».
Рисунок 4. Расчетные листки из «1С» в файле Excel
Код макроса, который будет формировать расчетные листки по формату «1С», представлен ниже.
Private Function FForm(Num)
If (Num = "") Or (Num = "0") Then FForm = "0 руб." Else FForm = Format(Num, "# ###.00") & "руб."
End Function
Public Sub MakeTextReg()
Dim SheetTable, SheetList As Worksheet
Dim Line, LineList, LineBlock As Integer
Dim Name, ViaCash As String
Dim Found As Boolean
Set SheetTable = ActiveWorkbook.Worksheets("Ведомость")
Set SheetList = ActiveWorkbook.Worksheets("Листки")
Open "d:\textreg2.txt" For Output As #1
Line = 4
Do While SheetTable.Cells(Line, 1).Value <> ""
Name = SheetTable.Cells(Line, 2).Value
LineList = 1
Found = False
Do While SheetList.Cells(LineList, 1).Value <> "stop"
If (SheetList.Cells(LineList, 1).Value = Name) And(SheetList.Cells(LineList + 10, 1).Value = "1.Начислено") Then
Found = True
Exit Do
Else
LineList = LineList + 1
End If
Loop
If Found And (SheetTable.Cells(Line, 4).Value <> "") Then
Print #1, "start"
Print #1, Name
Print #1, SheetTable.Cells(Line, 3).Value
Print #1, "Расчётный листок за" & SheetTable.Cells
(1, 9).Value
Print #1, "<b>" & "1. Начислено для выплаты на карточку" & "</b>"
LineBlock = LineList + 11
Do While SheetList.Cells(LineBlock - 1, 1).Value <> "Всего начислено"
Print #1, SheetList.Cells(LineBlock, 1).Value & ":" & FForm(SheetList.Cells(LineBlock, 25).Value)
LineBlock = LineBlock + 1
Loop
Print #1, ""
Print #1, "<b>" & "2. Удержан налог на доходы физических лиц" & "</b>"
LineBlock = LineList + 11
Do While SheetList.Cells(LineBlock – 1, 37).Value <> "Всего удержано"
If SheetList.Cells(LineBlock, 37).Value <>"" Then Print #1, SheetList.Cells(LineBlock, 37).Value & ":"& FForm(SheetList.Cells(LineBlock, 48).Value)
LineBlock = LineBlock + 1
Loop
LineBlock = LineBlock + 1
Print #1, ""
Print #1, "<b>" & "3. Перечислены деньги на банковскую карту" & "</b>"
Do While SheetList.Cells(LineBlock - 1, 37).Value <>"Всего выплат”
If Right(SheetList.Cells(LineBlock, 37).Value, 13) ="(через кассу)"
Then
ViaCash = Left(SheetList.Cells(LineBlock, 37).Value, Len(SheetList.Cells(LineBlock, 37).Value) - 14)
Else
ViaCash = SheetList.Cells(LineBlock, 37).Value
End If
Print #1, ViaCash & ":" & FForm(SheetList.Cells(LineBlock, 48).Value)
LineBlock = LineBlock + 1
Loop
Print #1, "stop"
Print #1, ""
End If
Line = Line + 1
Loop
Close #1
End Sub
Макрос организован сложнее, чем предыдущий. Он сканирует в цикле зарплатную ведомость на листе «Ведомость», для каждого сотрудника, указанного в ведомости, ищет расчетный листок на листе «Листки», производит синтаксический разбор листков и выгружает результаты в текстовый файл d:\textreg2.txt.
Структура полученного текстового файла представлена на рис. 5. Как мы видим, она чуть более сложная, чем в предыдущем примере, но суть та же самая: по каждому сотруднику указывается расчетная информация (тут она сгруппирована в блоки «1. Начислено для выплаты на карточку», «2. Удержан налог на доходы физических лиц» и «3. Перечислены деньги на банковскую карту»), заключенная в логические рамки start и stop.
Рисунок 5. Текстовый файл с данными для расчетных листков сотрудников из «1С: ЗУП»
Для отправки расчетных листков мы можем воспользоваться тем же макросом в Outlook, который мы использовали в прошлый раз. Как выглядит электронное письмо с расчетным листком, выгруженным из «1С», можно посмотреть на рис. 6.
Рисунок 6. Расчетный листок сотрудника из «1С» в виде электронного письма в Outlook
Предложенные подходы (расчетные листки на основе ведомостей по собственной форме и расчетные листки на основе выгрузок из «1С: ЗУП») можно комбинировать, формируя детальные листки с указанием начислений и удержаний, достигнутых показателей KPI и прочих составных частей системы мотивации, принятой в каждой конкретной компании. Преимущество данного механизма – в его гибкости и универсальности, а также возможности использования всего арсенала инструментов Excel при подготовке и осуществлении рассылок.
Организация веб-опросов
Всеобщий тренд интеграции программного обеспечения с интернетом коснулся и пакета Microsoft Office. В настоящее время он распространяется как в офлайн-версии (традиционный десктопный Office), так и в онлайн-версии (Office 365), когда все приложения, в том числе и Excel, реализованы в виде веб-приложений, а файлы пользователя сохраняются в облачном сервисе OneDrive. Благодаря интеграции Excel с этим облачным сервисом появляется возможность с легкостью проводить веб-опросы большого количества людей на какую-либо тему.
Причем Excel в связке с OneDrive блестяще справляется не только со сбором многочисленных ответов, но и с обработкой полученных результатов, а также их представлением в удобном и наглядном виде.
1. Установите OneDrive на компьютер. После этого в Проводнике Windows появится новая папка – OneDrive. Она уже связана с облаком OneDrive, и все размещенные в ней файлы автоматически копируются в облако.
2. Зайдите на сайт onedrive.live.com под своим логином и паролем. Oткроется стартовое окно сервиса (см. рис. 1).
Рисунок 1. Стартовое окно сервиса OneDrive
3. Вызовите команду «Опрос Excel» в меню «Создать».Откроется окно редактора опроса (см. рис. 2).
Рисунок 2. Окно редактора опроса
4. Заполните поля опроса. Допустим, мы организуем опрос сотрудников насчет того, какого числа они хотели бы получать заработную плату. Тогда в поле «Название опроса» напишем «Опрос о заработной плате», в поле «Описание опроса» – «Принятие решения о сроках выплаты зарплаты сотрудникам». После этого перейдем к полю первого вопроса. В правой части окна откроется раздел ввода вопроса, как показано на рис. 3.
Рисунок 3. Раздел ввода вопроса
Каждый вопрос характеризуется следующими параметрами:
- текст вопроса – именно на содержимое данного поля должен ответить пользователь;
- подзаголовок вопроса – здесь размещается дополнительная информация, конкретизирующая вопрос или дающая какие-то пояснения;
- тип отклика – задается тип ответа: текст, числовое значение, дата и т.п.;
- обязательно – данный признак указывает на то, что оставить вопрос без ответа нельзя;
- ответ по умолчанию – вариант ответа, который предлагается по умолчанию, то есть если пользователь не укажет свой ответ, то будет принят ответ, предложенный системой.
Первый вопрос будет касаться фамилии и имени сотрудника. Запрос будет выглядеть так, как показано на рис. 4.
Рисунок 4. Ввод имени и фамилии сотрудника
Сразу после ввода первого вопроса появляется возможность ввести второй вопрос, для этого надо кликнуть на строку «Добавление нового элемента».
Предложим сотруднику выбрать удобную для него дату выплаты заработной платы из выпадающего списка, то есть сотрудникам не нужно вбивать ответ вручную (см. рис. 5). Из перечисленных вариантов ответа: 5-го числа, 10-го числа, 15-го числа, последний вариант указан в качестве ответа по умолчанию.
Рисунок 5. Выбор даты выплаты заработной платы
Третий вопрос будет иллюстрировать использование типа отклика (да/нет). Спросим сотрудников, удобно ли им получать зарплату на карточку, а не наличными из кассы (см. рис. 6).
Рисунок 6. Выбор способа получения денег
5. Опубликуйте опрос. Завершив создание опроса, нажмите кнопку «Опубликовать опрос». OneDrive предложит вам создать ссылку на опрос, которую необходимо будет передать сотрудникам, чтобы они могли принять участие в опросе (см. рис. 7). Нажав кнопку «Создать ссылку», мы даем команду OneDrive сгенерировать ссылку. Спустя несколько секунд она будет отображена в этом же окне вот в таком виде:
https://onedrive.live.com/redir?page=survey&resid=44145588E67CC2F7!2524&authkey=!APCx_9vhYuvYFTo&ithint=file%2cxlsx
Рисунок 7. Создание ссылки на опрос
Однако отправлять сотрудникам такую длинную ссылку не очень удобно. Нажав кнопку «Сократить ссылку», мы сможем ужать ссылку до следующего вида: http://1drv.ms/1MLNlHw. Теперь с ней работать гораздо проще.
После создания ссылки в браузере откроется окно Excel Online, в котором отобразится таблица с нашими вопросами так, как это показано на рис. 8. Именно в эту таблицу будут заноситься ответы всех сотрудников – каждая строчка будет соответствовать одному вопросу. Кроме того, в папке OneDrive на нашем компьютере будет автоматически создан файл Опрос1.xlsx, куда также будут аккумулироваться ответы на вопросы, так что результаты опроса всегда будут у вас под рукой. То есть чтобы их увидеть, вам даже не придется открывать браузер.
Рисунок 8. Таблица с вопросами в Excel Online
Теперь покажем, что произойдет, когда сотрудники пройдут по ссылке опроса. Перед ними откроется страница, как показано на рис. 9. После ответа на вопросы данного опроса им нужно нажать кнопку «Отправить». Система подтвердит получение ответов так, как показано на рис. 10. Сразу после получения ответа он добавится в наш опрос, в чем можно убедиться, например, открыв файл Опрос1.xlsx в папке OneDrive на своем компьютере (см. рис. 11).