ExcelからVBAを使ってメール送信を行う

社内でExcelを使っている方は多いはずです。そしてその中にはメールアドレスをはじめとして、各種データが蓄積されているでしょう。今回はExcelからメール送信を行う方法を紹介します。利用するのはExcel VBAになります。

Customers Mail Cloudの設定

まずはCustomers Mail Cloudにて送信サーバの設定を行います。

ユーザ登録

ユーザ登録はまず、メールアドレスとパスワードを入力するところからはじまります。

f:id:moongift:20190510151017p:plain
ユーザ登録

入力したメールアドレス宛にメールアドレスの確認メール(仮登録受付メール)が届きますので、URLをクリックします。その後、名前や住所といった必要な情報を入力します。ユーザ登録が完了すると、無料トライアル開始のための審査が入ります。時間は長くとも30分程度です。「Customers Mail Cloud 無料トライアル利用開始のご案内」というメールが届いたら利用できます。

ログイン後に行うこと

ログインしたら、2つの作業を行う必要があります。

f:id:moongift:20190510150911p:plain
ダッシュボード

DKIMキーを設定する

DKIMキーはメールのなりすましを防止するための技術になります。メール配信を行うドメインのTXTレコードに対して設定します。管理コンソールでドメインを追加しようとすると、専用のキーが表示されます。

f:id:moongift:20190510150735p:plain
DKIMキーの取得

ドメインは s999999999999._domainkey.example.com のような形式になります。設定する値は v=DKIM1; p=MIG...QAB のように指定されるはずです。この設定はシステム管理者などに依頼して行ってもらってください。

DNS設定はすぐに反映されませんので、数分後にDNSを確認ボタンを押します。正しく設定されていれば、ドメインが追加登録できます。

ユーザを追加する

次にメール配信を行うユーザを追加します。これはAPI設定にて行います。ユーザ名(メールアドレス形式)、パスワードを設定します。また、利用できる機能を制限できます。例えばSMTPのみ、APIのみといった指定も可能です。

f:id:moongift:20190510150841p:plain
ユーザの追加

サーバに設定を反映する

設定を行ったら、それをサーバに反映します。サーバ構成機能にて行います。この反映を行わないと、DKIMやユーザ追加設定が反映されませんので注意してください。ここまでで以下の情報が入手できているはずです。

  • APIユーザ
  • APIキー

さらにAPIサーバのエンドポイントURLが必要です。これは契約しているプランによって異なりますのでご注意ください。

プラン名 エンドポイントURL
無料トライアル https://sandbox.smtps.jp/api/v2/emails/send.json
Standardプラン https://te.smtps.jp/api/v2/emails/send.json
Proプラン https://SUBDOMAIN.smtps.jp/api/v2/emails/send.json

これらの情報はメール送信時に設定しますので覚えておいてください。

必要なもの

Customers Mail CloudではJSONを使ってWeb APIを操作します。そこで、VBA-JSONというライブラリを使って手軽にWeb APIを呼び出します。

VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

参考にしたのは次の記事です。

VBA で Web API を呼び出す - Qiita

設定として上記VBA-JSONのインポートと、Microsoft Scripting Runtimeの参照設定を忘れずに行ってください。

f:id:moongift:20191217102404p:plain
参照設定

実装について

まずHTTPリクエストを行うメソッドについてです。これはJSONを受け取り、XMLHTTPを使ってWeb APIを呼び出します。

Public Function RequestHTTP(ByVal method As String, ByVal url As String, Optional ByVal param As Object) As Object
    Dim json
    json = ConvertToJson(param)

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open method, url, False
        .SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .send json

        If .ResponseText <> "" Then
            Set RequestHTTP = ParseJson(.ResponseText)
        End If
    End With
End Function

次に呼び出しです。まずDictionary型を使ってリクエスト内容を作ります。tofrom のようにDictionary型が入れ子になっているものは括弧を使って追加できないので、 Add メソッドを利用してください。

また、API経由でメール送信を行う際に使うAPIユーザ、APIキーはそれぞれ置き換えてください。

Dim params As New Dictionary

params("api_user") = "api@smtps.jp"
params("api_key") = "YOUR_PASSWORD"

Dim toAddress As New Dictionary

toAddress("address") = "to@smtps.jp"
toAddress("name") = "テスト受信者"
params.Add "to", toAddress

Dim fromAddress As New Dictionary
fromAddress("address") = "info@smtps.jp"
fromAddress("name") = "Customers Mail Cloud"
params.Add "from", fromAddress

params("subject") = "メールのテスト"
params("text") = "メールの本文"

リクエストする構造ができあがったら、契約状態によって異なるAPIエンドポイントURLを指定してRequestHTTPを実行します。

Call RequestHTTP("POST", "https://sandbox.smtps.jp/api/v2/emails/send.json", params)

今回はCallメソッドを使っていますが、返値はObject型になります。処理が問題なく行われれば、次のようなJSONが返ってきます。

{
  "id": "<2106857532.1251.1565150810829@mta02.sandbox.smtps.jp>"
}

f:id:moongift:20191217102429p:plain
受信したメール

まとめ

ExcelからSMTPを使って送信する方法もありますが、外部ライブラリを読み込みなどが大きな手間になります。Web API経由であれば、ごく簡単にメール送信が可能です。宛先や件名、本文などはExcelシートに設定しておき、まとめて配信するのも簡単でしょう。ぜひCustomers Mail Cloudとともにお試しください。

クラウドからのメール送信を簡単に。確実に。| Customers Mail Cloud