Skip to content

Instantly share code, notes, and snippets.

@labnol

labnol/mail-merge-gmail.js

Forked from gnunicorn/mass_emailer.js
Last active August 14, 2019 00:50
Show Gist options
  • Save labnol/1907310 to your computer and use it in GitHub Desktop.
Save labnol/1907310 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. labnol renamed this gist Aug 13, 2019. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.js → mail-merge-gmail.js
    View file
    File renamed without changes.
  2. labnol revised this gist Sep 27, 2017. No changes.
  3. labnol revised this gist Mar 6, 2012. 1 changed file with 12 additions and 12 deletions.
    24 changes: 12 additions & 12 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,13 @@
    * Forked from gist: 1838132 by ligthyear
    */

    function start_mailer() {
    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"));
    @@ -29,21 +35,21 @@ function start_mailer() {
    top_panel.add(ok_btn);
    myapp.add(top_panel);

    var handler = myapp.createServerClickHandler('callback').addCallbackElement(lb).addCallbackElement(name_box);
    var handler = myapp.createServerClickHandler('startMailMerge').addCallbackElement(lb).addCallbackElement(name_box);
    ok_btn.addClickHandler(handler);

    SpreadsheetApp.getActiveSpreadsheet().show(myapp);
    }

    function callback(e) {
    function startMailMerge(e) {
    var mail = GmailApp.getMessageById(e.parameter.message);
    _send_mails(mail, e.parameter.name);
    fnSendMails(mail, e.parameter.name);
    var app = UiApp.getActiveApplication();
    app.close();
    return app;
    }

    function _send_mails(mail, name) {
    function fnSendMails(mail, name) {
    var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection(),
    data = ws.getValues(),
    attrs = data.shift(),
    @@ -78,10 +84,4 @@ function _send_mails(mail, name) {
    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);
    }

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Start Mail Merge", functionName: "start_mailer"}];
    ss.addMenu("Mail Merge", menuEntries);
    }
    }
  4. labnol revised this gist Mar 6, 2012. 1 changed file with 20 additions and 31 deletions.
    51 changes: 20 additions & 31 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,28 +1,17 @@
    /*
    * Mass Emailer Google Spreadsheet script.
    *
    * Changelog:
    *
    * 16. Feb. 2012
    * - released the first version to public
    * 15. Feb. 2012
    * - add possibility to change the name
    * - add full-blown UI
    * 10. Feb. 2012
    * - add Support to load Mail from Draft
    */
    * Added Remaining Quota Notifications
    * Renamed a few menu options
    * Forked from gist: 1838132 by ligthyear
    */

    function start_mailer() {
    var myapp = UiApp.createApplication().setTitle('Mass Emailer').setHeight(120).setWidth(300);
    var top_panel = myapp.createFlowPanel();

    top_panel.add(myapp.createLabel("Please select the draft you'd like to be sent"));

    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("Please save your template as a draft in your gmail account!");
    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;
    }

    @@ -32,11 +21,11 @@ function start_mailer() {

    top_panel.add(lb);

    top_panel.add(myapp.createLabel("Name to send from (optional)"))
    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('Send mails now');
    var ok_btn = myapp.createButton("Start Mail Merge");
    top_panel.add(ok_btn);
    myapp.add(top_panel);

    @@ -52,8 +41,7 @@ function callback(e) {
    var app = UiApp.getActiveApplication();
    app.close();
    return app;

    }
    }

    function _send_mails(mail, name) {
    var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection(),
    @@ -64,7 +52,7 @@ function _send_mails(mail, name) {

    mail_idx = attrs.indexOf('email');
    if (mail_idx === -1) {
    Browser.msgBox("Canceled: At least one row must be called 'email'");
    Browser.msgBox("Mail Merge canceled: At least one column should be labeled as 'email'");
    return;
    }

    @@ -78,21 +66,22 @@ function _send_mails(mail, name) {
    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
    })
    }

    Browser.msgBox(count + " Mails send");
    }


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

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Send Mass Email", functionName: "start_mailer"}];
    ss.addMenu("Mass-Mailer", menuEntries);
    }
    var menuEntries = [ {name: "Start Mail Merge", functionName: "start_mailer"}];
    ss.addMenu("Mail Merge", menuEntries);
    }
  5. @gnunicorn gnunicorn revised this gist Feb 16, 2012. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,17 @@
    /*
    * Mass Emailer Google Spreadsheet script.
    *
    * Changelog:
    *
    * 16. Feb. 2012
    * - released the first version to public
    * 15. Feb. 2012
    * - add possibility to change the name
    * - add full-blown UI
    * 10. Feb. 2012
    * - add Support to load Mail from Draft
    */

    function start_mailer() {
    var myapp = UiApp.createApplication().setTitle('Mass Emailer').setHeight(120).setWidth(300);
    var top_panel = myapp.createFlowPanel();
  6. @gnunicorn gnunicorn created this gist Feb 15, 2012.
    84 changes: 84 additions & 0 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,84 @@
    function start_mailer() {
    var myapp = UiApp.createApplication().setTitle('Mass Emailer').setHeight(120).setWidth(300);
    var top_panel = myapp.createFlowPanel();

    top_panel.add(myapp.createLabel("Please select the draft you'd like to be sent"));

    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("Please save your template as a draft in your gmail account!");
    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("Name to send from (optional)"))
    var name_box = myapp.createTextBox().setName("name").setWidth(250);
    top_panel.add(name_box);

    var ok_btn = myapp.createButton('Send mails now');
    top_panel.add(ok_btn);
    myapp.add(top_panel);

    var handler = myapp.createServerClickHandler('callback').addCallbackElement(lb).addCallbackElement(name_box);
    ok_btn.addClickHandler(handler);

    SpreadsheetApp.getActiveSpreadsheet().show(myapp);
    }

    function callback(e) {
    var mail = GmailApp.getMessageById(e.parameter.message);
    _send_mails(mail, e.parameter.name);
    var app = UiApp.getActiveApplication();
    app.close();
    return app;

    }

    function _send_mails(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("Canceled: At least one row must be called '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
    })
    }

    Browser.msgBox(count + " Mails send");
    }



    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Send Mass Email", functionName: "start_mailer"}];
    ss.addMenu("Mass-Mailer", menuEntries);
    }