-
-
Save sam-ple/77d1fc8e3e7529ab30fa12dca4e0cbd4 to your computer and use it in GitHub Desktop.
メール一括配信
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var ssID = SpreadsheetApp.getActiveSpreadsheet(); | |
// トリガーを設定 | |
function setTrigger() { | |
var sh_Trigger = ssID.getSheetByName("★トリガー設定"); | |
var year = sh_Trigger.getRange(2, 1).getValue(); | |
var month = sh_Trigger.getRange(3, 1).getValue(); | |
var day = sh_Trigger.getRange(4, 1).getValue(); | |
var hour = sh_Trigger.getRange(5, 1).getValue(); | |
var minute = sh_Trigger.getRange(6, 1).getValue(); | |
var triggerDay = new Date(year, (month - 1), day, hour, minute, 0, 0); | |
ScriptApp.newTrigger("sendMail").timeBased().at(triggerDay).create(); | |
} | |
// トリガーを削除する関数(消さないと残る) | |
function deleteTrigger() { | |
var triggers = ScriptApp.getProjectTriggers(); | |
for(var i=0; i < triggers.length; i++) { | |
if (triggers[i].getHandlerFunction() == "sendMail") { | |
ScriptApp.deleteTrigger(triggers[i]); | |
} | |
} | |
} | |
// メール一括送信処理 | |
function sendMail() { | |
deleteTrigger(); | |
var sh_List = ssID.getSheetByName("★宛先リスト"); | |
var start = sh_List.getRange(1, 3).getValue(); // 処理を開始する行番号を取得 | |
var numRows = sh_List.getDataRange().getNumRows(); // 処理を終了する行番号を取得 | |
var sh_Content = ssID.getSheetByName("★メール本文"); | |
var name = sh_Content.getRange(3, 2).getValue(); //差出人を取得 | |
for (var i=start; i<=numRows; i++) { | |
var subject = sh_Content.getRange(3, 3).getValue(); // メール件名を取得 | |
var body = sh_Content.getRange(3, 4).getValue(); // メール本文を取得 | |
// 対象行の差込データを取得 | |
var repData1 = sh_List.getRange(i, 6).getValue(); | |
var repData2 = sh_List.getRange(i, 7).getValue(); | |
var repData3 = sh_List.getRange(i, 8).getValue(); | |
// 対象行の差込データを置換 | |
var subject = subject.replace(/{差込1}/g,repData1).replace(/{差込2}/g,repData2).replace(/{差込3}/g,repData3); | |
var body = body.replace(/{差込1}/g,repData1).replace(/{差込2}/g,repData2).replace(/{差込3}/g,repData3); | |
// 対象行の宛先を取得 | |
var to = sh_List.getRange(i, 2).getValue(); | |
var cc = sh_List.getRange(i, 3).getValue(); | |
var bcc = sh_List.getRange(i, 4).getValue(); | |
// メールを送信 | |
MailApp.sendEmail(to,subject,body,{name:name,cc:cc,bcc:bcc}); | |
// 配信状態を設定 | |
done = "配信完了" + Utilities.formatDate(new Date(), "Asia/Tokyo", "MMddHHmm"); | |
sh_List.getRange(i, 5).setValue(done); | |
} | |
// 終了確認ダイアログを表示 | |
//Browser.msgBox("確認", "メール一斉送信が完了しました。", Browser.Buttons.OK); | |
}; | |
// スプレッドシートを開いた時にメニューを作成 | |
function onOpen() { | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
//メニュー配列 | |
var myMenu=[ | |
{name: "トリガー設定", functionName: "setTrigger"}, | |
null, | |
{name: "メール一斉送信", functionName: "sendMail"}, | |
]; | |
sheet.addMenu("スクリプト実行", myMenu); | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment