Create a gist now

Instantly share code, notes, and snippets.

GAS Source Code for Mail Merge
/*
* 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);
}
@haripetrov

For some reason I receive "Error encountered: Cannot read property "1" from null." if I have inline picture (jpg, png, it doesn't matter) in the body of the message. Any suggestions how to fix that?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment