I'm trying to automate the sending of emails with a pre-filled Google Form, which is updated on a weekly basis, for different recipients. All hyperlinks are different. The problem is that most hyperlinks are very large and the letter looks unreadable. I tried to make short links using third-party services e.g TinyURL or bit.ly with API, but over the time this method stopped working due to the complexity and volume of the hyperlink itself (for example, it contains paragraphs that we can not replace, as well as large comments that need to be saved). Therefore, this method works in about 50% of cases.
An effective method that may be useful to me is forming a hyperlink with the text and its subsequent addition to the body of the letter. I have the app script code and HTML code, but while sending a letter, the text is added without a hyperlink. Can you please advise me, if there is any way to fix this? I'm asking because of a lack of experience in gs and HTML coding.
Sample file [here][1]
gs code:
function sendEmailForm() {
var htmlBody = HtmlService.createTemplateFromFile('mail_template');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("T_TEST");
var startRow = 2; // First row of data to process
var numRows = 70; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 11);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i in data) {
var row = data[i];
{
var emailAddress = row[0]; // First column
if (emailAddress.match('@') === null){
continue; // skip this iteration of the loop and go to the next one
};
var ccEmailAddress = row[1]
var subject = row[2];
htmlBody.project = row[3].replace(/\n/g, "<br>");
var message = htmlBody.evaluate().getContent();
var aliases = GmailApp.getAliases()
Logger.log(aliases); //returns the list of aliases you own
Logger.log(aliases[0])
GmailApp.sendEmail(emailAddress, subject, message, {htmlBody : message, cc: ccEmailAddress,'from': aliases[0]});
}
}
}
HTML code `
<head>
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
<o:PixelsPerInch>96</o:PixelsPerInch>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<title>Test Email Sample</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="format-detection" content="telephone=no">
<!--[if !mso]><!-->
<link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700,800" rel="stylesheet">
<!--<![endif]-->
<style>
body {
font-family: Helvetica, Arial, sans-serif;
margin: 0 !important;
padding: 0 !important;
-webkit-text-size-adjust: 100% !important;
-ms-text-size-adjust: 100% !important;
-webkit-font-smoothing: antialiased !important; /*style only recognised by some browsers*/
}
.text-center {
text-align: center;
}
.button {
border: none;
color: white;
padding: 15px 32px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 18px;
margin: 4px 2px;
cursor: pointer;
background-color: #2057f5;
border-radius: 12px;
}
img {
border: 0 !important;
display: block;
outline: none !important;
}
p {
color: #171717;
font-size: 16px;
line-height: 24px;
margin: 0px !important; /*Old versions of Outlook ignore margin if it is lower case as usual*/
padding: 0px !important;
}
h1, h3 {
color: #171717;
padding: 0px !important;
font-size: 20px;
font-weight: bold;
line-height: 28px;
margin: 0px !important; /*Old versions of Outlook ignore margin if it is lower case as usual*/
}
h2 {
color: #171717;
padding: 0px !important;
font-size: 32px;
font-weight: bold;
line-height: 40px;
margin: 0px !important; /*Old versions of Outlook ignore margin if it is lower case as usual*/
}
ul {
color: blue;
list-style-position: inside;
padding: 0;
}
ul strong {
color: #171717;
}
table {
border-collapse: collapse;
mso-table-lspace: 0px; /*Microsoft Office only styling*/
mso-table-rspace: 0px; /*Microsoft Office only styling*/
}
td, a, span {
border-collapse: collapse;
mso-line-height-rule: exactly; /*Microsoft Office only styling*/
}
.ExternalClass * {
line-height: 100%;
}
span.MsoHyperlink {
mso-style-priority: 99; /*Microsoft Office only styling*/
color: inherit;
}
span.MsoHyperlinkFollowed {
mso-style-priority: 99; /*Microsoft Office only styling*/
color: inherit;
}
</style>
</head>
<body style="margin: 0px; padding: 0px;" bgcolor="#ffffff">
<table valign="top" width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#ffffff" align="center">
<tbody>
<tr>
<td valign="top" align="center">
<table style="max-width: 600px; color: #171717;" width="100%" valign="top" cellspacing="0" cellpadding="0" border="0" bgcolor="#f7f7f7">
<tbody>
<tr>
<td colspan="3" style="background-color: #2057f5;">
<p style="font-size: 1px; line-height: 50px; margin: 0;"> </p>
</td>
</tr>
<!-- Header banner START -->
<tr>
<td width="10" style="background-color: #2057f5;">
</td>
<td width="100%" valign="top" style="background-color: #2057f5;">
<h2 style="color: #ffffff; font-size: 32px; line-height: 36px; margin: 0;">Provide your<br />Weekly Status Update</h2>
</td>
<td width="10" style="background-color: #2057f5;">
<p style="font-size: 40px; line-height: 60px; margin: 0;"> </p>
</td>
</tr>
<!-- Header banner END -->
<tr>
<td colspan="3">
<p style="font-size: 1px; line-height: 30px; margin: 0;"> </p>
</td>
</tr>
<tr>
<td width="20">
<p style="font-size: 50px; margin: 0;"> </p>
</td>
<!-- Greeting section START -->
<td width="100%" valign="top">
<h1 style="font-size: 18px; line-height: 26px; margin: 0;">Hi there,</h1>
<p style="margin: 0;"> <br />Please provide the weekly status report for the below project(s) <br /> </p>
<p style="margin: 0;">
<?!= project ?>
<br /> </p>
<p style="margin: 0;"> By keeping everyone in the loop with updates on a weekly basis you help provide accountability and align resources when necessary. You may review the submitted data in the general file <br /> </p>
<p style="margin: 0;"> Should you have any questions or experience difficulties, reach out to our Team.
<br /> </p>
<h1 style="font-size: 18px; line-height: 28px; margin: 0;">Best regards,</h1>
<h1 style="font-size: 16px; line-height: 24px; margin: 0;">XX Team</h1>
</td>
<!-- Greeting section END -->
<td width="20">
<p style="font-size: 50px; margin: 0;"> </p>
</td>
</tr>
<tr>
<td colspan="3">
<p style="font-size: 1px; line-height: 30px; margin: 0;"> </p>
</td>
</tr>
`
Thank you in advance! [1]: https://docs.google.com/spreadsheets/d/15De6w3RUn62xWZ3oumMAYI7dPfGfMAcDjr01qTwX5Jk/edit#gid=0