This example shows you how to send email from a Google Sheet with an attachment.
Suppose we have a Google Sheet with all the clients and their email addresses. We need to get a PDF form filled and signed by each of these clients. The plan is to send the PDF form as attachment to each client and then ask them to fill, sign and send it back.
Here is the Google Sheet.
You can make a copy of the sample sheet here.
Add a menu item to the Google Sheet
First, let us add a menu item to the Google Sheet for this part of the automation.
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Automation')
.addItem('send PDF Form', 'sendPDFForm')
.addToUi();
}
function sendPDFForm()
{
var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
sendEmailWithAttachment(row);
}
function sendEmailWithAttachment(row)
{
}
do File → Save and close and re-open the Google Sheet.
You should now see the new Menu item.
Upload the file
The next step is to upload the PDF file to your Google Drive. Go to drive.google.com. You may want to create a new folder to keep the files. Then upload the file:
Give a unique name to the file. We will be using the name to fetch the file and attach to the email.
Create an email template
Let us now create an HTML file that will serve as the email template. Do File → New and select HTML.
This is a simple email template. You have to customize per your needs.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Fill this form and send back to us</h2>
<p>Hello <?= client.name ?> </p>
<p>This is the second part of the process.</p>
<p>Please find the attached form.</p>
<p>You are to make sure the form reaches our office within next two weeks</p>
<p>Thanks<br/></p>
Company.com
</body>
</html>
Sending the email
We have all the pieces ready; now we can compose and send the email.
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;
}
Fetching the file and attaching the file is done by these lines:
var file = DriveApp.getFilesByName(filename);
MailApp.sendEmail({
to: client.email,
subject: "The form to fill",
htmlBody: message,
attachments: [file.next().getAs(MimeType.PDF)]
});
getClientInfo() function gets the client details from the row. Then we use that client details in the email template and compose the email.
MailApp.sendEmail() function is used to send the email.
Next, we need a way to invoke this function. We had already added a menu item. Just update the function to get the current row to send the email to the client of that row.
function sendPDFForm()
{
var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
sendEmailWithAttachment(row);
}
Do File → Save. Go back to the Google Sheet and do Automation → send PDF Form.
It should send an email to the client in that row.
Send all emails in a single step
You may not want to send each email one-by-one. Here is the code to send all emails in a single step. All you have to do is iterate through each row and run the sendEmailWithAttachment() function.
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Automation')
.addItem('send PDF Form', 'sendPDFForm')
.addItem('send to all', 'sendFormToAll')
.addToUi();
}
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");
}
}