Звоните из таблицы, отправляйте SMS из базы данных – использование Twilio в Excel и Access

Если вы, как и я, пользуетесь Windows последние 20 лет, то, скорее всего, одними из самых привычных программ для вас являются Excel и Access. И хотя у этих двух приложений тысячи функций, до сих пор никто в Microsoft не додумался встроить в них телефон. Но сегодня это изменится.

Хорошо это или плохо, но Microsoft Excel и Access стали основой многих бизнес-процессов в компаниях любого размера. Известно, что только Excel используется для создания финансовых моделей многих предприятий и даже целых стран. Эти приложения завоевали доверие бизнес-пользователей благодаря своей высокой настраиваемости, в том числе с помощью создания пользовательской логики на Visual Basic for Applications (VBA) — реализации языка Visual Basic. Расширение Office с помощью VBA популярно, так как этот инструмент доступен даже непрофессиональным разработчикам, прост в изучении и позволяет быстро добавлять пользовательскую логику в приложения Office.

В этой статье я покажу, как с помощью VBA расширить возможности Excel и Access, чтобы отправлять SMS-сообщения и совершать звонки через API Twilio прямо из этих приложений.

Одно из самых распространённых применений Excel — использование его в качестве простой плоской базы данных. Создать новую таблицу, ввести заголовки столбцов и начать составлять списки очень просто. Давайте используем этот сценарий, чтобы продемонстрировать, как легко с помощью VBA взаимодействовать с API Twilio.

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

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

Начнём с создания пользовательского интерфейса, который позволит инициировать отправку сообщений. Начиная с Excel 2007, можно добавлять новые элементы в ленту: вкладки, группы и кнопки, а затем обрабатывать события от этих элементов в коде VBA. Я добавлю новую кнопку для отправки SMS.

Самый простой способ добавить элементы в ленту — использовать редактор Office Custom UI Editor, который можно скачать здесь.

Это приложение позволяет определять новые элементы с помощью XML-схемы ленты. Ниже приведён XML-код, который я создал для добавления новой вкладки, группы и кнопки в ленту:

Самый важный атрибут в XML — это onAction, расположенный в элементе button. С его помощью вы указываете Excel, какую подпрограмму VBA вызывать при нажатии кнопки. В моём случае я указал, что при нажатии кнопки должна выполняться подпрограмма conSendSms, которую я напишу чуть позже.

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

Теперь мне нужно написать код, который будет отправлять SMS-сообщения.

Чтобы открыть редактор VBA в Excel, нажмите Alt+F11 или откройте вкладку РАЗРАБОТЧИК и нажмите кнопку Visual Basic. Чтобы сделать вкладку видимой, можно настроить ленту:

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

Чтобы проверить это, я могу вернуться в Excel и нажать кнопку «Отправить SMS». Появится окно сообщения. Довольно просто, не правда ли?

Далее я хочу создать пользовательское диалоговое окно, в котором можно будет указать текст сообщения. Для этого я создам новую форму в проекте и добавлю текстовое поле и две кнопки: «Отправить» и «Отмена».

При нажатии кнопки «Отмена» диалоговое окно должно закрыться (это очевидно), а при нажатии кнопки «Отправить» — отправляться SMS-сообщения.

Чтобы указать, кому отправлять сообщения, я создам выделение, выбрав набор ячеек в таблице. Теперь в событии нажатия кнопки «Отправить» я могу перебрать все выделенные строки в выделении:

Наконец, мне нужно, чтобы мой код VBA взаимодействовал с REST API Twilio для отправки SMS. Для этого требуется отправить HTTP-запрос на нужный URL Twilio с обязательными параметрами: номер телефона отправителя, номер телефона получателя и текст сообщения.

Для отправки HTTP-запроса я буду использовать объект MSXML2.XMLHTTP60 из библиотеки Microsoft XML 6.0, поэтому мне нужно добавить ссылку на неё в проект:

После добавления ссылки на библиотеку XML я использую её API для создания и отправки HTTP-запроса Twilio внутри функции SendSms. При отправке запроса необходимо настроить аутентификацию с помощью учётных данных Twilio (их можно получить в панели управления аккаунтом Twilio) и передать обязательные параметры в теле запроса:

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

Теперь мне остаётся только обновить цикл, чтобы вызывать функцию SendSms, передавая параметры from, to и message:

Вот и всё. Несколько строк кода VBA — и я отправляю SMS прямо из Excel. Конечно, можно сделать этот код более надёжным и универсальным: например, добавить возможность указывать столбец с номерами телефонов (в моём примере он задан жёстко) или динамически подставлять данные из других ячеек таблицы в сообщение.

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

Отправка SMS из Access немного отличается, так как в Access уже много версий реализована концепция форм. Формы Access позволяют разрабатывать довольно сложные приложения прямо внутри Access и легко работать с данными в базе. Я покажу, как отправлять SMS прямо из Access, используя тот же сценарий с футбольным тренером, что и в предыдущем разделе.

Для начала я создал простую базу данных с двумя таблицами: Teams и Players, и добавил те же данные, что были в таблице Excel. Я установил связь между таблицами Teams и Players, чтобы можно было сопоставить игроков с определённой командой:

Затем я создал новый запрос Roster, который возвращает INNER JOIN между таблицами Teams и Players, предоставляя единый список, который я буду использовать в качестве источника данных для формы.

Теперь, когда база данных настроена, я могу создать форму для отправки сообщений. В отличие от Excel, где мне пришлось добавлять кнопку на ленту, чтобы показать диалоговое окно, в Access уже есть встроенные формы, поэтому я могу создать новую форму диалога и добавить в неё элементы управления.

Как и в диалоге Excel, я добавил текстовое поле для ввода сообщения и две кнопки: для закрытия диалога и отправки сообщений. Аналогично форме в Excel, при нажатии кнопки «ОК» в форме Access происходит перебор записей и отправка SMS для каждой из них.

Однако в Access, поскольку данные хранятся в традиционной SQL-базе, вместо выбора ячеек для перебора я могу просто выполнить запрос Roster, созданный ранее, и перебрать записи, возвращённые запросом.

Я могу получить доступ к полям каждой записи, указав имя запроса и имя поля:

Наконец, я добавлю ссылку на библиотеку Microsoft XML 6.0 и вставлю в форму ту же подпрограмму SendSms, которую создал для примера с Excel, и теперь я отправляю SMS из Access.

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

Теперь, когда я показал, как легко отправлять SMS из Excel и Access, давайте посмотрим, как изменить код, чтобы Twilio инициировала исходящий звонок каждому игроку и сообщала о переносе тренировки из-за плохой погоды.

Для этого мне нужно внести два изменения в код. Во-первых, изменить URL Twilio, на который отправляется HTTP-запрос. URL для инициирования звонков отличается от того, который используется для отправки SMS.

Этот новый URL требует передачи другого набора параметров. Если для отправки SMS нужны параметры from, to и message, то для инициирования исходящего звонка требуются параметры from, to и url.

Это подводит меня ко второму изменению. Мне нужно создать URL, который Twilio сможет запросить для получения инструкций о том, что делать после ответа на звонок. Эти инструкции называются TwiML. Чтобы упростить создание таких инструкций, я воспользуюсь сервисом Simple Message Twimlet.

Сервис Simple Message Twimlet умеет принимать строку текста в качестве параметра URL и преобразовывать её в TwiML с помощью глагола . Когда сгенерированный TwiML возвращается Twilio, Twilio преобразует текст внутри в голосовое сообщение, которое услышит ответивший на звонок.

В URL ниже показано, как передать сообщение, которое должен услышать абонент, в Twimlet. Вы можете посмотреть TwiML, который генерирует Twimlet, перейдя по ссылке.

Я возьму URL Twimlet и передам его в качестве значения параметра url в HTTP-запросе к Twilio.

Теперь, когда я запускаю диалог в Access, Twilio совершает звонок на каждый номер телефона игрока и сообщает, что сегодняшняя тренировка отменена из-за погоды.

Вот и всё. Превратить два самых широко используемых приложения — Excel и Access — в телефоны так же просто, как написать немного кода на VBA. Эти программы и без того обладают огромными возможностями, а возможность расширять их с помощью API Twilio делает их ещё мощнее.

В этой статье я показал, как с помощью небольшого кода VBA в Excel и Access и API Twilio можно легко отправлять SMS-сообщения и совершать исходящие звонки прямо из этих приложений.

Вы можете скачать файлы Excel и Access, использованные в примерах этой статьи, а если у вас есть вопросы или комментарии, свяжитесь со мной в Twitter или по электронной почте.