Mail merging s: Word, Excel and Outlook

Software Used

Revision Information

Version Date Author Changes made

1.0 July 2012 Pamela Stanworth Created

1.1 January 2013 Pamela Stanworth Updated for IT Services

Copyright and Acknowledgements

Pamela Stanworth makes this document and the accompanying PowerPoint presentation available under a Creative Commons licence:

Attribution-NonCommercial-ShareAlike CC BY-NC-SA Screenshots in this document are copyright of Microsoft.

Contents

1 Introduction ... 2 

1.1. What Do You Need? ... 2

1.2. What Will You Learn? ... 2

1.3. Where Can I Get A Copy of the Software? ... 2

2 Getting Started ... 2 

2.1. What You Will Use ... 2

2.2. Using Office 2010 ... 3

3 Collecting a List of Data ... 3 

Exercise 1

Creating a list of data ... 4

4 Writing the Common Text ... 5 

Exercise 2

Creating a text for the emails ... 5

5 Starting the Mail Merge Wizard ... 7

Exercise 3

Starting the Mail Merge Wizard ... 8

6 Setting the Source of the Document and the Data ... 9 

Exercise 4

Setting the information sources ... 9

7 Finalising the email Message ... 12 

Exercise 5

Working on the email text ... 12

8 Previewing the Messages ... 14

Exercise 6

Previewing the messages ... 14

9 Sending the Messages ... 15 

Exercise 7

Sending out the emails... 15

10 Date Formats in email Messages ... 16 

11 What Next? ... 16 

11.1. Word-processing Courses ... 17

11.2. Excel Courses ... 17

11.3. Nexus Email Courses ... 17

11.4. Office 2010 ... 17

11.5. Downloadable Course Materials and More – the ITLP Portfolio ... 17

1 Introduction

Welcome to the Mail merging emails session!

This booklet accompanies the presentation delivered by IT Services at Oxford University (formerly OUCS), IT Learning Programme.

1.1. What Do You Need?

We will assume that you are familiar with the everyday use of a word-processor, a spreadsheet and an email client. This includes opening, saving and closing a document, editing text, sending an email, and choosing options from a menu, ribbon tab or dialog.

These activities use the Microsoft Office suite of software.

1.2. What Will You Learn?

This presentation will help you learn to use Excel, Word and Outlook together to create and send a set of personalised emails.

In this session we will cover the following topics:  Organising a data list in Excel

 Setting up pattern text in Word  Including fields for the varying text

 Merging to create a set of emails to send out by Outlook

These notes deal with Office 2010 using Windows. Having worked through these notes, you should also be able to adapt to other versions including Office for Mac, since most of the principles hold true regardless of the version of the software. Getting to grips with a package as sophisticated and powerful as Office can be time-consuming, so allow yourself plenty of time for practice. To increase your understanding of Office, make use of the Help facilities available within the software, or attend a taught course in the IT Learning Programme.

1.3. Where Can I Get A Copy of the Software?

If you are unable to find Microsoft Office 2010 on your computer, it may not have been installed and you should talk to your IT support contact (or the IT Services Help Centre).

If you are a member of staff, you can obtain a copy of Microsoft Office from the IT Services on-line shop. Students can obtain a Microsoft Student Licence, but this must be bought through a Microsoft Authorised Education Reseller.

2 Getting Started

2.1. What You Will Use

In this demonstration, we will suppose that you need to send emails to a number of people, with slightly different content in each case.

The method described here requires you to have MAPI mail client software. So it works most neatly with Microsoft Outlook, and may adapt to work with other software such as Thunderbird, but will not be suitable if you use a web-based email tool such as Nexus/OWA/OutlookWebAccess.

2.2. Using Office 2010

If you have previously used another version of Office, you may find Office 2010 looks rather unfamiliar. “Office 2010: What’s New” is a self-study guide covering the Ribbon, Quick Access Toolbar and so on. This can be downloaded from the ITLP Portfolio at http://portfolio.it.ox.ac.uk.

For anyone who prefers not to use the mouse to control software, or who finds a keyboard method more convenient, it is possible to control Office 2010

applications without using a mouse. Pressing ALT once displays a white box with

a letter or character next to each visible item on the Ribbon and title bar (shown in Figure 1).

Figure 1 Keystrokes to Control Ribbon Tabs and Title Bar (Press ALT to Show These)

After you have typed one of the letters/characters shown, the relevant Ribbon tab or detail appears, with further letters/characters for operating the buttons and controls.

The elements of a dialog can be controlled, as usual with Windows applications, by using TAB to navigate between items or typing the underlined character shown

beside an item.

3 Collecting a List of Data

Exercise 1 Creating a list of data

 Use Excel  Collect the data

 Check the information saved

Task 1

Start Excel

Step 1

Look in the Start menu, for an entry for Excel

Step 2

It may appear in a group called something similar to Microsoft Office

Step 3

Or look for an Excel icon on your desktop

Task 2

Insert your data in columns

Step 1

Put labels at the tops of the columns, for the information you will use, such as Name, emailAddress,

DateOfEvent

Any column headings will work, however it will be easier to match them into the merge document later, if you use labels that are easy to remember

Step 2

You need a column for each field: each fact that will vary from person to person

Step 3

Insert the text or numbers that will be needed: one row for each recipient

Task 3

Make sure the data is complete and correct

Step 1

This is the opportunity to check all the data carefully

Step 2

Correct any spelling mistakes or inaccuracies Complete any gaps

Task 4

Figure 2 Collecting the data in a worksheet

4 Writing the Common Text

Use Word to write the main text of the document, with markers where the details will be filled in.

Exercise 2 Creating a text for the emails

 Use Word

 Draft the text of the email

 Insert markers at each point where a detail is to go

Task 1

Start Word

Step 1

Look in the Start menu, for an entry for Word

Step 2

It may appear in a group called something similar to Microsoft Office

Step 3

Or look for an Word icon on your desktop

Task 2

Compose the main part of the email text

Step 1

Write your email text in a Word document

Step 2

Task 3

Add markers where the variable data is to go

Step 1

At a point where one of the variable facts is to be inserted from the Excel workbook, type a marker

Step 2

Any text will do as a marker – such as XXX or $%£ However, you may like to use phrases that remind you where each fact is to be inserted – such as FULLNAME and DEPARTMENTNAME

Step 3

Add a marker for every fact that is to be inserted from the Excel workbook

Task 4

Format the text as required

Step 1

Apply any formatting to the text, such as bold text, centred headings

This text can be laid out and formatted so that it will be effective as an email

Task 5

Make sure the data is complete and correct

Step 1

This is the opportunity to check all the text carefully

Step 2

Correct any spelling mistakes

Task 6

Save the Word document file, making a note of the filename and the location

5 Starting the Mail Merge Wizard

The Mailings tab of the Ribbon includes . Although this menu offers the elements of the mail merge process separately, it is easiest to use the Wizard which takes you through the process, step by step.

Exercise 3 Starting the Mail Merge Wizard

 Use Word

 Start the Mail Merge Wizard  Choose to produce emails

Task 1

Start the Wizard

Step 1

Click the Mailings tab of the Ribbon

Step 2

Click to see the Mail Merge menu

Step 3

Click Step by Step Mail Merge Wizard

Task 2

Select the E-mail document type, in Step 1

Step 1

The instructions appear in the grey Mail Merge pane at the side of the Word window

At any point while working through the Wizard, you can page back to an earlier step and change your choices

Step 2

In Step 1 of the Wizard, read the list of document types available

Step 3

Choose E-mail messages

Step 4

At the bottom of the Wizard pane, click Next, to move to Step 2 of the Wizard

Task 3

If you chose E-Mail document in Step 1 of the Wizard, then in Step 2 the email is displayed in Web Layout View

Figure 5 Selecting to send emails

6 Setting the Source of the Document and the Data

 Specify which document is to be used  Specify which data is to be used

Task 1

In Step 2, select a starting document

Step 1

In Step 2 of the Wizard, you specify the document where the main text of your document is to be found

Step 2

If your document from Exercise 2 is still open, choose to Use the current document

Step 3

Figure 6 Taking the text from the current document Task 2

Click Next to go to Step 3 of the Wizard

Task 3

In Step 3, select Use an existing list and browse to where the list of recipients has already been saved

Step 1

In Step 3 of the Wizard, you select where the people’s details are to be found, who will receive the emails

Step 2

If your list of recipient information has not already been made up, choose to Type a new list

Step 3

If your Excel workbook contains the information about your recipients, choose Use an existing list

Step 4

Click

Navigate to where your Excel workbook was saved in Exercise 1

Step 5

Figure 7 Locating the data source Task 4

Select from the list which of the data is to be used in this mail merge

Step 1

The list of recipients can be filtered to include only some of the records, by setting criteria

Step 2

Used the arrows at the top of the columns to show a menu for filtering the records by matching values

Step 3

Tick or clear the checkboxes on individual rows, to include or exclude specific records in the merge

Task 5

If, later, you need to make further changes to the choice of data, click to display this dialog again

Task 6

Figure 8 Filtering and ad-hoc-selecting to choose recipients

7 Finalising the email Message

In the next Step, markers called merge fields are needed to show where each person’s details are to be inserted from the Excel workbook.

Exercise 5 Working on the email text

 Revise the text of the email if necessary  Insert merge fields for the variable data

Task 1

In Step 4 of the Wizard, check over the text once more

Task 2

Replace each marker that you typed in the text, with a merge field code

Step 1

Locate a marker that you typed earlier, showing a position where one of the variable facts is to be inserted eg. FULLNAME

Step 2

Delete the marker text

Step 3

Click

Step 4

Step 5

Insert the field that matches the marker you deleted

Step 6

Continue replacing each marker text with the relevant merge field

Step 7

Note that the email addresses of the recipients must be available in the table of data (Excel), even if they are not shown explicitly in the text of the message

Task 3

Click Next to go to Step 5 of the Wizard

8 Previewing the Messages

You can preview the collection of merged messages, to confirm that each one is as you require.

Exercise 6 Previewing the messages

 Look at the first message

 Check that the variable data from the Excel worksheet has been inserted properly

 Check each message in turn

Task 1

In Step 5 of the Wizard, preview the first merged message

Step 1

Look at the first merged message

Step 2

The merge codes have been replaced with text and numbers from the first record in your worksheet

Step 3

Confirm that the message reads well, and that the merged data appears in the correct places

Step 4

If necessary, click Previous to return to an earlier step in the Wizard and make corrections

Task 2

Inspect all the merged messages, making changes as necessary

Step 1

Use << and >> buttons to page between the messages and inspect the contents of each one

Step 2

There should be one merged message for each recipient chosen from the worksheet

Task 3

If you find a problem with one or all the emails, this is the time to abandon the merge. Go back to the original documents, the data in the Excel spreadsheet and the text in the Word document, and correct them, then run the merge again.

Task 4

Figure 10 Previewing the merged messages

9 Sending the Messages

The next step is to have Word send out the messages, each to the correct recipient, via Outlook.

Exercise 7 Sending out the emails

 Specify where the email addresses are listed

 Check the subject line and the formatting for the emails  Send

Task 1

In Step 6, make final settings for sending the emails

Step 1

In Step 6 of the Wizard, click

In the Merge to Email dialog, specify which column in your data table contains the email addresses where the messages are to be sent

Step 2

Give a suitable phrase which will appear as the Subject line of the emails

Step 3

Task 2

The message/s are sent immediately

Step 1

When you click OK , the messages are sent out by Outlook

Step 2

Check in the Sent mail folder of your Outlook, to see the messages that have been sent

Task 3

You should save the Excel workbook and the Word text document, but you may not need to keep the document with all the separate emails

Figure 11 Sending the emails

10 Date Formats in email Messages

If some of the data from the Excel workbook is dates, and is formatted as Date in the worksheet, you should check carefully how the dates appear in your email messages.

By default, dates appear in US date format, so 3rd _{August 2011 appears as}

8/3/2012 which may not be suitable for UK readers. One way to work around this is to type the dates as text in the Excel workbook, in the format required.

Alternatively, you can edit the mergefield to force the date format: Press ALT+F9 (to reveal the field as a code)

Type inside the {MERGEFIELD} to look like this: {MERGEFIELD “EventDate” \@"d MMMM, yyyy"}.

The conventions about d, m, M and y codes are the same as when creating custom formats using Excel.

Use the field name of your dates to replace EventDate, and make sure you enclose it with double quote marks.

11 What Next?

Courses to help you with word-processing and related topics are described below. In all cases, please refer to the IT Learning Programme web page (via

11.1. Word-processing Courses

Word: Creating Professional Documents Word: Creating Your CV

Word: Charts, Pictures and Diagrams Word: Managing Your Thesis

Word: Building Your Long Documents

11.2. Excel Courses

Excel: Fundamentals

Excel: Functions and cell referencing

Excel: 3D formulas, charts, histograms and sharing data Excel: Analysing your spreadsheet data with pivot tables

Excel: Analysing spreadsheet lists, external data and what-if-analysis Excel: Working with arrays, macros and VBA

11.3. Nexus Email Courses

Nexus: An introduction to Outlook 2010

Nexus: Essential features for email and calendars

11.4. Office 2010

“What’s new” booklets for Word 2010, Excel 2010, Outlook 2010, Office 2010. There are self-study booklets for you to work on at your own pace, in the ITLP Portfolio at http://portfolio.it.ox.ac.uk. Exercise files are also downloadable from the same website, for you to practice the techniques described.

11.5. Downloadable Course Materials and More – the ITLP

Portfolio

These course materials are available through the ITLP Portfolio, at http://portfolio.it.ox.ac.uk .

Each course pack includes the course handbook in pdf form and a zip folder of the exercise files that you need to complete the exercises. Archive versions of the course book may also be useful if you use an earlier version of the software. The ITLP Portfolio helps you find articles, videos, resources and weblinks for further IT study. For some resources, you will be asked for your Oxford (SSO) username and password.

11.6. IT Services Help Centre

For Help Centre opening times, visit www.it.ox.ac.uk/help/gettinghelp/ and follow links to the General Helpdesk, or contact them by email on

IT Learning Programme Mailmerge for emails:

using Word, Excel and Outlook together Pamela Stanworth

More from the IT Learning Programme Word-processing

Spreadsheets Mail and Nexus and plenty more

http://www.oucs.ox.ac.uk/itlp

[email protected]

This presentation is made available by Pamela Stanworth under a Creative Commons licence:

Attribution-NonCommercial-ShareAlike CC BY-NC-SA