Outlook差し込みメール作成のGAS版の作成
最近職場のメールがMicrosoftExchangeサーバーからGoogleのGmailに変更になりました。
MicrosoftExchangeを使っていたころはOutlookを使用していたので、ExcelやOutlookのVBAでメールを作成して業務を効率化していましたが、その辺りのツールが全部使えなくなってしまいました。
会社セキュリティの都合でSMTP・IMAPでのOutlookからの接続もダメで何とかGmailでも同じようにメールを作成できるようにGAS(GoogleAppsScript)の勉強を始め、色々なツールを準備しています。
以前にこちらでExcelVBAを用いてOutlookのメールを大量に作成するVBAを作成していましたが、今回はこのツールと同じようなことができるツールをGASとGoogleスプレッドシートを用いて作成しましたのでご紹介します。
GAS(GoogleAppsScript)とVBAの違いについて
①GASはGoogleのサーバー上で実行される
GASはVBAと異なり、Googleのサーバー上で実行されます。
そのため、必要な情報はすべてGoogleドライブなどのサーバー上に存在する必要があります。
メールの送信においては「添付ファイル」を参照する際に問題になります。
VBAでメールを送る際、添付ファイルは、通常ローカルにあるものを呼び出すことが多いと思いますが、GASにおいてはローカルの参照ができません。
そのため今回は以下で詳細を紹介しますが、添付ファイルはGoogleドライブ上に格納しておくことにしました。
②GAS実行時間の制限
VBAでは実行されている時間はどれくらいかかっても問題なかったのですが、GASでは1回のスクリプト実行が5分までと決まっています。
これはGoogleのサーバーの負荷を軽減するための処理だと思いますが、VBAよりもさらに開発の際に実行時間が短縮されるように工夫が必要になります。
③メールの送信上限数
スクリプトでメールを送信する回数に1アカウント1日2,000件程度の上限が設けられています。
こちらはスパムなどの対策だと思いますが、大量にメールを送る際には複数のアカウントから送るなど注意が必要です。
スポンサーリンク
GASを実行する際の事前準備
①Googleドライブへのメール添付ファイルのアップロード
Googleドライブの中で添付ファイルの格納場所を決めてアップロードしておきます。
今回は「添付」というフォルダを作成して添付ファイルを格納しています。
この添付ファイルを格納したフォルダ名は②のスプレッドシートに記載することで添付ファイルの格納場所を指定する仕組みになっています。
添付ファイルはファイル名で認識するので、同じ名前のファイルは格納しないようにします。
②スプレッドシートの作成
まず本文・件名・宛先リストなどの必要な情報をExcelの代わりに記載するスプレッドシートを用意します。
前回ご紹介しているOutlookでのメール送信と同様に、こちらは本文・件名のテンプレートおよびリストを用意しておき、差し込み印刷のような要領でちょっとずつ本文や件名の内容、宛先などを変更したメールを大量に送信することを目的としています。
7行目以降記入した行数の分だけメールが自動作成されます。
記載内容としては以下の通りです。
B2・B3セルには件名と本文のテンプレートを記載します。
メールごとに変更したい部分は<>でくくって記載し、<>内の文言を(3)の変更内容の通りに記載します。
サンプルの例では<所属コード>、<所属名>、<宛名>をメールごとの変更内容として設定しています。
複数の宛先・CC・BCCを付けたい場合カンマ区切りで記載します。
上のサンプルの例ですと本文の中に書かれている<所属コード>、<所属名>、<宛名>がE列、F列、G列の内容に置き換わるように設定しています。
例えば<所属コード>では6行目に所属コードと書かれているE列に書かれている「10001340」「10001733」と置き換えられます。
6行目の内容を変更すれば自由に変換する場所を設定することができ、今回のファイルでは最大でE~S列分の15個変更箇所を用意できます。
①で添付ファイルを格納したフォルダ名をT6セルに記載します。
また、メールごとに添付したいファイルのファイル名をT列の7行目以降に記入します。
複数ファイルを添付したい場合、改行してファイル名を記載します。
③①の添付ファイル格納フォルダと②スプレッドシートを同じフォルダに格納する
①の添付ファイルを格納しているフォルダと②で作成した必要な情報を記載したスプレッドシートは同じフォルダ内に入れておきます。
作成したGASのコード
今回作成したコードは以下の通りです。
添付ファイルをドライブから参照するステップがかなり複雑になったため、別の関数(getAttachmentId)を作成して分けて作成しています。
ファイル添付するにはファイルのIDが必要になります。
ドライブの中に作成した添付ファイルを格納しているファイルを順に取得して、ファイル名が一致している場合にファイルのIDを取得してスプレッドシートのU列に記入します。
メールを作成する際にはスプレッドシートに記載したIDから取得します。
function getAttachmentId() { //添付ファイルの名前からIDを取得してスプレッドシートに記載するプログラム let ss = SpreadsheetApp.getActiveSpreadsheet(); let fileId = ss.getId(); let sheet = ss.getActiveSheet(); let row = 1; const BValues = sheet.getRange('B:B').getValues(); //A列の値を全て取得 const LastRow = BValues.filter(String).length + 3 ; //空白の要素を除いた長さを取得 +3は本文・件名などの入力行数分 //const firstValues = targetsheet.getRange('1:1').getValues(); //1行目の値を全て取得 //const LastColumn = firstValues[0].filter(String).length; //空白の要素を除いた長さを取得 const LastColumn = 21; var data = sheet.getRange(1,1,LastRow,LastColumn).getValues(); var displayData = sheet.getRange(1,1,LastRow,LastColumn).getDisplayValues(); let FolderIterator = DriveApp.getFileById(fileId).getParents(); let parentFolder = FolderIterator.next(); let subFolders = parentFolder.getFolders(); //子フォルダ一覧を取得 let attachFolderId = ""; while ( subFolders.hasNext() ){ var subFolder = subFolders.next(); if (subFolder.getName() == displayData[5][19]){ //添付格納フォルダ名の取得 attachFolderId = subFolder.getId(); //Logger.log(attachFolderId); break; }; }; //添付ファイルのIDは一度全部削除 for(var i = 6; i <= LastRow - 1 ; i++) { sheet.getRange(i + 1, 21).setValue(''); } if (attachFolderId != '') { //対象フォルダがあればファイルを検索 let files = DriveApp.getFolderById(attachFolderId).getFiles(); while (files.hasNext()) { let file = files.next(); let filename = file.getName(); let fileId = file.getId(); let ssfilenames = ''; for(var i = 6; i <= LastRow - 1 ; i++) { if (displayData[i][19] != '') { ssfilenames = displayData[i][19].split('\n') //ssに記載された添付ファイル名を改行ごとに区切ってssfilenamesに格納 for(var k = 0; k <= ssfilenames.length - 1 ; k++) { if (ssfilenames[k] == filename) { if (sheet.getRange(i + 1, 21).getValue() != '') { sheet.getRange(i + 1, 21).setValue(sheet.getRange(i + 1, 21).getValue() + '\n' + fileId); //複数ある場合改行して結合 } else { sheet.getRange(i + 1, 21).setValue(fileId); //最初の1個目は改行なし } } } } } } } } function createDraft_htmlMail() { getAttachmentId(); //添付ファイルの名前からIDを取得してスプレッドシートに記入 let ss = SpreadsheetApp.getActiveSpreadsheet(); let fileId = ss.getId(); let sheet = ss.getActiveSheet(); let row = 1; const BValues = sheet.getRange('B:B').getValues(); //B列の値を全て取得 const LastRow = BValues.filter(String).length + 3 ; //空白の要素を除いた長さを取得 +3は本文・件名などの入力行数分 const LastColumn = 22; let data = sheet.getRange(1,1,LastRow,LastColumn).getValues(); let displayData = sheet.getRange(1,1,LastRow,LastColumn).getDisplayValues(); for(var i = 6; i <= LastRow - 1 ; i++) { if (displayData[i][0] != '') { let mailSubject = displayData[1][1]; let mailBody = displayData[2][1]; for(var k = 4; k <= LastColumn - 4 ; k++) { if (displayData[5][k] != '') { mailSubject = mailSubject.replace('<'+ displayData[5][k] + '>',displayData[i][k]); mailBody = mailBody.replace('<'+ displayData[5][k] + '>',displayData[i][k]); } } console.log(mailBody); var options = { cc: displayData[i][2], bcc: displayData[i][3], htmlBody: mailBody.replace(/\n/g,'
'), }; if (displayData[0][1] != '') { options['from'] = displayData[0][1] } if (displayData[i][20] != '') { let attachments = displayData[i][20].split('\n'); //IDの文字列を配列に格納 let arr_attacchments = [] //ファイルを配列に格納するための宣言 for(var k = 0; k <= attachments.length - 1 ; k++) { //console.log(attachments[k]); arr_attacchments.push(DriveApp.getFileById(attachments[k]).getBlob()) //ファイルを配列に格納 } options['attachments'] = arr_attacchments //ファイルを添付 } console.log(options); GmailApp.createDraft(displayData[i][1],mailSubject,mailBody,options); //to , Subject , Body の順 } } } function send_Draft() { var drafts = GmailApp.getDrafts(); // 下書きフォルダ内の最初の下書きメッセージ for(var i = drafts.length - 1 ; i >= 0 ; i--) { Logger.log(drafts[i].getMessage().getSubject()); var msg = drafts[i].send(); } }
下書きに入ったメールは内容を確認後、send_Draftのスクリプトを実行することですべてのメールを送信することができます。
下書きに入ったメールをすべて送信するので、他の下書きが入っていないことを確認してから実行する必要があります。
コメント