Source Code for Mail Merge

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);
}

Hello Amit, thank you for taking the time to respond to my comment. I appreciate the recommendation. ᐧ

…

On Wed, Aug 14, 2019 at 12:41 AM Amit Agarwal ***@***.***> wrote: Please switch to the Gmail Mail Merge <https://chrome.google.com/webstore/detail/mail-merge-with-attachmen/nifmcbjailaccmombpjjpijjbfoicppp> addon for Google Sheets. 🔥 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/1907310?email_source=notifications&email_token=AMT6XP5WJFGS7Z3PT32QEF3QELI3FA5CNFSM4ID5COH2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFW6TW#gistcomment-2997563>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AMT6XP6IG3QA5R6ZA32BKA3QELI3FANCNFSM4ID5COHQ> .

-- Warm regards, *Kaven IzuharaRecruitment Specialist - Pharmaceutical Industry* *Titan Consulting K.K.* * <https://www.linkedin.com/company-beta/3782339/>* Address: ASAX Hiroo building 6F, 1-3-14 Hiroo, Shibuya-ku, Tokyo 150-0012 Phone: *03-4550-2846* Linkedin: https://ph.linkedin.com/in/kaven-izuhara-771187a2 Webpage: *http://www.titanconsulting.jp/ <http://www.titanconsulting.jp/whoweare.html>* Map: *http://www.titanconsulting.jp/jp/contact.html <http://www.titanconsulting.jp/jp/contact.html>*