Gmail Mail Merge: Editing Script

[Noah_Kaplan]

I'm trying to setup a mail merge using a gmail docs script from http://www.labnol.org/software/mail-...h-gmail/13289/. So far, it works pretty well, but I want to edit it to include the following:

1) The script should start with "Dear Noah" as opposed to "Dear Noah_Kaplan"

2) I want to add my canned response to the script so that it always appears at the bottom of the e-mail.

3) Lastly, I want the outgoing mail to be saved as individual drafts in my drafts folder in gmail before it's sent out. That way, I can decide if I want to add a sentence or two to personalize each individual email draft.

Thanks in advance for your awesome help.

Best,
Noah

By the way, here's the script I want to edit:

function onOpen() {
var mySheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Step 1: Import Gmail Contacts", functionName: "importContacts"},
{name: "Step 2: Start Mail Merge", functionName: "sendEmail"},
{name: "Help / About", functionName: "showHelp"}];
mySheet.addMenu("Mail Merge", menuEntries);
}

function importContacts() {
var groupName = Browser.inputBox("Enter the name of your Gmail Contacts group here:");
var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var myContacts = ContactsApp.findContactGroup(groupName).getContacts();
for (i=0; i < myContacts.length; i++) {
var myContact = [[myContacts[i].getFullName(), myContacts[i].getPrimaryEmail(), "Pending"]];
mySheet.getRange(i+2, 1,1,3).setValues(myContact);
}
Browser.msgBox("You have successfully imported " + myContacts.length + " contacts from Gmail. Please proceed to Step 3.");
}

function sendEmail() {

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var newLine = "<br><br>";

var emailSubject = mySheet.getRange("F2").getValue();
var emailSalutation = mySheet.getRange("F3").getValue();

var emailLine1 = mySheet.getRange("F4").getValue() + newLine;
var emailLine2 = mySheet.getRange("F5").getValue()?
mySheet.getRange("F5").getValue() + newLine : "";
var emailLine3 = mySheet.getRange("F6").getValue()?
mySheet.getRange("F6").getValue() + newLine : "";
var emailLine4 = mySheet.getRange("F7").getValue()?
mySheet.getRange("F7").getValue() + newLine : "";
var emailLine5 = mySheet.getRange("F8").getValue()?
mySheet.getRange("F8").getValue() + newLine : "";

var emailBody = emailLine1 + emailLine2 + emailLine3 + emailLine4 + emailLine5;

var emailSignature = mySheet.getRange("F9").getValue() + newLine;
var emailYourName = mySheet.getRange("F10").getValue();
emailBody = emailBody + emailSignature + emailYourName + "<br>";

var emailReplyTo = mySheet.getRange("F11").getValue();

var myContacts = mySheet.getDataRange();
var myContact = myContacts.getValues();
for (i=1; i < myContact.length; i++) {
var person = myContact[i];
if (person[1] != "" && person[2] != "OK") {
var emailMsg = emailSalutation + " " + person[0] + "," + newLine + emailBody;
var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo};
MailApp.sendEmail(person[1], emailSubject, emailMsg , advancedArgs);
mySheet.getRange(i+1,3).setValue("OK");
}
}
SpreadsheetApp.flush();
}

function showHelp() {
Browser.msgBox("With Mail Merge, you can send personalized email messages to your Gmail contacts in two easy steps. For help, visit http://labnol.org/?p=13289 or send me a tweet @labnol.");
}​

[jsvlad]

I am looking to mail cover letter with resume attached

Company Name
Address
Phone
Website
Email

Subject: Resume - Job Position for Company Name

Hello my name is JOB APPLICANT..

You should hire me..

Attached is my resume...


From
Job applicant

Please show how to
1. Add Resume attachment
2. Quick List contacts: I have all contact info in excel.. organized by

Company Name
Address
Phone
Website
Email

How do i quickly import?

[Philip M]

jsvlad - Please do not hijack someone else's thread. In any case what you are looking for is nothing to do with Javascript. You should use MS WORD and mailmerge.

Noah - When posting here please help us to help you by following the posting guidelines and wrapping your code in CODE tags. This means use the octothorpe or # button on the toolbar which will insert the tags. You can (and should) edit your previous post.

[swenpro]

Hi all, Just saw this post and I have the question as Noah. Did anyone ever figure this out?

Thanks,
Greg

[IT4SmallBiz]

If you're looking for a script for mail merging Gmail and Google docs, we've created one which works similarly to a Microsoft mail merge.

See

http://www.it4smallbusiness.co.uk/we...ogle-mailmerge

The sheets and script are free to use/copy/modify.

[IT4SmallBiz]

We've updated the script above to handle large numbers of emails rather better. In our testing, up to 100 recipients works fine. And it also now marks each email as successful or not, so you can re-send any which fail.