Created
May 9, 2019 08:38
-
-
Save prasanthmj/cfe0795f0e3c61feefac2954343739ef to your computer and use it in GitHub Desktop.
Send emails with attachment from a Google Sheet using Google Apps Script. Read full article here: http://blog.gsmart.in/google-apps-script-send-email-with-attachment/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function onOpen() | |
{ | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Automation') | |
.addItem('send PDF Form', 'sendPDFForm') | |
.addItem('send to all', 'sendFormToAll') | |
.addToUi(); | |
} | |
function sendPDFForm() | |
{ | |
var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); | |
sendEmailWithAttachment(row); | |
} | |
function sendEmailWithAttachment(row) | |
{ | |
var filename= 'fillable-form-5516.pdf'; | |
var file = DriveApp.getFilesByName(filename); | |
if (!file.hasNext()) | |
{ | |
console.error("Could not open file "+filename); | |
return; | |
} | |
var client = getClientInfo(row); | |
var template = HtmlService | |
.createTemplateFromFile('email-template'); | |
template.client = client; | |
var message = template.evaluate().getContent(); | |
MailApp.sendEmail({ | |
to: client.email, | |
subject: "The form to fill", | |
htmlBody: message, | |
attachments: [file.next().getAs(MimeType.PDF)] | |
}); | |
} | |
function getClientInfo(row) | |
{ | |
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); | |
var values = sheet.getRange(row,1,row,3).getValues(); | |
var rec = values[0]; | |
var client = | |
{ | |
first_name: rec[0], | |
last_name: rec[1], | |
email: rec[2] | |
}; | |
client.name = client.first_name+' '+client.last_name; | |
return client; | |
} | |
function sendFormToAll() | |
{ | |
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); | |
var last_row = sheet.getDataRange().getLastRow(); | |
for(var row=2; row <= last_row; row++) | |
{ | |
sendEmailWithAttachment(row); | |
sheet.getRange(row,4).setValue("email sent"); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have an excel sheet that has print area that i would like to send as an attachment. Any help