FPs: Launching an e-card system

In this tutorial video, I explain and show how I created an automated e-card program for Framingham caregivers to send electronic thank you cards to teachers and staff. This execution is unique in that there are checks and balances to assure appropriateness of message and an image is embedded into the body of the email using a mix of merge tags and basic HTML. Other K-12 Communicators are invited to follow these steps as needed to run their own program. (recorded in April, 2021)

Background

During the 2020 - 2021 School Year, the majority of teaching and learning was being done remotely in Framingham. While some were back in classrooms early in the school year based on need, the majority of those who opted to return to the classroom did so on a hybrid schedule, and of course, some families chose to keep their students remote all year.

In May of 2020, I collected videos and photos from families and turned around several slideshow videos for teachers. For this particular Teacher Appreciation Week, I wanted to do something different. I wanted to create something that would empower others to share their gratitude or messages of inspiration.

Enter E-Cards

I belong to a national group of School Communication Professionals and came across a similar project from another District. I utilized that to get everything up and running. It took many hours and was working like a charm. It went like this:

  • Sender completes a google form by selecting an e-card image, typing their name and email address, the recipient’s name and email address, and then adds a special message.

  • The Form drops information into a spreadsheet.

  • Spreadsheet automation runs and the e-card is sent to the recipient.

  • Done!

Pivot

When I ran this all by our Senior Leader team, a colleague raised an important question. She asked, “what if someone sends an inappropriate message? We have no way of prohibiting the message delivery”. This forced me to take a step back and think about how to add checks and balances into the operation. It also opened the door for me to expand on this execution and do something that hadn’t been done before.

Results

In April 2021, I launched the FPS E-Card Program with a website, followed by an email and text message to all FPS Families. I followed up with one additional reminder as we Teacher Appreciation Week. We received over 1,000 submissions of families and students thanking teachers!

HOW-TO TUTORIAL

Items needed

  • e-Card Graphics (5in x 3.5in)

  • Google Form Header Graphic (1600x1400)

  • Google Form 

  • Google Responses (for the Google Response Sheet)

  • Google Doc (template for email attachment)

  • Website Page (FPS Live Example is http://bit.ly/FPSTAW21)

  • Communication to families or intended users

  • Staff Directory (Recommended)

uniqueness

Several other Districts have executed this, but I had yet to find one done with Autocrat in English, Spanish, and Portuguese, with a manual trigger, and with an image embedded in the body of the email using custom merge tags and basic HTML. I am sharing my execution for anyone else that might want to duplicate it!

Graphics

  • e-Card Graphics: I created ours initially in Adobe Spark and in subsequent years used Canva - sized 3.5 x 5” and saved as .png.

  • Google Form Header: Copy and resize the e-card image to 1600px x 400px, then move graphic and text items as needed

Website

  • Write Context / Instructions

  • Upload the e-Card graphics to a dedicated page on your website

  • Hyperlink each image to the Google Form send link

Google Form

  • The first question should be multiple choice and include your graphic images and their associated Image URL. The trilingual prompt for image selection is: Choose e-Card | Elige una tarjeta | Selecione um cartão

screenshot of Multiple Choice question on a google form where the answer includes an image


  • When you name the graphics, use the image URL which you can get by right clicking on the image on your public-facing website and select ‘Copy Image Address’. (This is really important if you want the e-card image to display in the body of the email.)

  • Add the questions you want to use. I included mine below which are set up in English, Spanish, and Portuguese

    • Your Name / Tu nombre / Nome inteiro:

    • Your Email Address / Tu dirección de correo electrónico / Endereço de e-mail:

    • Recipient Name / Nombre del destinatario / Nome do recipiente:

    • Recipient Email Address / Correo electrónico del destinatario / Endereço de email do destinatário:

    • Special Message / Mensaje especial / Mensagem especial:

  • Grab the ‘send link‘ for your Google Form (click Send > Send Via (link) and copy the link) so you can hyperlink the e-card images on your website and/or as a hyperlink on your website.

Google Doc (Template)

  • Create a Google Doc template that transitions to a PDF and will be sent as an attachment to the recipients. It will contain information submitted in the Google Form. Mine was called “FPS e-Card”.

  • Everything in the <<___>> brackets displayed below will be mapped from your form questions / column headers via the AutoCrat merge. The bracket content in the google doc can be whatever you want but should align with the column headers.

Screenshot of the google document template needed if you want to send your special message as a PDF.

Google Form Response Sheet + Autocrat Automation INSTRUCTIONS

First: Go to your Google Form > Responses > Create Google Sheet
Second: Add-ons > Get Add-ons > AutoCrat
Steps / Settings: This is how you create the merge job that will take your user-submitted form information and merge it into a PDF AND send it to their desired recipients.

  • Autocrat - Launch

  • New job NEXT

  • Enter Job Name (Mine was “Send an FPS e-Card”) NEXT

  • Choose Template From Drive (Select your Google Doc template, mine is titled FPS e-Card) NEXT

  • Mapping Source data from your Google Form to your Template

Table showing the Source Type, Template Brackets, and Google Form Columns

NEXT

  • File Settings

    • Name: Mine is An FPS e-Card (This is what your output PDF will be named when it is attached to the email sent automatically to the recipient.)

    • Type: Google Docs

    • Output as Multiple Output Mode (Classic Mode) NEXT

  • Choose Destination Folder

    • Click Choose Folder, create new folder called Sent e-Cards

    • Select Sent e-Cards and remove the original folder selected.

    • This is where all of the output PDFs will be saved.

    • NEXT

  • Add Dynamic Folder Reference - skip / NEXT

  • Set merge condition - skip / NEXT

  • Share docs & send emails

    • Share Doc: Yes

    • Share doc as: PDF

    • Allow Collaborators to re-share: Yes

    • Send from generic no-reply address: No

    • Email Template: This is what my template looks like. The recipient will receive an email with your defined subject line. The body of the message contains the chosen graphic along with the information submitted in the Google Form. Everything in <<Brackets>> needs to be your exact column headers from the google response sheet. The Recipient will also get the PDF attachment. 

To: <<Recipient Email Address / Correo electrónico del destinatario / Endereço de email do destinatário:>>

Cc: <<Your Email Address / Tu dirección de correo electrónico / Endereço de e-mail:>>

Reply to: <<Your Email Address / Tu dirección de correo electrónico / Endereço de e-mail:>>

Title: You've received an FPS e-Card!

<img src="<<Choose e-Card | Elige una tarjeta | Selecione um cartão>>" alt="FPS eCard">

Dear <<Recipient Name / Nombre del destinatario / Nome do recipiente:>>,

<<Special Message / Mensaje especial / Mensagem especial:>>

This was sent with love and admiration from <<Your Name / Tu nombre / Nome inteiro:>> (<<Your Email Address / Tu dirección de correo electrónico / Endereço de e-mail:>>) via the FPS eCard Program for Teacher Appreciation Week 2021.

Send your own FPS e-card >> www.framingham.k12.ma.us/FPSecard

  • Add/Remove Job Triggers

    • Run on From Trigger: No

    • Run on Time Trigger: No

    • SAVE

Checks and Balances

As mentioned above, there was concern about mis-use if the form submission automatically sent the special message to the recipient from the sender. I automated the template creation - but manually ran the merge job after reviewing the submitted messages to ensure nothing offensive is sent to teachers and staff. If I saw any messages in the ‘Special Message’ column that were offensive or inappropriate, I would cut them from the Spreadsheet and put them in another tab. (this never happened!)

Several times each day I checked the Google Response Sheet, review the special message column, and then ran the Autocrat merge job by clicking on the ‘Add-on’ and running the saved job. When I do this, it automatically sent any newly entered form responses to the intended recipients.

This was not my original idea, I saw it in a group on Facebook and ran with it. I modified it to be unique for Framingham. These unique aspects included: graphics, trilingual communication, automated sending AFTER review/moderation, and embedded graphics into the body of the email.

Here’s a link to the original posts where I found inspiration: https://www.facebook.com/groups/781480078695552/permalink/1741699432673607

https://www.facebook.com/groups/781480078695552/permalink/1817616761748540

COMMUNICATION

Webpage Instructions

Share the Love - A Teacher Appreciation Week E-Card Program

In honor of Teacher Appreciation Week (insert dates) and School Nurses Day (date), we created a simple and fun way for you to give thanks, encourage, and celebrate those who have made this challenging time brighter for your family! 

All you need is the staff member's email address! For the staff directory, follow this link - (insert link to staff directory)

Simply click an e-card below, enter your name and email, your recipient’s name and email, include your own customized message and submit! The E-Card will be delivered right to their inbox!

(INSERT ECARD GRAPHICS)

Email to Families

ENGLISH

Subject: Share the Love - A Teacher Appreciation Week E-Card Program

In honor of Teacher Appreciation Week (insert dates) and School Nurses Day (date), we created a simple and fun way for you to give thanks, encourage, and celebrate those who have made this challenging time brighter for your family! 

Simply follow these steps:

  1. Visit this Page (insert link)

  2. Click on the E-Card Image You Like

  3. Complete the Simple Form (Trilingual)

  4. Share the Love!

Text (140 Characters): Share the Love - A Teacher Appreciation Week E-Card Program: (insert link)

SPANISH

Email Subject: Comparta el amor – Un programa de tarjetas electrónicas de la semana de agradecimiento a los maestros

En honor a la semana de apreciación a los maestros (del 3 al 7 de mayo) y el Día Nacional de las Enfermeras Escolares (el 6 de mayo), ¡hemos creado una manera simple y divertida de dar gracias, animar y celebrar a aquellos que han hecho esta época difícil sea brillante para su familia! 

Siga estos sencillos pasos:

  1. Visite esta página (insert link)

  2. Haga clic en la imagen de la tarjeta electrónica que le guste

  3. Complete el formulario simple (Trilingual)

  4. ¡Comparta el amor!

Text (140 Characters): Comparta el amor – Un programa de tarjetas electrónicas de la semana de agradecimiento a los maestros: (insert link)

Translations are provided by FPS. Translators are not responsible for the content of the document. GARC – 02/03/2020 SPANISH

PORTUGUESE

Email Subject: ‘Share the Love’ (Compartilhe o amor) – Um programa semanal de envio de cartões eletrônicos (E-Cards) para a Semana de Reconhecimento dos Professores (Teacher Appreciation Week)

Em celebração `a Semana de Reconhecimento dos Professores (3-7 de maio) e do Dia Nacional das Enfermeiras escolares (6 de maio), nós criamos uma maneira simples e divertida para vocês agradecerem, incentivarem e celebrarem aqueles que têm tornado estes tempos difíceis mais positivos para a sua família! 

Simplesmente siga estes passos:

  1. Visite esta página  (insert link)

  2. Clique na imagem do cartão eletrônico (E-Card) que você escolher

  3. Complete o simples Formulário (trilíngue)

  4. Compartilhe o amor!

Translations are provided by FPS. Translators are not responsible for the content of the document. CAME – 04/22/2021.

Text (140 Characters): Compartilhe o amor - Um programa semanal de envio de cartões eletrônicos para a Semana de Reconhecimento dos Professores: (insert link)