Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

commented Apr 25, 2016

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?

@Terminator-X

This comment has been minimized.

Copy link

commented Feb 7, 2019

Sorry, it is broken, since UIapp is unsupported anymore. Must be replaced with HTML service...

@relativegnosis

This comment has been minimized.

Copy link

commented Jul 16, 2019

Unfortunately this script is no longer working due to this error: UiApp has been deprecated. Please use HtmlService instead.
Details

Any chance this can be updated? This tool has been very handy for me ever since. It'd be so sad if it can't be fixed. Looking forward to some update

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.