Leasing Reminders
I had a friend work at a home leasing company. Homes were leased for x amount of months at a certain rate. Leases would expire and need to be renewed at different rates or the lessee would need to decide to move. My friends job was to send out the emails to theses lessees a month or 3 months before the lease expired. They had many homes that expire at all times of the year, but not every day, so it was annoying to keep up on this tedious task and easy to make mistakes.
Since they used Google for their email they sent from I made a little Google Spreadsheet. I had him fill it with all the data needed for the email they send. Things like, lessee’s name, address, old rate, new rate, expire date, etc. I made a template email in html which would take the data from the spreadsheet and fill it in.
I then wrote a script in Google App Script which would run every morning, it would check the current day and see if it was 1 month or 3 months before the expire date. If it was then it would fill the html with the data and send that email to the lessee. That easy. It would update the spreadsheet with a timestamp of when the email was sent.
My friend would have that email in their account as a sent email like they typed it themselves. They could respond to anything like normal. Easy peasy.
Spreadsheet for Leasing Data
HTML Email Template
<!doctype html>
<html>
<head>
<base target="_top" />
</head>
<body>
<?= todayDate ?>
<br />
<?= name ?>
<br />
<?= street ?>
<br />
<?= cityState ?>
<br />
<p>Dear <?= name ?>,</p>
<p>
As a valued tenant, we would like to thank you for residing with us over
the last year. Your current lease Is set to expire. We would like to offer
you the opportunity to sign another year lease. With your signed lease,
your new monthly rate will be
<span style="color: #ff0000">$<?= annual ?></span> starting
<span style="color: #ff0000"><?= expireDate ?></span>.
</p>
<p>
If you choose not to commit to a new 1 year lease, you have the option to
lease on a month to month basis at the new lease rate of
<span style="color: #ff0000">$<?= monthToMonth ?></span> per month.
NOTE: A 30 day written notice to vacate will still be required.
</p>
<p>
If you are interested in signing another year lease with us, please
respond to this email within 14 days and a new lease will be sent to you
to sign. If you do not reply to this email, it will be assumed that you
wish to remain on a month to month agreement and therefore will be paying
the additional $25 rent.
</p>
<p>
If you have an questions about your new lease, feel free to get a hold of
SOMEONE through phone, text, or email.
</p>
<p>Sincerely yours,</p>
<p>SOMONE THAT WORKS HERE</p>
<table style="height: 44px; width: 652px">
<tbody>
<tr style="height: 25px">
<td style="width: 157px; height: 25px">
<p><strong>555-555-5555</strong></p>
<p>
<span style="color: #00ccff"><strong>PHONE</strong></span>
</p>
</td>
<td style="width: 307px; height: 25px">
<p><strong>123 FAKE STREET, NOMALVILLE, ST 12345</strong></p>
<p>
<span style="color: #00ccff"><strong>ADDRESS</strong></span>
</p>
</td>
<td style="width: 187px; height: 25px">
<p>
<strong
><a href="mailto:email@domain.com">EMAIL@DOMAIN.com</a></strong
>
</p>
<p>
<strong><span style="color: #00ccff">EMAIL</span></strong>
</p>
</td>
</tr>
</tbody>
</table>
</body>
</html>
AppScript Code
function leaseReminder() {
// This creates a shortcut name for the active spreadsheet.
const ss = SpreadsheetApp.getActive().getActiveSheet();
// This creates a variable for today's date to refer to in a specific format.
var dateToday = Utilities.formatDate(
new Date(),
Session.getScriptTimeZone(),
"MM/dd/yyyy",
);
var lastRow = ss.getLastRow();
// This creates a variable for the HTML email we've created.
var template = HtmlService.createTemplateFromFile("ReminderEmail");
// When triggered this will look for anyone it needs to email.
for (var i = 2; i <= lastRow; ++i) {
var isReminderDateBlank = ss.getRange(i, 9).isBlank();
var reminderDate = ss.getRange(i, 9).getValue();
const dateFormat = "MM/dd/yyyy";
const timeZone = Session.getScriptTimeZone();
if (
!isReminderDateBlank &&
dateToday == Utilities.formatDate(reminderDate, timeZone, dateFormat)
) {
// Variables for the email template
var sentVerificationCell = ss.getRange(i, 13);
var email = ss.getRange(i, 4).getValue();
const myEmail = "cjared.mortenson@gmail.com";
const subject = "Your lease expires next week";
const subjectToMe = "Reminder Lease Email Sent";
var renterName = ss.getRange(i, 1).getValue();
const timeStampFormat = "MM/dd/yyyy hh:mm";
var timeStamp = Utilities.formatDate(
new Date(),
timeZone,
timeStampFormat,
);
template.todayDate = Utilities.formatDate(
new Date(),
timeZone,
dateFormat,
);
template.name = ss.getRange(i, 1).getValue();
template.street = ss.getRange(i, 2).getValue();
template.cityState = ss.getRange(i, 3).getValue();
template.reminderDate = ss.getRange(i, 8).getValue();
template.expireDate = Utilities.formatDate(
ss.getRange(i, 8).getValue(),
timeZone,
"EEE MMMM dd, yyyy",
);
template.annual = ss.getRange(i, 11).getValue();
template.monthToMonth = ss.getRange(i, 12).getValue();
// This email is to the renter
MailApp.sendEmail(email, subject, "", {
htmlBody: template.evaluate().getContent(),
});
sentVerificationCell.setValue("Sent " + timeStamp);
// This email is to you letting you know the previous email was sent
MailApp.sendEmail(
myEmail,
subjectToMe,
"The following Lease Expiration Reminder was just sent to " +
renterName,
{ htmlBody: template.evaluate().getContent() },
);
}
}
}
