[GAS]Googleスプレッドシートのメールアドレスリストへメール一斉送信(添付ファイル対応版)

GAS+Vue.JSで添付ファイル付きメール送信 GAS

Google Apps Scriptで、Googleスプレッドシート上のメールアドレスと名前のリストへ、メールを個別に一斉送信するスクリプトを作成します。添付ファイルをアップロードできるようにもなっています。

困っている人
困っている人

ファイルを添付して、メールを一斉にスプレッドシートから送りたいです。

シップ
シップ

スプレッドシートのサイドバーにメール送信フォームを表示させることでできますよ。

概要

スプレッドシートに記入されたメールアドレスと名前のリストから、宛先やあて名を変えて一斉配信したいときに役立つスクリプトです。

サイドバーにメール送信フォームが表示され、件名や本文、添付ファイルを指定して送信が行えます。

Google Apps Scriptを利用するため、すべて無料で実現できます。

必要なもの
  • 名前とメールアドレスをリスト化したGoogleスプレッドシート

アドレスリストの中の一部のアドレスだけに送信するためのグループと、送信フラグでの絞り込み機能も付いています。

ファイル添付やフォームを使用しないシンプルな一斉送信スクリプトでいい場合はこちら

Googleスプレッドシートで名前とメールアドレスのリストを用意

A:送信フラグB:グループC:名前D:敬称E:メールアドレスD:個別メッセージ
「1」の時送信絞り込みのグループ相手の名前「様」などの敬称メールアドレス個別メッセージ
スプレッドシートの列

各列の意味

A列:送信フラグ

この列が半角数字の「1」の場合、その行のメールアドレスに送信が行われます。

B列:グループ

アドレスリストの中から送信するグループを絞り込むために使用します。例えば部署ごととか会社ごとなどに分けて送信したいときに使用します。

C列:名前

送信先の名称です。敬称が付け加えられたものを {name} で件名や本文中で使用できます。

D列:敬称

送信先の宛先に付与する敬称です。「様」「先生」、企業の場合は「御中」などです。

E列:メールアドレス

送信先メールアドレスです。

D列:個別メッセージ

同じ文面の中で、各宛先ごとに変えたい部分がある場合に使用できます。 {message} で件名や本文中で使用できます。

  1. 上記のようなGoogleスプレッドシートを作成します。
  2. 1列目は項目名を記入します。
  3. 2行目から実際のデータを記入していきます。

Googleスプレッドシートにスクリプトを追加

  1. メニューの「拡張機能」→「Apps Script」(ない場合は「ツール」→「スクリプトエディタ」)からApps Scriptを開きます。
  2. コード.gsを選択し(最初から選択されています)最初から入力されている内容は削除し、次のコードをコピペします。
//送信者名を表示させたい場合は記入
var senderName = '';

//送信元メールアドレスを変更する場合は記入
//記入したアドレスは実行するGoogleアカウントのエイリアスとして設定されている必要があります
var senderMail = '';

var userSheetIndex = [
  "isSend","group","name","gender","mail","message"
]; //使用者リストの列順番

//スプレッドシート起動時に実行(メニューの登録)
function onOpen() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('メール', [
      {name: '一斉送信', functionName: 'createSidebar'},
    ]);
}

//使用者シートのカラムインデックスを返す row: カラム名
function getUserSheetIndex(row){
  return userSheetIndex.indexOf(row)
}

//グループを返す
function getGroups(){
  var mySheet = SpreadsheetApp.getActiveSheet();
  var selectedRow = mySheet.getRange(2, 2, mySheet.getLastRow()-1, 1 ).getValues(); //グループ列全体を取得
  var groups = ["全て"];
  for(var i=0; i<selectedRow.length; i++){
    if(selectedRow[i][0] != ""){
      groups.push(selectedRow[i][0]);
    }
  }
  return JSON.stringify(Array.from(new Set(groups)));
}

//サイドバーを作成する
function createSidebar() {
  var htmlOutput = HtmlService.createTemplateFromFile('mailform').evaluate();
  htmlOutput.setTitle('一斉送信');
  SpreadsheetApp.getUi().showSidebar(htmlOutput);   
}

//HTMLファイルをインクルード
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

//JSファイルをインクルード
function includeJS(filename) {
  var templateHtml = HtmlService.createTemplateFromFile(filename);
  return templateHtml.evaluate().getContent();
}

//メールの送信
function processSendEmail(form, reader_result, file_name){
  if(form["group"].length == 0){
      return {"error":true,"message":"グループを選択してください。"}
  }
  if(form["subject"]==""){
      return {"error":true,"message":"件名を入力してください。"}
  }
  if(form["body"]==""){
      return {"error":true,"message":"本文を入力してください。"}
  }
  
  let attachmentBlob = null;
  if(reader_result != null){
    var result_split = reader_result.split(','); //base64エンコードされたファイルをコンマで分割
    var content_type = result_split[0].split(';')[0].replace('data:', ''); //ファイルのMINEタイプ
    var row_data = result_split[1]; //ファイルの実データ
    var data = Utilities.base64Decode(row_data); //base64エンコードされた文字列からバイナリへデコード
    attachmentBlob = Utilities.newBlob(data, content_type, file_name);
  }

  var mySheet = SpreadsheetApp.getActiveSheet();  //アクティブシートを取得
  if(!mySheet){
      return {"error":true,"message":"シートがありません。"}
  }

  //送信元メールアドレスが、メールエイリアスとして登録されているかチェック
  if(senderMail!=""){
    let aliases = GmailApp.getAliases();
    if(aliases.indexOf(senderMail) === -1 && Session.getActiveUser().getUserLoginId() != senderMail) {
      return {"error":true,"message":'送信元メールアドレス'+senderMail+'はメールエイリアスとして設定されていません。'}
    }
  }

  console.log("Sheet:"+mySheet.getName()+" Group:"+form["group"]+" Subject:"+form["subject"]+" Body:"+form["body"]+" Attachment:"+(attachmentBlob?attachmentBlob.getName() + " ContentType: " + attachmentBlob.getContentType() + " Size: "+attachmentBlob.getBytes().length:"なし"));
  var regexp = /^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]{1,}\.[A-Za-z0-9]{1,}$/; //メールアドレスの正規表現
  var selectedRow = mySheet.getRange(2, 1, mySheet.getLastRow()-1, userSheetIndex.length ).getValues(); //アクティブな行全体を取得
  var sendque = []; //送信予定リスト
  var sended = [];  //送信済みリスト
  var errors = [];  //エラーリスト
  for(var i=0; i<selectedRow.length; i++){
    if((form["group"].indexOf("全て") > -1 || form["group"].indexOf(selectedRow[i][getUserSheetIndex("group")]) > -1) && selectedRow[i][getUserSheetIndex("isSend")] == "1" && selectedRow[i][getUserSheetIndex("mail")]!=""){
      userData = {
        "name" : selectedRow[i][getUserSheetIndex("name")],
        "gender" : selectedRow[i][getUserSheetIndex("gender")],
        "mail" : selectedRow[i][getUserSheetIndex("mail")].trim(),
        "message" : selectedRow[i][getUserSheetIndex("message")],
      }
      var subject = form["subject"].replace(/{name}/gi,userData["name"]+userData["gender"]);
      subject = subject.replace(/{message}/gi,userData["message"]);
      var body = form["body"].replace(/{name}/gi,userData["name"]+userData["gender"]);
      body = body.replace(/{message}/gi,userData["message"]);
      if(regexp.test(userData["mail"])){
        var message = {
          to: userData["name"]+" <"+userData["mail"]+">",
          subject: subject,
          body: body,
        }
        sendque.push(message);
      }else{
        errors.push(userData["name"]+" <"+userData["mail"]+">"); //メールアドレスの形式が正しくない場合はエラーリストへ追加
      }
    }
  }

  if(errors.length > 0){  //正しくないメールアドレスが含まれていた場合
    console.log('次のメールアドレスが正しくありません:'+errors.join(','));
    return {"error":true,"message":'次のメールアドレスが正しくありません:'+errors.join(',')}
  }

  if(sendque.length > MailApp.getRemainingDailyQuota()){ //送信予定数が残りの一日の送信可能数を超える場合
    console.log('一日の送信可能数を超えるため送信できません。本日残り可能送信数:'+MailApp.getRemainingDailyQuota());
    return {"error":true,"message":'一日の送信可能数を超えるため送信できません。本日残り可能送信数:'+MailApp.getRemainingDailyQuota()}
  }

  for(var i=0; i<sendque.length; i++){ //送信先リストからメール送信
    if(senderMail!=""){
      sendque[i]["from"] = senderMail;
    }
    if(senderName!=""){
      sendque[i]["name"] = senderName;
    }
    if(attachmentBlob){
      sendque[i]["attachments"] = [attachmentBlob];
    }
    MailApp.sendEmail(sendque[i]);
    sended.push(sendque[i]["to"]);
  }

  if(sended.length > 0){
    console.log("メールを送信しました。"+sended.join(','));
    return {"error":false,"message":"メールを送信しました。","sended":sended, "quota":MailApp.getRemainingDailyQuota()}
  }else{
    return {"error":true,"message":"該当する送信先がありません。"}
  }
}
  1. ファイルの追加(+ボタン)をクリックして、「HTML」を選択し、「mailform」という名前を付け、内容を下記のコードに書き換えます。
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://unpkg.com/tailwindcss@^1.0/dist/tailwind.min.css" rel="stylesheet">
  </head>
  <body>
   <div id="app">
    <div class="text-center font-semibold text-black">一斉メール送信</div>
    <div class="flex items-center justify-center bg-gray-50 py-1 px-2 sm:px-2 lg:px-2">
     <div class="max-w-3xl w-full space-y-4">
       <component :is="page" v-on:page="onPageChange" v-bind:groups="groups"></component>
       <div class="md:flex md:items-center">
         <div class="md:w-1/2">
         <button @click="closeSidebar" class="inline-block align-baseline font-bold text-sm text-blue-500 hover:text-blue-800" type="button">
         閉じる
         </button>
        </div>
       </div>
     </div>
    </div>
   </div>
  <script src="https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.js"></script>
  <script>
Vue.component('component-alert-success', {
  template: `
   <div class="md:flex md:items-center justify-center">
   <div class="bg-green-200 px-6 py-4 mx-2 my-4 rounded-md text-lg flex items-center mx-auto w-full">
    <svg viewBox="0 0 24 24" class="text-green-600 w-5 h-5 sm:w-5 sm:h-5 mr-3">
     <path fill="currentColor" d="M12,0A12,12,0,1,0,24,12,12.014,12.014,0,0,0,12,0Zm6.927,8.2-6.845,9.289a1.011,1.011,0,0,1-1.43.188L5.764,13.769a1,1,0,1,1,1.25-1.562l4.076,3.261,6.227-8.451A1,1,0,1,1,18.927,8.2Z"></path>
     </svg>
    <span class="text-green-800"><slot></slot></span>
   </div>
  </div>
        `
});

Vue.component('component-alert-error', {
  template: `
   <div class="md:flex md:items-center justify-center">
    <div class="bg-red-200 px-6 py-4 mx-2 my-4 rounded-md text-lg flex items-center mx-auto w-full">
      <svg viewBox="0 0 24 24" class="text-red-600 w-5 h-5 sm:w-5 sm:h-5 mr-3">
        <path fill="currentColor" d="M11.983,0a12.206,12.206,0,0,0-8.51,3.653A11.8,11.8,0,0,0,0,12.207,11.779,11.779,0,0,0,11.8,24h.214A12.111,12.111,0,0,0,24,11.791h0A11.766,11.766,0,0,0,11.983,0ZM10.5,16.542a1.476,1.476,0,0,1,1.449-1.53h.027a1.527,1.527,0,0,1,1.523,1.47,1.475,1.475,0,0,1-1.449,1.53h-.027A1.529,1.529,0,0,1,10.5,16.542ZM11,12.5v-6a1,1,0,0,1,2,0v6a1,1,0,1,1-2,0Z"></path>
      </svg>
    <span class="text-red-800"><slot></slot></span>
   </div>
  </div>
        `
});

Vue.component('component-loading', {
  template: `
 <div class="md:flex md:items-center justify-center">
  <div class="fixed top-0 right-0 h-screen w-screen z-50 flex justify-center items-center">
   <div class="animate-spin rounded-full h-32 w-32 border-t-2 border-b-2 border-gray-900"></div>
  </div>
  <div class="md:w-full justify-center items-center text-2xl py-4 px-4 text-center"><slot></slot></div>
 </div>
        `
});

Vue.component('component-mailform', {
  props: [
    'groups'
  ],
  data: function(){
    return {
     result: {
      error: false,
      message: '',
     },
     state: 'selecting',
     group: [],
     body: '{name}\n\n{message}\n', 
     subject: '',
     imageData: [null],
     isValidFile: [false],
     selectedFileName: ['ファイルを選択'],
    }
  },
  methods: {
    registReturnHandler: function(ret){
      this.result = ret;
      this.state = "uploaded";
      if(!this.result.error){
        document.registForm.reset();
      }
    },

    handleRegister: function(){
      if(document.getElementById("attachmentfile").files.length > 0) {
        var file = document.getElementById("attachmentfile").files[0];
        this.uploadFile(file);
      }else{
        this.result.message = "メールを送信しています・・・";
        this.state = "uploading";
        google.script.run
          .withSuccessHandler(this.registReturnHandler)
          .withFailureHandler(this.registReturnHandler).processSendEmail({"group":this.group,"subject":this.subject,"body":this.body},null,null);
      }
    },
    uploadFile: function(file) {
     var self = this;
      var reader = new FileReader();
      reader.onload = function() {
        var reader_result = reader.result;
        var file_name = file.name;
        self.result = {"message":"添付ファイルを送信しています・・・"}
        google.script.run
          .withSuccessHandler(self.registReturnHandler)
          .withFailureHandler(self.registReturnHandler).processSendEmail({"group":self.group,"subject":self.subject,"body":self.body}, reader_result, file_name);
      }
      this.result = {"message":"ファイルを読み込み中です・・・"}
      this.state = "uploading";
      reader.readAsDataURL(file);
    },
    
    fileSelected: function(e) {
      var files = e.target.files;
      var num = Number(e.target.getAttribute('data-number'));
      
      if(files.length > 0) {
        var file = files[0];
        var len = files.length;
        var size = file.size;
        var name = file.name;
        var content_type = file.type;
        this.selectedFileName[num] = name;

        var maxSize=1024 * 1024 * 25;
        if(len > 0 && size < maxSize){
          this.isValidFile.splice(num, 1, true);
        }else{
          this.isValidFile.splice(num, 1, false)
        }
        var reader = new FileReader();
        reader.onload = (event) => {
          this.imageData.splice(num, 1, event.target.result);
        };
        if(content_type.match('image.*')){
          reader.readAsDataURL(file);
        }
      }
    },
  },
  template: `
<div>
  <form @submit.prevent="handleRegister" name=registForm id=registForm>
    <div class="rounded-md shadow-sm space-y-4">
      <div>
        <label for="group" class="block text-gray-700 font-bold">グループ</label>
        <div class="space-x-2">
          <template v-for="(groupname, index) in groups" >
            <label>
              <input type="checkbox" v-bind:value="groupname" v-model="group" name="group">
              {{ groupname }}
            </label>
          </template>
        </div>
      </div>

      <div>
        <label for="subject" class="block text-gray-700 font-bold">件名</label>
        <input v-model="subject" id="subject" name="subject" type="text" required class="appearance-none relative block w-full px-3 py-2 border border-gray-300 placeholder-gray-500 text-gray-900 rounded focus:outline-none focus:ring-indigo-500 focus:border-indigo-500 focus:z-10 sm:text-sm" placeholder="件名">
      </div>

      <div>
        <label for="body" class="block text-gray-700 font-bold">本文</label>
        <textarea v-model="body" name="body" class="w-full h-32 border border-gray-300 text-gray-900 rounded" placeholder="本文" rows="10" cols="20"></textarea>
      </div>

      <div>
        <label class="inline-block align-baseline bg-blue-500 hover:bg-blue-400 focus:shadow-outline focus:outline-none text-white font-semibold py-4 px-4 my-2 rounded md:py-2 md:px-2 cursor-pointer">
          添付ファイル
          <input id="attachmentfile" ref="attachmentfile" data-number="0" name="attachmentfile" type="file" v-on:change="fileSelected($event)" class="hidden" />
        </label>
        <span class="px-2">{{selectedFileName[0]}}</span>
        <img :src="imageData[0]" v-if="imageData[0]" class="w-1/3">
      </div>

      <div>
        <button type="submit" class="group relative w-full flex justify-center py-2 px-4 border border-transparent text-sm font-medium rounded-md text-white bg-indigo-600 hover:bg-indigo-700 focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-indigo-500">
          メール送信
        </button>
      </div>
  
    </div>
  </form>
 
  <div v-if="state=='uploading'">
    <component-loading>{{ result.message }}</component-loading>
  </div>

  <div v-if="state=='uploaded'"> 
    <div v-if="result.error">
      <component-alert-error >{{ result.message }}</component-alert-error>
    </div>
    <div v-else>
      <component-alert-success >{{ result.message }}</component-alert-success>
      <div>
        {{result.sended.length}}件送信しました。本日残り{{ result.quota }}件送信可能です。
      </div>
      <div class="border overflow-auto w-full h-32 text-sm">
        <div v-for="(to, index) in result.sended">{{to}}</div>
      </div>
    </div>
  </div>
</div>
`
});

var app = new Vue({
  el: '#app',
  data: {
    page: 'component-mailform',
    groups: <?!= getGroups(); ?>,
  },
  methods: {
    onPageChange: function(page){
      this.page = page;
    },
    closeSidebar: function(){
      google.script.host.close();
    }
  },
});
</script>
  </body>
</html>

「無題のプロジェクト」という名前をわかりやすい名前に変更し、「プロジェクトを保存」ボタンをクリックしてスクリプトを保存します。

送信者名を変更する

送信者名を指定しないとメールアドレスが送信者名として表示されますが、名前で表示したい場合は「コード.gs」内の下記の行を変更して送信者名を記入してください。

//送信者名を表示させたい場合は記入
var senderName = '';

//例として田中太郎を送信者名としたい場合
var senderName = '田中太郎';

送信者名を指定した場合としない場合の見え方の違い

指定すると受信者が誰からメールを受け取ったのかわかりやすくなります。

送信元メールアドレスを変更したい場合

送信元メールアドレスは後ほど説明するスクリプト実行者のGmailアドレスとなりますが、Gmailの設定でメールエイリアスの設定がされている場合は、その別のアドレスに設定することができます。

//送信元メールアドレスを変更する場合は記入
//記入したアドレスは実行するGoogleアカウントのエイリアスとして設定されている必要があります
var senderMail = '';

//送信元メールアドレスを「sender@example.com」にしたい場合
var senderMail = 'sender@example.com';

他人のメールアドレスを名乗れないように、送信元メールアドレスに設定できるのは、Gmailでメールエイリアスの設定がなされているアドレスのみです。

メールエイリアスの追加はGmailの設定画面で「アカウントとインポート」タブを開き、
名前:の欄の「他のメール アドレスを追加」リンクから、送信元として使用したい別のメールアドレスを追加してください。

スクリプトの実行

アクティブなシートに入力されているアドレスリストへ送信しますので、あらかじめ送りたいリストが記録されているシートを開いた状態で操作してください。

  1. Googleスプレッドシートを再読み込みします。
  2. メニューに「メール」が追加されているので、「メール」→「一斉送信」をクリックします。
  3. 初回の場合はスクリプトにGoogleアカウントへのアクセスを許可する操作を行います。

初回のアクセス許可

1.「承認が必要」のダイアログで「続行」をクリックします。

2.「アカウントの選択」で、送信元メールアドレスのGoogleアカウントを選択します。リストにない場合は「別のアカウントを使用」より送信元メールアドレスのGoogleアカウントでログインを行ってください。

3.「このアプリはGoogleで確認されていません」と表示が出るので、「詳細」をクリックし、「(先ほど指定したプロジェクト名)に移動」をクリックします。

4.「(プロジェクト名)がGoogleアカウントへのアクセスをリクエストしています」と表示されるので「許可」をクリックします。

5.許可が完了したら再度「メール」→「一斉送信」をクリックします。

6.サイドバーに「一斉送信」フォームが表示されるので、送信するグループにチェックを入れ、件名、本文、必要に応じて添付ファイルを指定して「メール送信」をクリックします。

グループで「全て」にチェックを入れた場合は、未記入も含めたすべてのグループが対象となります。

件名と本文中で使用できるタグ

件名や本文中に下記のタグがある場合、それぞれ置き換わって送信されます。

タグ置換対象
{name}名前+敬称(C列+D列)
{message}個別メッセージ(F列の内容。それぞれのアドレスごとに変えたい文言がある場合)

実行時に表示されるメッセージ

〇件のメールを送信しました。本日残り〇件送信可能です。

メール送信が完了した際に表示されます。実際に送信したメール内容はGmailを開いて送信済みフォルダから確認できます。

エラーメッセージが表示される場合

送信元メールアドレス〇〇はメールエイリアスとして設定されていません。

指定した送信元メールアドレスがメールエイリアスとして設定されていない場合に表示されます。スクリプトを実行しているGoogleアカウントのGmailのメールエイリアスに、送信元メールアドレスを追加してください。

次のメールアドレスが正しくありません:メールアドレス

送信先リストに含まれるメールアドレスの形式が正しくない場合に表示されます。リストの中のメールアドレスをご確認ください。

一日の送信可能数を超えるため送信できません。本日残り可能送信数:〇〇

GASからのGmail送信には、アカウントごとに1日当たり100通までという制限があります。そのため、送信しようとしているメールの通数が、その日の残り可能送信数を超えている場合は1通も送信せずこのメッセージが表示されます。

送信対象が残り可能送信数以下になるように調整してから再度送信を行ってください。対象が100を超える場合は最初から送信できないため、100以内にしてからお試しください。

送信先がありません。

シート中に有効な送信先メールアドレスがない場合に表示されます。

  • A列の送信フラグが「1」の行がない(半角数字)
  • B列のグループが未記入もしくは、選択されたグループに属するリストが存在しない

Exception: 無効なメール:

メールアドレスがRFC 5321に準拠していない形式の場合Gmailでは送信できません。

宛先欄(To:)の敬称

宛名欄(いわゆるTo:)は「名前 <メールアドレス>」の形で送信されますが、「名前+敬称 <メールアドレス>」やメールアドレスのみに変更することができます。

スクリプトの111行目付近にある下記の行を変更することでTo:欄を変更できます。

to: userData["name"]+" <"+userData["mail"]+">",

宛先の名前 <メールアドレス>

デフォルトでこの形で送信されます。

メールアドレスのみにする

to: userData["mail"],

名前+敬称 <メールアドレス> の形にする

例えば「様」をつけたい場合は次のようにします。

to: userData["name"]+userData["gender"]+" <"+userData["mail"]+">",

使用技術

  • Google Apps Script
  • Vue.JS
  • tailwindcss

その他

使い方や設定方法が分からない場合などはお問い合わせください。カスタマイズが必要な場合もお問い合わせください。お見積りさせていただきます。

Comments

タイトルとURLをコピーしました