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.

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.

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.

Change the headings from this…

To this…

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.

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.

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.

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

Choose Import from another program or file.

Choose Comma Separated Values.

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.

Choose the folder you created to store your imported data.

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

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.

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.

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.

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.

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

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

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

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.

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.

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

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.

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.

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