スプレッドシートからのメールの一斉送信にファイル添付機能を追加

添付ファイルを一斉送信する

前回は、スプレッドシート内に記入したメールアドレスに対してメールを一斉送信するスクリプトを紹介した。

しかし、ビジネスシーンにおいては、こうした多くのメールアドレスに添付ファイルを送りたいケースもあるだろう。そこで今回は、前回のスクリプトを改良し、Googleドライブからのファイル添付機能、そしてスプレッドシートに対する配信記録の保存機能を追加する。

まず添付ファイルだが、Google Apps Scriptに興味がある方々なら、会社でも各種書類をGoogleドキュメントベースで作成しているケースが多いだろう。

今回のスクリプトを使えば、これらGoogleドキュメント上にあるデータを自動でPDF化し、一斉送信メールの添付ファイルとして活用できるようになる。

それではまずスプレッドシートの内容から見ていこう。

スプレッドシートの「メール本文」シートを前回と比べると、「本文」の下部に「添付ファイル1 ID」「添付ファイル2 ID」「添付ファイル3 ID」という項目を追加している。ここに、メールに添付したい文書やスプレッドシートなどを指定する。

添付ファイルのIDは、添付したいファイルのURLから抽出する。Googleドキュメントから対象ファイルを開き、たとえばURLが「docs.google.com/document/d/ABCDEFG123456789/edit」なら「ABCDEFG123456789」部分のみをコピーし、添付ファイル1 IDの項目へ貼り付ければよい。

なお、ここでは3個まで同時に添付できるようにしているが、スクリプトを変更すれば4個以上のファイル添付も行える。

一斉配信メールの配信記録を残す

企業では複数の担当者がメール送信を手がけることもあるが、こうした場合に注意しなければならないのが、お互いに「他の人が送信しているだろう」と思い込んで実際には送信されていなかったり、逆に二重配信をしてしまったりといったケースだ。

そこで今回のバージョンでは、メールを一斉送信した内容の配信記録が残るようにしている。この配信記録があれば、いつ誰にメール送信したのかが一目瞭然。添付ファイルに指定したGoogleドキュメント上のファイルも確認できる。

それではスプレッドシートの内容を見てみよう。

スプレッドシートには、新たに「配信記録」というシートが作成されており、ここに一斉配信した記録を残すことができる。

記録項目は左から、誰が配信したかを示す「配信者」、配信日時が分かる「配信完了日」、何件にメールを送ったか把握できる「宛先数」、さらにはメールの「件名」と「本文サマリ」、どのような添付ファイルを送ったかが分かる「添付ファイル1」「添付ファイル2」「添付ファイル3」を用意した。

これらの項目はメール一斉配信のスクリプト実行時に自動で入力されるため、手入力と違って入力忘れやミスがないのもポイントといえるだろう。

スクリプトの中身をチェック

それでは、スクリプトの中身についても確認しておこう。スクリプトは記事末尾に掲載しているので、連載1回目を参照し、スプレッドシートを新たに作成してスクリプトエディタを開き、コピー&ペーストして利用できる。

最初の部分は前回のスクリプトと同じく、5～16行目でスプレッドシートの宛先リストから処理対象となる行数を、18～29行目でメール本文の情報を取得している。

そして31行目からが添付ファイルの取得処理だ。33行目で変数を作成し、36～38行目で「メール本文」シートにある「添付ファイル1 ID」を取得する。もし添付ファイルがあれば、40～46行目でGoogleドライブから指定ファイルを呼び出し、添付用のデータを作成。51～59行目、64～72行目については、同様に「添付ファイル2 ID」と「添付ファイル3 ID」の処理を行っている。

032 :  // 添付ファイル用の配列を作成
033 :  var attachmentFiles = new Array();
034 :  
035 :  // 添付ファイル1を取得
036 :  var attachment1_Id = sheetMailHonbun.getRange(6, 3).getValue();
037 :  var attachment1;
038 :  var attachment1_URL = "";
039 :  
040 :  if (attachment1_Id != "") {
041 :    // Google Driveから添付ファイルのデータを取得
042 :    var attachment1 = DocsList.getFileById(attachment1_Id).getBlob();
043 :    if (attachment1 != null) {
044 :      // Gmail添付用のデータを作成（ファイル名、mimeタイプ、バイト配列を指定）
045 :      attachmentFiles.push({fileName:attachment1.getName(), mimeType: attachment1.getContentType(), content:attachment1.getBytes()});
046 :      attachment1_URL = DocsList.getFileById(attachment1_Id).getUrl();
047 :    }
048 :  }

なお、ファイル名はGoogleドライブ上のものがそのまま流用される。

77行目からは、前回のスクリプトと同じくメール送信処理を行っている。77～84行目で差込データの取得、87～101行目で差込処理を実施。105～107行目で宛先となるTo/Cc/Bccを取得している。

110～113行目のメール送信については、添付ファイルの有無に応じて送信処理を変えている。添付ファイルがある場合は111行目、ない場合は113行目の処理が実行される仕組みだ。

110 :      if (attachmentFiles.length > 0) {
111 :       MailApp.sendEmail(to, sasikomiGoSubject, sasikomiGoBody, {cc:cc, bcc:bcc, attachments:attachmentFiles});
112 :     } else {
113 :       MailApp.sendEmail(to, sasikomiGoSubject, sasikomiGoBody, {cc:cc, bcc:bcc});
114 :     }

そして前回と同じく、117行目で次に処理する行番号を設定、120行目で配信状態の変更を行っている。

125行目からは、「配信記録」シートへの自動入力処理を行っている。

125行目で配信記録シートを指定し、128～129行目でデータが入力されている範囲を取得。132行目で配信者、135行目で配信完了日、138行目で宛先合計、141行目で件名、144行目で本文サマリ、147～158行目で各添付ファイルのURLをスプレッドシートに入力している。

なお、本文サマリについては初期状態で30文字まで表示されるようになっているが、「setValue(body.substring(0, 30)」の「30」部分を変えれば任意の文字数に設定できる。

162行目以降は、終了確認ダイアログの表示や処理開始行番号の初期化など、スクリプトの全体動作に関する処理となる。

以上でメール一斉送信のスクリプトは完了だが、皆さんはどのように感じただろうか。このようにスクリプトが用意されていれば、「意外に難しくないかも」と思った方も多いはずだ。

本連載では次回以降も見本スクリプトの公開と解説を行っていくので、興味を持った方はぜひチャレンジしていただきたい。

/**
 * メール一括送信処理の定義
 */
function sendMail() {
  //------------- （１）宛先リストから処理対象の行数を取得 -----------------------
  // スプレッドシート（MS Excelで言う所のブック）を取得
  var book = SpreadsheetApp.getActiveSpreadsheet();

  // 宛先リストシートを取得
  var sheetAtesakiList = book.getSheetByName("宛先リスト");

  // データが入力されている範囲を取得
  var datRange = sheetAtesakiList.getDataRange();

  // 処理対象の行数を取得
  var numRows = datRange.getNumRows();  

  //------------- （２）メール本文の情報を取得 -----------------------
  // メール本文シートを取得
  var sheetMailHonbun = book.getSheetByName("メール本文");

  // メール件名を取得
  var subject = sheetMailHonbun.getRange(2, 3).getValue();

  // メール本文を取得
  var body = sheetMailHonbun.getRange(4, 3).getValue();

  // 処理を開始する行番号を取得
  var start = sheetAtesakiList.getRange(1, 5).getValue();

  //------------- （３）Google Driveから添付ファイルを取得 -----------------------
  // 添付ファイル用の配列を作成
  var attachmentFiles = new Array();

  // 添付ファイル1を取得
  var attachment1_Id = sheetMailHonbun.getRange(6, 3).getValue();
  var attachment1;
  var attachment1_URL = "";

  if (attachment1_Id != "") {
    // Google Driveから添付ファイルのデータを取得
    var attachment1 = DocsList.getFileById(attachment1_Id).getBlob();
    if (attachment1 != null) {
      // Gmail添付用のデータを作成（ファイル名、mimeタイプ、バイト配列を指定）
      attachmentFiles.push({fileName:attachment1.getName(), mimeType: attachment1.getContentType(), content:attachment1.getBytes()});
      attachment1_URL = DocsList.getFileById(attachment1_Id).getUrl();
    }
  }

  // 添付ファイル2を取得
  var attachment2_Id = sheetMailHonbun.getRange(7, 3).getValue();
  var attachment2;
  var attachment2_URL = "";

  if (attachment2_Id != "") {
    var attachment2 = DocsList.getFileById(attachment2_Id).getBlob();
    if (attachment2 != null) {
      attachmentFiles.push({fileName:attachment2.getName(), mimeType: attachment2.getContentType(), content:attachment2.getBytes()});
      attachment2_URL = DocsList.getFileById(attachment2_Id).getUrl();
    }
  }

  // 添付ファイル3を取得
  var attachment3_Id = sheetMailHonbun.getRange(8, 3).getValue();
  var attachment3;
  var attachment3_URL = "";

  if (attachment3_Id != "") {
    var attachment3 = DocsList.getFileById(attachment3_Id).getBlob();
    if (attachment3 != null) {
      attachmentFiles.push({fileName:attachment3.getName(), mimeType: attachment3.getContentType(), content:attachment3.getBytes()});
      attachment3_URL = DocsList.getFileById(attachment3_Id).getUrl();
    }
  }

  //------------- （４）メール送信処理 -----------------------
  for (var i = start;i <= numRows;i++) {
    // 対象行の差込データを取得
    var sasikomiData1 = sheetAtesakiList.getRange(i, 6).getValue();
    var sasikomiData2 = sheetAtesakiList.getRange(i, 7).getValue();
    var sasikomiData3 = sheetAtesakiList.getRange(i, 8).getValue();

    var sasikomiGoSubject = subject;
    var sasikomiGoBody = body;

    // 差込データ1が入力されている場合は、差込処理を行う（件名＋本文）
    if (sasikomiData1 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ1}}", sasikomiData1, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ1}}", sasikomiData1, "g");
    }

    // 差込データ2が入力されている場合は、差込処理を行う
    if (sasikomiData2 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ2}}", sasikomiData2, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ2}}", sasikomiData2, "g");
    }

    // 差込データ3が入力されている場合は、差込処理を行う
    if (sasikomiData3 != "") {
      sasikomiGoSubject = sasikomiGoSubject.replace("{{差込データ3}}", sasikomiData3, "g");
      sasikomiGoBody = sasikomiGoBody.replace("{{差込データ3}}", sasikomiData3, "g");
    }

    // 対象行の宛先を取得
    var to = sheetAtesakiList.getRange(i, 2).getValue();
    var cc = sheetAtesakiList.getRange(i, 3).getValue();
    var bcc = sheetAtesakiList.getRange(i, 4).getValue();

    // メールを送信（添付ファイルがある場合とない場合で処理分け）
    if (attachmentFiles.length > 0) {
      MailApp.sendEmail(to, sasikomiGoSubject, sasikomiGoBody, {cc:cc, bcc:bcc, attachments:attachmentFiles});
    } else {
      MailApp.sendEmail(to, sasikomiGoSubject, sasikomiGoBody, {cc:cc, bcc:bcc});
    }

    // 処理を開始する行番号を設定（次回実行時には次の行から開始するようにする）
    sheetAtesakiList.getRange(1, 5).setValue(i + 1);

    // 配信状態を設定
    sheetAtesakiList.getRange(i, 5).setValue("配信済");
  }

  //------------- （５）後処理 -----------------------
  // 配信記録シートに結果を記録
  var sheetHaishinKiroku = book.getSheetByName("配信記録");

  // データが入力されている範囲を取得
  var datRange2 = sheetHaishinKiroku.getDataRange();
  var numRows2 = datRange2.getLastRow();

  // 配信者を保存
  sheetHaishinKiroku.getRange(numRows2 + 1, 1).setValue(Session.getActiveUser().getUserLoginId());

  // 配信完了日を保存
  sheetHaishinKiroku.getRange(numRows2 + 1, 2).setValue(new Date());

  // 宛先合計を保存
  sheetHaishinKiroku.getRange(numRows2 + 1, 3).setValue(numRows - 2);

  // 件名を保存
  sheetHaishinKiroku.getRange(numRows2 + 1, 4).setValue(subject);

  // 本文サマリを保存
  sheetHaishinKiroku.getRange(numRows2 + 1, 5).setValue(body.substring(0, 30).replace("\r", " ", "g").replace("\n", " ", "g") + "…");

  // 添付ファイル1URL
  if (attachment1_URL != "") {
    sheetHaishinKiroku.getRange(numRows2 + 1, 6).setValue(attachment1_URL);
  }

  // 添付ファイル2URL
  if (attachment2_URL != "") {
    sheetHaishinKiroku.getRange(numRows2 + 1, 7).setValue(attachment2_URL);
  }

  // 添付ファイル3URL
  if (attachment3_URL != "") {
    sheetHaishinKiroku.getRange(numRows2 + 1, 8).setValue(attachment3_URL);
  }

  // 終了確認ダイアログを表示
  Browser.msgBox("確認", "メール一斉送信が完了しました。", Browser.Buttons.OK);

  // 処理を開始する行番号を初期化
  sheetAtesakiList.getRange(1, 5).setValue(3);

};

/**
 * スプレッドシート（MS Excelで言う所のブック）を開いた時に実行する処理。
 * スプレッドシートを開いた際に、以下の処理（関数）が自動実行される。
 * ここではコントロールメニューにスクリプト実行コマンドを追加している。
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {
      name : "メール一斉送信",
      functionName : "sendMail"
    }
  ];
  sheet.addMenu("スクリプト実行", entries);
};