members

members

記入していない人だけにリマインド!メールの自動送信ハンズオン

Tweet

記入していない人だけにリマインド!メールの自動送信ハンズオン

メーリングリストに送ったリマインドメールは自分が対象じゃないと思われたり、メール振り分け設定によって無視されてしまいがちです。
一方で一人一人にメールを送るとなると毎回宛先の設定や本文の内容の設定が必要になり、工数がかかってしまいます。

今回はGoogleスプレッドシートとわずかなGoogle Apps Scriptを使って、自動で対象者だけにメールを送る仕組みを構築します。

目次

  1. 本記事の対象範囲
  2. Googleスプレッドシートに記入していない人を抽出する
    1. 記入用シートを用意する
    2. メールの原稿を作成する
  3.  スプレッドシートからメールを送る
    1. コードを埋め込む
    2. コードをテストする
  4. 抽出からメール送信までを自動化する
  5. 補足

本記事の対象範囲

今回は下記の構造で自動化を進めていきます。

  1. Googleスプレッドシートで指定のセルに記入していない人を抽出する
  2. スプレッドシートからメールを送る
  3. 抽出からメール送信までを自動化する

スプレッドシートのメール原稿をメール送信スクリプトで拾って、自動スケジュールでGmailと連携します

Googleスプレッドシートに記入していない人を抽出する

今回スプレッドシートに必要な情報は、下記5点になります。

  1. 記入済みかどうかを判定するセル
  2. 記入されていなかった時にメールを送る送り先
  3. メールを送る日付
  4. メールの件名・本文
  5. (日付の代わりに営業日で指定する場合)祝日や会社の休日

記入用シートを用意する

記入シート

今回は単純なものを想定します。
記入する欄をC列に設け、メールの送信先をB列で規定しているものとします。

メールの原稿を作成する

メール自動化シート

別のシートに原稿を用意します。
配信日、宛先、件名、本文、祝日等の情報の5つを記載しています。
今回は仮で配信日は下記関数を使用して、「今月の第一営業日」を指定しました。

=WORKDAY(EOMONTH(TODAY(),-1),1,A7:A)

こちらを記載すれば、毎月配信日を更新することなく第一営業日にメールが送信されます。
関数の内容は、「今日の日付の先月の最終日の次の営業日」を表記するようになっており、要約すると今月の第一営業日になります。
今日の日付を持ってくるTODAY関数と日付の月の最終日を取得するEOMONTH関数、営業日を計算するWORKDAY関数の組み合わせです。

宛先欄の関数は下記になります。

=TEXTJOIN(",",TRUE,QUERY('記入'!A2:C,"select B where C=''"))

「記入シートでC列が空のときのB列の行をカンマ区切り」で引用するような関数になっています。
QUERY関数で未入力のセルの行のメールアドレスを持ってきて、TEXTJOIN関数でカンマ区切りでくっつける処理をしています。

スプレッドシートからメールを送る

自動化する処理を作成します。
自動化シートのシート名を取得し、その中の指定のセルをGmailのフォーマットに落とし込んでいます。

コードを埋め込む

スクリプトエディタを開く

まず、スプレッドシートのスクリプトエディタを開きます。

次に、下記のコードをコピペします。

Google Apps Scriptのプロジェクトにコードを貼り付ける


const sheet = SpreadsheetApp.getActive().getSheetByName('メール自動化');//''の間にシート名を入れるとそのシート名の情報を持ってくる
const recipient = sheet.getRange(2, 2).getValue(); // TO B2
const subject = sheet.getRange(3, 2).getValue(); // 件名 B3
const body = sheet.getRange(4, 2).getValue(); // 本文 B4
const remind_hour = "9";//この時間だけ動作
const date = Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyy/MM/dd"); //今日の日付
const hour = Utilities.formatDate(new Date(), "Asia/Tokyo", "H"); //今の時間
const workday = Utilities.formatDate(sheet.getRange(1, 2).getValue(), "Asia/Tokyo", "yyyy/MM/dd"); //配信日 B1

function sendMail(){
if(hour == remind_hour && workday == date){ //日時が合っているか
GmailApp.createDraft(recipient, subject, body); //下書きにする場合はcreateDraft、直接送る場合はsendEmail
}
}

function onOpen(){
SpreadsheetApp.getUi()
.createMenu('メール')
.addItem('メール送信', 'sendMail')
.addToUi();
}

コードをテストする

onOpen関数を実行する

onOpenを選択して、実行ボタンを押します。

初回だけ下記ポップアップが出てくるので、「権限を確認」をクリックします。

確認ポップアップを承認

そのあとにいくつかポップアップが出てきますがすべて許可をしてください。

無事onOpenが実行できると、スプレッドシートのツールバーに手動でメール送信するメニューが追加されます。

ツールバーにメニューが追加された

現状のコードだとメール送信はされないので、安心してメール送信ボタンを押してください。

スプレッドシートのメール自動化シートB1の日付と今日の日付、スクリプトのremind_hourと今の時間が合致していればメールボックスに下書きが保存されます。

生成されたメール

何も起きない場合は自動化シートのB1とコードのremind_hourを今日の日時に合わせてテストしてみてください。

テストに問題がなければ、コードの12行目、createDraftをsendEmailに置換しましょう。

ここまでで、本当にメールが送られる設定になりました。

抽出からメール送信までを自動化する

2で作った処理を毎時自動で動作させます。コードを開いて、左メニューのトリガーを選択してください。

Google Apps Scriptからトリガーを設定する

次の画面で右下の「トリガーを追加」を選択し、「sendMailを毎時発火する」トリガーを設定します。

トリガーを追加からsendMail、時間主導型、時間ベースのタイマー、1時間おきに設定して保存する

そうすると、パソコンを閉じていても毎時動作して、指定の日時の場合のみメールが送られるトリガーが完成しました。

トリガー設定が完了し、自動でメールが送られる設定ができていることの確認画面

補足

今回はごく単純なメール自動化について説明しました。

Google Apps Script はスプレッドシートのように列の追加や削除に対して柔軟に対応することができません。
もし変更が必要な場合はsheet.getRangeの参照セルを変更してください。

また、「GAS sendEmail」で検索すればCCの追加など、機能を足すこともできると思います。

他にもQUERY関数のように入り組んだ関数もありますが、その部分をIF文で書き出して、その範囲に対してTEXTJOIN関数の対象を取ることもできます。

今回の内容を応用すれば、簡易的なマーケティングオートメーションを組めたり、入力者を名簿と照合してGoogleフォームでアンケート未記入の人にのみメールを送ったりすることも可能です。

コラム執筆者

加藤 功介(かとう こうすけ)

EMCカンパニー 第4ビジネスユニット アカウントサービス第10ユニット所属。
ノーコード・ローコードに精通したディレクター。部署内外の自動化を多数支援。