以下の記事を参照。
Created
January 7, 2020 08:20
-
-
Save W-Yoshida/d3f538387b0616bc0f8cda3ba93d527e to your computer and use it in GitHub Desktop.
差し込みメール配信を実行するGAS
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 ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheets()[0]; | |
//実行メニューを作成 | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
var menu = ui.createMenu("GAS実行"); | |
menu.addItem("メール送信実行", "sendMergeEmail"); | |
menu.addToUi(); | |
} | |
function sendMergeEmail(){ | |
var lastColum = sheet.getLastColumn(); | |
var lastRow = sheet.getLastRow(); | |
var startRow = 6; | |
var numRows = lastRow - startRow + 1; | |
var dataRange = sheet.getRange(startRow, 1, numRows, lastColum); | |
var data = dataRange.getValues(); | |
var strFrom = sheet.getRange(1,2).getValue(); | |
var docID = sheet.getRange(2,2).getValue(); | |
var attachementID = sheet.getRange(3,2).getValue(); | |
//テンプレートテキストの取得 | |
var docTemplate = DocumentApp.openById(docID); | |
var strTemplate = docTemplate.getBody().getText(); | |
for (var i = 0; i < data.length; ++i) { | |
var row = data[i]; | |
row.rowNumber = i + startRow; | |
//Result列がブランクであれば処理を実行 | |
if (!row[7]) { | |
var result = ""; | |
try | |
{ | |
var strVal1 = row[4]; | |
var strVal2 = row[5]; | |
var strVal3 = row[6]; | |
//テンプレートテキスト内の変数を置換 | |
var strBody = strTemplate.replace("\{VALUE1\}",strVal1).replace("\{VALUE2\}",strVal2).replace("\{VALUE3\}",strVal3); | |
var strTo = row[0] | |
var strCc = row[1] | |
var strBcc = row[2] | |
var strSubject = row[3] | |
var options = {}; | |
options.cc = strCc; | |
options.bcc = strBcc; | |
options.from = strFrom; | |
//添付ファイル指定がある場合はoptionsに追加 | |
if(attachementID){ | |
var attachment = DriveApp.getFileById(attachementID); | |
options.attachments = attachment | |
} | |
//メール送信実行 | |
GmailApp.sendEmail(strTo,strSubject,strBody,options); | |
result = "Success"; | |
}catch(e){ | |
result = "Error:" + e; | |
} | |
//実行結果をResult列にセット | |
sheet.getRange(row.rowNumber, lastColum).setValue(result); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment