Office 365 Mail merge

Client:

Transcript: 

How to send customized emails with data from an Excel Spreadsheet and email content from a Word Document.  Template files provided.

1 00:00:01,466 --> 00:00:05,832 In this final video of the 3 video series, we’ll use the parent contacts we’ve collected to send 2 00:00:05,833 --> 00:00:09,366 customized emails to parents about their students. 3 00:00:09,366 --> 00:00:13,032 We’ll use an Excel spreadsheet to put data into these emails. 4 00:00:13,033 --> 00:00:18,199 Before we send the messages we need to make sure that we have a good spreadsheet to work from. 5 00:00:18,200 --> 00:00:25,533 I’ve prepared a good template that you can use as a starting point for your mail merge setup and a base template for your email content. 6 00:00:25,533 --> 00:00:30,799 Click on the graphics to download both the excel spreadsheet and Word document. 7 00:00:30,800 --> 00:00:37,400 You’ll also need the spreadsheet that you used to import the parent contacts into Outlook available. 8 00:00:37,400 --> 00:00:40,700 Open both spreadsheets up in standalone Excel. 9 00:00:40,700 --> 00:00:46,400 Select the entire tab of collected parent data and copy it with Control C. 10 00:00:46,400 --> 00:00:51,600 Switch to the newly downloaded parent mail merge base spreadsheet, 11 00:00:51,600 --> 00:01:01,200 select the Collected Parent Addresses tab on the bottom, click in the top left to select all 12 00:01:01,200 --> 00:01:07,566 and Control V to paste the collected addresses into the base spreadsheet. 13 00:01:07,566 --> 00:01:11,699 This is a good time to modify the base spreadsheet for your needs. 14 00:01:11,700 --> 00:01:18,700 You might want to add columns to reference in your emails or maybe change the language of the behavior or homeworks by 15 00:01:18,700 --> 00:01:21,666 clicking on this choices and Canned messages tab. 16 00:01:21,666 --> 00:01:28,499 You can edit any of these options here that will then come back to the dropdown lists over here. 17 00:01:28,500 --> 00:01:33,300 Save with control S and close both spreadsheets. 18 00:01:33,300 --> 00:01:40,566 Move the base spreadsheet file to your Office 365 OneDrive or wherever you store such things. 19 00:01:40,566 --> 00:01:45,266 Put the downloaded Word document that you got earlier in the same spot. 20 00:01:45,266 --> 00:01:54,066 Doubleclick that downloaded Word document to open it. 21 00:01:54,066 --> 00:01:58,266 Word will try to reach out to the Spreadsheet, but for now we can click no here. 22 00:01:58,266 --> 00:02:05,899 Make any changes to the content of the e-mail you would like to send and re-save the Word document. 23 00:02:05,900 --> 00:02:11,400 Select Mailing and then Greeting Line. 24 00:02:11,400 --> 00:02:19,200 Setup the Greeting line as you would like then click Match fields. 25 00:02:19,200 --> 00:02:27,233 Link Courtesy Title to Relation and Last name to Parent. 26 00:02:27,233 --> 00:02:32,566 You may want to check this box down at the bottom so you won’t have to do this again. 27 00:02:32,566 --> 00:02:34,532 And Hit Ok. 28 00:02:34,533 --> 00:02:41,133 The Merge fields correspond to the column headers in your base spreadsheet, 29 00:02:41,133 --> 00:02:44,666 they appear within carrets on the word document, 30 00:02:44,666 --> 00:02:48,699 and can be easily seen by clicking Highlight Merge Fields. 31 00:02:48,700 --> 00:02:54,966 You can move around what merge fields are on the template, or delete and re-add them from the Insert Merge Field Menu. 32 00:02:54,966 --> 00:02:59,999 Now would be a good time to customize your base email that you will be sending. 33 00:03:00,000 --> 00:03:02,400 Feel free to tidy up the language and make it your own. 34 00:03:02,400 --> 00:03:06,366 You can edit this before sending each batch of emails if you want, 35 00:03:06,366 --> 00:03:12,032 but having a solid starting point to work from on each email is a good idea. 36 00:03:12,033 --> 00:03:24,766 This template is built so that customized information is at the top and more generic whole classroom information is at the bottom. 37 00:03:24,766 --> 00:03:31,799 You can attach documents to the email but in order to keep people’s email from bogging down and to make email reading on Smartphones 38 00:03:31,800 --> 00:03:38,666 easier, I would recommend sending links to documents posted on your school or classroom website instead of attaching them 39 00:03:38,666 --> 00:03:46,199 For example, To insert a link.such as making this CCPS main website text here a clickable link, 40 00:03:46,200 --> 00:03:55,266 just go ahead and highlight the link, right click, 41 00:03:55,266 --> 00:03:57,666 select hyperlink, 42 00:03:57,666 --> 00:03:59,599 and put the address that you want it to go to here. 43 00:03:59,600 --> 00:04:07,833 I’ve already opened the site here, so I’m just gonna copy, Control C, and then come back over to Word and paste. 44 00:04:07,833 --> 00:04:09,999 And hit ok. 45 00:04:10,000 --> 00:04:13,666 And now my text is a clickable link. 46 00:04:13,666 --> 00:04:18,732 I would recommend putting everything in your maill merge template file that you could possible want, 47 00:04:18,733 --> 00:04:24,533 and then each day that you decide to send something, you can simply delete what you don’t want to send. 48 00:04:24,533 --> 00:04:26,899 It’s much easier to delete things then to add things. 49 00:04:26,900 --> 00:04:30,700 For example, if I don’t wanna send anything out about missing work on a given day, 50 00:04:30,700 --> 00:04:35,933 I can just highlight this item and delete it. 51 00:04:35,933 --> 00:04:37,766 And then there won’t be anything on the emails about missing work. 52 00:04:37,766 --> 00:04:43,432 When you’ve got the Word document template the way you want it, control S to save. 53 00:04:43,433 --> 00:04:51,666 Now that you have a good word template for content and an excel spreadsheet for customized data to start each mailing. 54 00:04:51,666 --> 00:04:55,399 It’s time to prepare your first mass mailing. 55 00:04:55,400 --> 00:04:59,966 Start by reopening the spreadsheet and right away saving as with a different name, 56 00:04:59,966 --> 00:05:05,099 maybe adding the date to the filename to keep things straight. 57 00:05:05,100 --> 00:05:23,866 I’ve built several features into this spreadsheet so that you can quickly get your data ready. 58 00:05:23,866 --> 00:05:29,932 Start by selecting classroom behavior. We assume that the behavior was appropriate, 59 00:05:29,933 --> 00:05:38,333 so we can select that here on the dropdown, 60 00:05:38,333 --> 00:05:52,199 for those students where it was not, select a different option on the drop down.. 61 00:05:52,200 --> 00:06:09,066 Received homework works much the same way and you’ll notice that entries that differ from the default change color to draw your attention to it. 62 00:06:09,066 --> 00:06:22,799 At the top of each column you can enter a default entry that can apply to most students, 63 00:06:22,800 --> 00:06:30,366 then just work down and make changes as necessary. 64 00:06:30,366 --> 00:06:45,599 If you have emails going to both mom and dad 65 00:06:45,600 --> 00:06:47,600 you can either copy the information or 66 00:06:47,600 --> 00:07:02,866 make a formula on your base spreadsheet that will copy the data from above for these entries 67 00:07:02,866 --> 00:07:09,499 where the student generates more than 1 email. 68 00:07:09,500 --> 00:07:13,933 When you are done save and close the spreadsheet. 69 00:07:13,933 --> 00:07:19,799 Next open the Word document template and tie it to the daily excel spreadsheet you just created. 70 00:07:19,800 --> 00:07:30,600 Click No and then mailings, then select recipients use an existing list 71 00:07:30,600 --> 00:07:43,966 and navigate to the spreadsheet with the date you just created with the date on it. 72 00:07:43,966 --> 00:07:50,466 Select the daily data tab. 73 00:07:50,466 --> 00:07:54,599 Then save as with a new name perhaps with the date on it 74 00:07:54,600 --> 00:08:04,233 like you just did for the spreadsheet. 75 00:08:04,233 --> 00:08:08,799 Change your classroom-wide message on the bottom and make any adjustments to the customized content too. 76 00:08:08,800 --> 00:08:14,800 Maybe you don’t have anything to say on missing assignments, so you can just delete that. 77 00:08:14,800 --> 00:08:20,866 At this time, open Outlook alongside Word. 78 00:08:20,866 --> 00:08:26,266 Then it’s time to send your first mass email. 79 00:08:26,266 --> 00:08:29,499 Up here under the mailings tab, click finish and merge. 80 00:08:29,500 --> 00:08:37,033 Then on the dropdown you have the option here of editing individual documents. 81 00:08:37,033 --> 00:08:40,999 For the first few times you do this you might want to try this, 82 00:08:41,000 --> 00:08:43,700 select all and then ok. 83 00:08:43,700 --> 00:08:46,700 This will bring up every single email that’s going to come out. 84 00:08:46,700 --> 00:08:53,400 There’s a few blank spaces on your spreadshhet so you’re going to get Dear zero, nothing’s actually going to go out there. 85 00:08:53,400 --> 00:08:58,533 So scroll down until you find one that actually has data in it. 86 00:08:58,533 --> 00:09:05,099 And there’s one, the name is there, there’s your daily update, it popped in the behavior stuff we have selected from the spreadsheet, 87 00:09:05,100 --> 00:09:07,833 and also the homework. 88 00:09:07,833 --> 00:09:14,466 Down here same thing, down here same thing. Assuming it’s all good you can X out of this and not save it. 89 00:09:14,466 --> 00:09:19,766 And then when you’re ready to do it for real., back up here to finish and merge, 90 00:09:19,766 --> 00:09:24,099 send email messages. It’s asking you who do you want them all to go to. 91 00:09:24,100 --> 00:09:26,900 We want them to go to parents. 92 00:09:26,900 --> 00:09:29,833 This is again from the spreadsheet columns. 93 00:09:29,833 --> 00:09:32,866 Subject line you can change everyday if you’d like. 94 00:09:32,866 --> 00:09:44,832 At this point you can cut down and just do certain people if you want. 95 00:09:44,833 --> 00:09:49,466 But in this case we’re going to do all, and hit ok. 96 00:09:49,466 --> 00:09:54,599 You’ll see each item get rendered. 97 00:09:54,600 --> 00:10:00,400 And if you down to Outlook, one of them came right back to us, you can go into your sent file 98 00:10:00,400 --> 00:10:04,100 and you’ll see all of the different people that got them. 99 00:10:04,100 --> 00:10:07,300 This is a good way to keep track of what you’ve sent to parents. 100 00:10:07,300 --> 00:10:10,400 It’ll still be in your sent file in Outlook.