public
Last active — forked from ligthyear/mass_emailer.js

GAS Source Code for Mail Merge

  • Download Gist
gistfile1.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
/*
* Added Remaining Quota Notifications
* Renamed a few menu options
* Forked from gist: 1838132 by ligthyear
*/
 
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Start Mail Merge", functionName: "fnMailMerge"}];
ss.addMenu("Mail Merge", menuEntries);
}
 
function fnMailMerge() {
var myapp = UiApp.createApplication().setTitle('Mail Merge').setHeight(120).setWidth(300);
var top_panel = myapp.createFlowPanel();
top_panel.add(myapp.createLabel("Please select a Gmail draft as your Mail Merge template"));
var lb = myapp.createListBox(false).setId('msg').setWidth(250).setName('message').setVisibleItemCount(1);
var threads = GmailApp.search('is:draft', 0, 10);
if (threads.length === 0) {
Browser.msgBox("There are no templates in your Gmail. Please save a template as a draft message in your Gmail mailbox and re-run Mail Merge");
return;
}
for (var i = 0; i < threads.length; i++) {
lb.addItem("" + threads[i].getFirstMessageSubject(), threads[i].getMessages()[0].getId());
}
top_panel.add(lb);
top_panel.add(myapp.createLabel("Sender's Name (this will show in the FROM field)"))
var name_box = myapp.createTextBox().setName("name").setWidth(250);
top_panel.add(name_box);
var ok_btn = myapp.createButton("Start Mail Merge");
top_panel.add(ok_btn);
myapp.add(top_panel);
var handler = myapp.createServerClickHandler('startMailMerge').addCallbackElement(lb).addCallbackElement(name_box);
ok_btn.addClickHandler(handler);
SpreadsheetApp.getActiveSpreadsheet().show(myapp);
}
 
function startMailMerge(e) {
var mail = GmailApp.getMessageById(e.parameter.message);
fnSendMails(mail, e.parameter.name);
var app = UiApp.getActiveApplication();
app.close();
return app;
}
 
function fnSendMails(mail, name) {
var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection(),
data = ws.getValues(),
attrs = data.shift(),
count = 0,
mail, bodyCopy, attachments, subjectCopy, idx, line_idx, mail_idx, line;
mail_idx = attrs.indexOf('email');
if (mail_idx === -1) {
Browser.msgBox("Mail Merge canceled: At least one column should be labeled as 'email'");
return;
}
 
attachments = mail.getAttachments();
 
for (line_idx in data) {
line = data[line_idx];
bodyCopy = mail.getBody();
subjectCopy = mail.getSubject();
for (idx in attrs) {
bodyCopy = bodyCopy.replace("{{" + attrs[idx] + "}}", line[idx]);
subjectCopy = subjectCopy.replace("{{" + attrs[idx] + "}}", line[idx]);
}
 
count += 1;
GmailApp.sendEmail(line[mail_idx], subjectCopy, bodyCopy, {
htmlBody: bodyCopy,
name: name,
attachments: attachments
})
}
 
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.toast(count + " mails delivered in this batch", "Status", 3);
ss.toast("You can send " + MailApp.getRemainingDailyQuota() + " more emails today with your remaining Gmail quota", "Quota", -1);
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.