labnol/mail-merge-gmail.js

## mail-merge-gmail.js
/*
* 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);
}
	/*
	* 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);
	}