G Suite and Outlook Power

Electronic communication is more convenient than phone calling or setting up a parent/teacher meeting. Have you ever wanted to collect parent e-mail addresses in a more efficient way? I always used paper and pen and then struggled with parent penmanship, trying to decipher an uppercase “I” to a lowercase “l”?

This post explains how I use Google Forms to collect e-email contact info, add it to a spreadsheet, export a .csv download,  and import into Outlook. Once in Outlook mail merging, setting up distribution lists, and/or messaging individual contacts is a possibility. I know this is a lot of info, but in the end, I think you will appreciate the ease of collecting contact info online and allowing a spreadsheet to update your contacts in Outlook.

remind parents of your Google Form

First we create a Google Form to collect E-Mail addresses. You can have your students and/or parents click a link on your web site to take them to a Google Form.

I added a link to my school web page, then I can send a paper note home with students with my web site url.

I have always had issues with parent penmanship. If you allow them to enter electronically, the chances of misreading characters are reduced.

image01-web

Next, we will build our e-mail collection form.

Setting Up your Form and Preparing for Export

I would suggest creating a minimal form, collecting parent/guardian name, e-mail, student first name, and student last name. We do this because we want to make the export Outlook friendly for a quick and problem free import. Once your form is created you can also customize the sheet field headings to match Outlook’s field names.

image00-form

Create your Google Form and Spreadsheet

When you are setting up your form here are some of the custom settings I suggest.

  • Under the Settings -> General option, select “Collect email address.” This will force them to add a valid address.
  • Under the Settings -> General option -> Requires sign in, uncheck “Restrict to Mukilteo School District users.” (for my colleagues in the MSD)
  • Under the Settings -> General option – Respondents can, uncheck “See summary charts and text responses.” You don’t want the respondents to see other respondents’ information.
  • The other custom Settings -> General options are up to you.
  • Under the Settings -> Presentation option, add a Confirmation Message to alert your respondents that their info has been received and you will be contacting them.

When a response comes in, you will be able to customize your field headings. This will make it easy to import and update parent email addresses and contact information. To access your Sheet click the green sheet icon.

image02-response

Change the headings from this…

image03-origheading

To this…

image04-newheading

Notice I also added a Gender column. This will be for our condition statements within Outlook.

Here are the changes I made:

  • Timestamp -> Timestamp (no change)
  • Username -> E-Mail
  • What is your first and last name? (Parent/Guardian) -> E-Mail Display Name
  • What is your child’s first name? -> First Name
  • What is your child’s last name? -> Last Name

These changes will now stick with the linked spreadsheet. When a new entry is added the headings will always remain the same making it easy to import and overwrite duplicates.

Exporting to .csv

We want to export a .csv file to import into Outlook. To do this click File -> Download as -> Comma-separated values (.csv, current sheet). It will save to a location of your choice, most likely your Downloads folder. You will have to find it later, so be sure to remember where it saves.

image05-csv

Import into Outlook and Map Field Headings

Now we are going to take our collected data and import it into Outlook. To do this we will need to have our field headings changed and a .csv file downloaded.

The next step is to open Outlook and click on the Contacts section.

image06-omenu

Before we import, we should create a folder to store our contacts. Sometimes if you store all your contacts in the top level folder, it can get challenging to stay organized from year to year.

You will right click on the Contacts with your e-mail (next to it), and choose New Folder. Give your folder a name and click Ok.

image07-olist

Then you will click File -> Open & Export -> Import/Export.

image08-ooptions

Choose Import from another program or file.

image09-oimport

Choose Comma Separated Values.

image10-oimport2

Browse to your saved .csv file that you downloaded from your Google Form responses. Depending on what you need you can choose an option. If you have run this import more than once, you will want to choose according to your needs.

image11-oimport3

Choose the folder you created to store your imported data.

image12-oimport4

Now we will Map Custom Fields (or check to be sure they are mapped).

image13-oimport5

The following should be mapped. Under the Name area, First Name and Last Name should map. Under the E-mail area, E-mail and E-Mail Display Name should map. Under Gender, Gender should map.

image14-ofieldmap

Click Ok, then Finish. Now we are ready to modify our view.

Modify Your Outlook List View

Click on your contact folder that you created. Now find the List option and click on it.

image15-olist

Right click in the view window and click on View Settings to add and remove custom fields. Add the following: Last Name, First Name, E-Mail, E-Mail Display As, and Gender.

image16-ofields

Now you are ready to use Word to send an email.

Starting Your E-Mail Merge WIth Word

Word can easily connect with your Outlook Contacts. We can also use conditional rules to look at a Gender field name and dynamically add pronouns throughout an email. So a blanket email can look more personalized, but you can send multiple emails with one click.

First, open Word and click on Mailings -> Start Mail Merge -> E-mail Messages.

image17-word

Next, click Select Recipients -> Choose from Outlook Contacts.

image18-word

Next, click on your folder that you created earlier, and OK.

image19-word

Now, select the recipients you wish to send an email to, and click Ok.

image20-word

Write Your E-Mail Message

As you write your letter you will insert merge fields. The list is long, but we will only use First (student first name), Last (student last name), and Display Name (student full name). We will also use the Gender field to write some conditions to get our pronouns in our letter.

image21-word

When you want to add text from your fields, click Insert Merge Field and drop down the list. To preview your letters click Preview Results.

Adding Gender Specific Pronouns

To add pronoun we will use a rule. Move your cursor and click where you would like to add a pronoun. Click Rules -> If…Then… Else.

image22-word

Now add your conditional rule. If the Gender is Equal to Male Insert this text: He. Otherwise insert this text: She.

image23-word

Throughout the letter you will add various conditional rules based on Gender. This makes your blanket e-mail more personalized.

Send Your E-Mail

To send your email(s), click Finish & Merge -> Send Email Messages. If you only want to send to specific recipients you can click Edit Recipient List and check only those you wish to send the message before clicking Send Email Messages.

image24-word

The final step will allow you to add subject line and choose the mail format (I usually choose HTML). For the To: you will want Email_Address.

image25-word

Now you have a dynamic contact list that you can continue to use to update your current contact list.

Leave a Reply

Your email address will not be published.