Merging Microsoft Word documents. Data sources that can be used for mail merging Create a data source in Word

From the article in the previous issue, we learned that the merge method allows you to create a data source using Word. In this article, we'll look at how data source information can be obtained from an Excel table.

The data source can be used in versions of Word 2000/2002/2003/2007, but the process of creating it is the same for any version of Word. So, let's describe the algorithm for creating a data source:

1. Open Excel.
2. In the table that appears, in cell A1, enter the name of the first field (for example, a country), in B1 – second (for example, index) etc. (Fig. 1).

Rice. 1. Data table

Using a tableWord 2000/2002

1. Open or create a main document in Word.
2. Merger button ( Merge dialog box).
3. Merger in the second step select the button Get data, and from the list - Open data source(Fig. 2).

Rice. 2. Dropdown list Open data source

1. In a new dialog box Opening a data source from the dropdown list File type select BooksMSExcel.
2. Open the selected file by clicking the button Open.
3. In the dialog box that appears MicrosoftExcel(Fig. 3) specify a range or name.

Rice. 3. Window MicrosoftExcel

The resulting file Excel has become, as it were, “tied” as a data source, so now you can insert merge fields into the main document, and also merge the data source with the main document as explained in the previous article.

Rule for selecting records for inclusion in the merge process with the main document

Records that meet certain specified criteria can be included in the merge process. Links are used to select records OR, AND.

To set selection criteria:

1. Open the main document.
2. Select from the toolbar Merger button ( Merge dialog box).
3. In the dialog box Merger in the third step select the button Selection records.

As a result, a dialog box will appear Selection of records.

1. In the tab Selection of records dialog box Selection of records in column Field From the drop-down list, select the name of the field on the value of which the condition will be applied.
2. In column Operator select comparison operation.
3. In column Meaning Enter the value to compare with.
4. After setting all the criteria, close the window Selection of records by pressing the button OK(Fig. 4) .

Rice. 4. Window Selection of records

1. In the dialog box Merger click the button Merge or on the toolbar Merger press the button ( Merging into a new document).

Using a tableExcel as a data source inWord 2003

1. On the menu Service select team Letters and mailings, and then the command Merger.
2. In the task pane that opens Merger, using the button Further, go to the third stage of the wizard Recipient selection, then click on the command
3. Find the required Excel spreadsheet file and open it by double-clicking on it.
4. Confirm that the first row of data contains the column headers (these are the merge fields) (Figure 5).

Rice. 5. Window Select table

Note. In the window Merge recipients you can edit the entries.

1. Moving on to the fourth step, select the command
2. In the dialog box that appears Add a merge field(Fig. 6) select a field from the list, having first positioned the mouse cursor at the place in the main document where the merge field should be located.

Rice. 6. Dialog box Add a merge field

To commit about Selecting records to include in the process of merging with the main document in Word 2003 is necessary, going to the third stage, select the command, and in the dialog box Merge recipients(Fig. 7) uncheck the R box for the entry to which the text of the main document should not be sent.

Rice. 7. Dialog box Merge recipients

Using a tableExcel as a data source inWord 2007

1. On the tab Newsletter in Group Merger select team Select recipients.
2. Select (Fig. 8), find required file in the dialog box Selecting a data source and open it.

Rice. 8. Team selection Use existing list

Note. After selecting a file, the tab commands will become active

1. Click on Change list of fields when the list changes.
2. Click on Insert merge field and select a field from the list, having first positioned the mouse cursor at the place in the main document where the merge field should be located.

Selecting records to include in the mail merge process in Word 2007

1. In the tab Newsletters in Group Start merge Press Select recipients and select from the menu drop-down list Use existing list.
2. Find the required Excel spreadsheet file and open it by double-clicking on it.
3. Confirm that the first row of data contains the column headers (these are the merge fields).
4. In the tab Newsletters in Group Start merge select Change the list of recipients.

5.In the Merge Recipients dialog box (Fig. 9), uncheck the R box for the entry to which you do not want to send the text of the main document.

Rice. 9. Window Merge recipients

Key Benefits of Using Excel as a Data Source for the Merge Process

1. Saving time, because there is no need to perform the same work repeatedly.

2. Automation of the workflow by creating multiple copies of a document template with various details.

3. Convenience and ease of performing the work, which consists of only three stages: creating the main document, creating a data source and directly merging them.

G.A. Serova, prof. RSUH

Now you need to create a merge data source (addresses and last names of email recipients).

1. In section Selecting Recipients window Merger task pane, select Creating a list(Fig. 8.6).

Rice. 8.6. Window Selecting Recipients masters Merger

2. Click on the command Create In chapter Creating a list. A dialog box will open, shown in Fig. 8.7.

The merge data source is a database consisting of records, based on each of which a specific copy of the letter will be generated using the main document. All records have the same data fields.

Some of the most common fields are pre-listed in the data source creation window. You can remove unnecessary fields from the database and add your own.

Rice. 8.7. Creating a Data Source

Other section items Selecting Recipients allow you to select an existing database, previously created in Word or in an application such as Access, or an address book as a data source.

Note The presence of extra fields in the data source does not in any way affect the result of the merge, but it slows down the program.

3. Click the button Settings. A dialog box will open, shown in Fig. 8.8.

4. Highlight an item Appeal.

5. Click the button Delete erase the highlighted field.

Rice. 8.8. Setting up data source fields

Rice. 8.9. Dialog window Merge recipients

6. Repeating steps 4 and 5, remove all fields except Name. Surname, Organization, Address 1 And Index.

7. Click the button OK.

8. Click the button Close dialog windows.

9. In the dialog box for saving the document that opens, enter the name Guests and click on the button Save. The image shown in Fig. 8.9 dialog box Merge recipients, which currently has no entries.

10. You will definitely have to enter information into a data source at some point. Click the button Change... to do it now. A data entry form will open. Let's add some entries. Later, the merge will generate exactly as many copies of the master document as there are records in the data source.

11. Fill out the form fields.

12. To add another entry, click the button Create an entry.

13. Enter a few more entries, transferring into them the information presented in the table. 8.1.

TABLE 8.1. Merge data source

14. Click on the button Close to save the database and then on the button OK window Recipients.

Note To change data source information, click in the toolbar Merger on the button Recipients, and then in the dialog window that opens - on the button Change. The data source form will open. To navigate through the source records, use the buttons First, Back, Forward, Last at the bottom of the form.

Rice. 8.10. Toolbar Merger

Word can pull merge data from a variety of sources. As part of a set Microsoft Office, Word easily accepts data from Outlook, Excel and Access. Other sources may be used, including web pages, text files OpenDocument and delimited data files saved as plain text. If you don't already have a data source, you can create one in Word.

Important: Access to named data sources in an HTTP location is not supported. Before using a file as a data source for a merge, save it to your local hard drive.

Merge Data Sources

Below are some data sources that you can use for mail merge in Word.

Other database files

You can also use other data sources for merging. To access these sources, run the Data Connection Wizard.

Once you set up your data source, you can create labels, envelopes, letters, and emails using mail merge.

additional information

Still have questions about merging in Word?

Help us improve Word

Do you have ideas for improving mail merge or other Word features? Share them on the page

7 Lecture No. 8

Merging documents.

Mail merge is used when you need to create a set of documents, such as address labels or letters on letterhead, that are sent to a large number of customers. Each letter or sticker contains both general and individual information. For example, the letter should address the customer by last name. The individual information for each letter or sticker comes from a data source.

The merge process consists of several general steps.

Setting up the main document. The main document contains text and graphics that are common to all versions of the master document, such as a return address or a salutation on letterhead.

Connecting a document to a data source. The data source is a file containing information that must be inserted into the document, such as the names and addresses of the recipients of the letter.

Refine the list of recipients or items. Microsoft application Office Word creates a copy of the master document for each data file item or record.

Adding text placeholders (merge fields) to a document When you merge, the merge fields are populated with data from the data file.

Previewing and completing a merge You can preview each copy before printing the entire set of copies of a document.

Tab commands are used to merge with mail Newsletters.

You can also merge using the task pane Merger, which allows you to carry out the entire process step by step. To use the task pane, in a group Start merge on the tab Newsletters select item Start merge, and then point Step-by-step Merge Wizard.

Setting up the main document

Launch the Word application.

By default, a blank document opens. Let it remain open. If you close it, the commands you need to perform in the next step will no longer be available.

On the tab Mail in Group Merger select team Start merge.

Click the document type you want to create.

For example, you can create the following document types.

Set of envelopes The return addresses on all envelopes are the same, but the recipient's address is unique in each case. Select an item Envelopes and on the tab Envelope options dialog box Envelope options Please indicate your preferences regarding envelope size and text formatting.

Set of address stickers Each sticker contains a name and address that are unique. Select an item stickers and in the dialog box Sticker options please indicate desired type stickers.

Set of documents on letterhead or messages Email The basic content of all letters or messages is the same, but each of them contains information intended for a specific recipient, for example, name, address or some other information. To create these types of documents, select letters or emails.

Directory or folder Each item displays the same type of information, such as a name or description, but it is unique to each item. Click catalog to create this type of document.

Resumption of merger

If you need to interrupt your merge work, you can save the main document and resume the merge later. Microsoft Office Word saves the data source and field data. If you used the task pane Merger, Word will return to the point where you want to resume the merge.

When you're ready to resume the merge, open the document.

Word will ask for confirmation to open the document, which will run the SQL command.

Since this document is connected to a data source, click Yes. The text of the document appears along with all inserted fields.

Connecting a document to a data source

To perform a data merge in a main document, you must connect the document to a data source or data file.

Selecting a data file

On the tab Mail in Group Merger select team Select recipients.

Follow these steps:

Open the application document Microsoft Word. This document must contain one table. The first row of the table should contain the headings, and the remaining rows should contain the entries that should be included in the compound document. You can also use a header data source as a data source (Header data source. A document containing a header row (or record) that is used to merge the specified data source with the main merge document.).

Refining the list of recipients or items

If you are connecting to a specific data file, you may not want to transfer the data of all data file records to the main document.

To limit the list of recipients or use a subset of data file items, follow these steps:

On the tab Mail in Group Merger select team Edit list.

In the dialog box Merger destinations

Selecting individual records This method is most useful if the list is short. Select the check boxes next to those recipients who should be included in the list, and clear the check boxes next to those recipients who should be excluded from the list.

If you know that you only want to include a few records in the master document, you can clear the check box in the title bar and then select only the records you want. Likewise, if you want to include most of the list in your document, select the check box in the title bar, and then uncheck the entries that you don't want to include.

Sorting entries Click the column header of the item you want to sort by. The list is sorted alphabetically in ascending order (A to Z).

Filtering entries This feature is useful if the list contains entries that you do not want to see or include in the master document.

Add text placeholders (merge fields) to a document

After you connect the main document to the data file, you can enter document text and add text placeholders to indicate where unique data should appear in each copy of the document.

Text placeholders, such as an address or salutation, are called merge fields. The fields in Word correspond to the highlighted column headings in the data file.

The data file columns represent categories of data. The fields added to the main document are text placeholders for these categories.

Data file lines represent data records. When you perform a mail merge, Word creates a copy of the main document for each record.

When placing a field in the main document, it is implied that a certain category of data, such as a last name or address, should appear at its location.

Note. When you insert a merge field into the main document, the field name is always enclosed in double angle brackets (""). In compound documents, these brackets are not used for emphasis. They only allow you to distinguish fields in the main document from normal text.

What happens during a merger

When merging, the data in the first row of the data file replaces the fields in the main document, thereby forming the first composite document. The data in the second row of the data file replaces the fields to form a second compound document, and so on.

Examples of working with fields

You can add any column heading from the data file to the main document as a field. This gives you some flexibility when creating letterheads, labels, emails, and other compound documents.

You can combine fields and separate them using punctuation marks. For example, to generate an exact address, you need to insert the following fields into the main document:

"First Name Last Name"

"House and Street"

"City", "Region", "Postal Code"

For common use cases, such as address blocks or greeting lines, Word provides complex fields—a combination of multiple fields. Examples are listed below.

The Address Block field is a combination of several fields, including first name, last name, house and street, city and zip code.

The Greeting Line field may include one or more name fields, depending on the greeting words used.

The content of each of the complex fields can be customized accordingly. For example, you can choose a formal name for the address (Mr. Andrey Alfirovich Jr.), and use “For” instead of “Dear” in your address.

Mapping Merge Fields to a Data File

To ensure that Word will find a column in the data file that corresponds to each address or greeting element, you must map the mail merge fields in Word to the columns in the data file.

To match the fields, select Field selection in Group Compose a document and insert fields on the tab Newsletters.

A dialog box will open Field selection.

The address and salutation elements appear on the left. Column headings from the data file appear on the right.

Word looks for the appropriate column for each element. The illustration shows that Word automatically found a match for the column Surname data file field Surname. But he was unable to contrast the other elements with each other. Specifically from this file Word data did not find a match for the field Name.

In the list on the right, you can highlight the data file column corresponding to the element on the left. Now the illustration shows that the column Name matches the field Name. There's nothing wrong with the fields Appeal, Unique identificator And Surname no match found. A master document does not need to use every field. If you add a field for which there is no data in the data file, it will appear in the master document as an empty text placeholder—usually an empty string or a hidden field.

Entering content and adding fields

In the main document, click where you want to insert the field.

Use the group Compose a document and insert fields on the tab Newsletters.

Add any of the following items.

Address block with name, postal address and other information

Select an item Address block.

In the dialog box Inserting an address block select the address elements and display formats you want, and then click OK.

Field selection, then Word couldn't find some information needed for the address block. Click the arrow next to (no match)

Welcome line

Select an item Welcome line.

Select a salutation line format that includes the salutation, the name, and the punctuation mark that follows the name.

Select greeting line text for cases where the recipient's name is not available, for example, when the data source does not contain the recipient's first or last name but only the company name.

Click the button OK.

If a dialog box appears Field selection, then Word couldn't find some information needed for the greeting line. Click the arrow next to (no match), and then highlight the corresponding field in the data source that you want to merge.

Individual fields

Information can be inserted from individual fields, such as last name, phone number, or donor contribution amount. To quickly add a field from a data file to the main document, click the arrow next to the list Inserting a data field, and then select a field name.

To use more options when inserting individual fields into a document, follow these steps:

On the tab Mail in Group Recording and adding fields select team Insert merge field.

In the dialog box Inserting a data field Perform one of the following actions.

To select address fields that will automatically match the data source fields even if the data source fields have different names, select Address fields.

To highlight fields that always use data directly from a data file column, select Database fields.

On the list Fields Click the required field.

Click the button Insert, and then Close.

If a dialog box appears Field selection, then Word couldn't find some of the information it needed to insert the field. Click the arrow next to (no match), and then highlight the corresponding field in the data source that you want to merge.

• Custom fields from Outlook contact list

The only way to include custom contact list fields in the main document is to start a mail merge from Outlook. First you need to configure the display of the contact list using exactly those fields that should be used during the merge. After this you can start merging. Once all the necessary settings have been made, Word will automatically open and you can complete the mail merge.

Formatting Merge Results

To format the data contained in a document, select the mail merge field and format it the same way you format any other text. The selection must include double chevron quotes ( « » ) surrounding the field.

Preview and complete the merge

Once all the fields have been added to the main document, you can preview the merge results. Before the merge is actually completed, you can review the merged documents and make appropriate changes if necessary.

To view, do one of the following in a group View results tabs Newsletters :

Click View results.

Browse through each compound document page by page using the buttons Next entry And Previous post in Group View results.

View a specific document using the command Find recipient.

Completing the merger

Multiple documents can be printed or edited individually. This can be done for all documents or for a subset of documents.

Select whether to edit the entire set of documents, just the copy currently displayed, or a subset of documents identified by a specified record number. Word saves the copies you want to edit as a single file, separated by page breaks.

Saving the main document

Remember that compound documents are saved separately from the main document. It is useful to keep the main document separate because it may be useful when performing subsequent merges.

The merger procedure consists of several stages.

Stage 1. Preparing Spreadsheet Data

A table to be merged must meet certain requirements:

• There should be no merged cells in the table. It would be more accurate to say this: IF there are merged cells in the table, then you need to be prepared for the fact that during export the merge will be canceled, and accordingly, extra empty rows and/or columns will be formed, which can disrupt the structure of the table. In general, merged cells are evil :)
• all columns must have unique names to be used when merging. If the table is missing the first row with the names of the columns, then it will be replaced by the first row of data, which means it will not participate in the distribution.

As an example, let's take a table with a list of clients of the Excellent fitness club.

Stage 2. Preparing the template Word document

At this stage in text editor Word, a document is generated into which the spreadsheet data will be embedded in the future. The text of this document is a part common to all mailings.

Suppose we plan to send notification letters to all customers whose club card expires next month.

The text of the letter will be the same except for the address, club card number and expiration date. This data will be imported from an Excel spreadsheet (highlighted in blue)

Thus, at this stage, the text common to all letters is printed in a Word document.

For more convenient further work during merging, it is recommended to set the parameter Field shading to position Always to distinguish inserted merge fields from plain text. When enabled, fields appear with a gray background. Naturally, this background is not printed.

Stage 3. MS Word Merge Wizard at work

Open the letter file in MS Word.

The easiest way to merge data is to follow the Merge Wizard. In versions after Word2003 Merge Wizard startsvia buttonStart merge on the tab Newsletters

In versions before Word2007 you should execute the menu commandService - Letters and mailings - Merger.In addition, for more convenient operation versions before Word2007 you can display the Mail Merge toolbar

Once again, please note that the letter form contains only text common to all letters, so the appeal looks like We respect you!, and the card number and date are missing.

The Merge Wizard works in 6 steps.

Step 1: select the type of document to be sent, in our case it is Letters
	Step 2: select the document on the basis of which mailings will be created; it can be an open current document, a template or an existing document. When choosing an option Sample or Existing document it becomes possible to specify the desired file in Explorer. We choose Current document
Step 3: select recipients. In our case, the data source will be an Excel table, so check the option Using a list. Then using the button Review... select the desired file in Explorer
		After selecting a file, a dialog box opens with the selected table. If we need all the records, then immediately click OK. If necessary, you can sort the list, filter the required records, or find them using the appropriate commands. The filtering and search capabilities here are, of course, much poorer than in Excel, but you can make a simple selection based on text or numeric values. In addition, it is possible to select posts for distribution manually using checkboxes :) In our case, we need to set a filter by field Newsletter by value "yes" (the value "yes" appears in the table if the club card expires next month). The filter can be set by clicking on the field name and selecting “yes”, or using the Filter link in the same dialog box
Step 4: insert the required fields into the document. Before choosing one of the options offered by the Wizard, you should place the cursor in the text where you want to insert data. If you forgot to do this, that’s okay, the fields can be inserted anywhere in the document and then moved. In our case, we place the cursor after the word “Respect” before exclamation point. Since we need separate fields, we select Other elements...
		A dialog box opens to allow you to select merge fields. Select a field Name, press Insert, same for the field Surname. Closing the window Inserting Merge Fields and add spaces between the inserted fields. If the parameter Field shading set to position Always, the inserted fields will be clearly visible against the gray background. Place the cursor after the number, click the link again Other elements..., select Club card number - Insert. Insert the field in the same way Card expiration date In addition to the above fields, you must insert the end of the request th, which depends on the field value Floor. To do this, we will use a special field that allows you to insert one of two values ​​depending on the data. Place the cursor immediately after the word “Respect” and press the button Rules on the tab Newsletters and select the option IF...THEN...ELSE. In versions prior to Word2007, a similar button is called Add a Word field and is located on the toolbar Merger In the dialog box that opens, set the parameters After clicking OK, we get the result
Step 5: We view the resulting letters using the navigation buttons. At this step, you can also change the list of recipients by applying a filter or clearing check boxes. Since we applied the filter earlier, there are 3 recipients left.
		Taking a closer look at the result obtained, we see that it does not quite meet our expectations Club card number instead 001768 displayed as 1768 , and the date is not at all like ours: first the month, and then the day, although in Excel spreadsheet everything was OK. The same troubles can arise when importing cells with decimal numbers, currency format, etc. The conclusion is disappointing: when merging, the formatting of numbers and dates is not preserved. But there is a way out! The fields of the Word document into which our data is inserted are code that, firstly, can be viewed, and secondly, changed. To see the field code, for example, with the club card number, you should right-click on it (RMB) and select the command Codes/Field Values. We get the following ( MERGEFIELD "M__club_card" } We will not change the code itself, but we will add to the format. The principle of adding a format will be clear to anyone who is even slightly familiar with creating a custom format in Excel. To ensure that a number always contains six digits, the format must consist of six zeros: ( MERGEFIELD "M__club_card"\# "000000" ) . Now right-click on the field again - Update Field, and we see the number in the required format. We do the same with the date.( MERGEFIELD "card_expiration_date" \@ " D.D. MM. YYYY " ) And we get the final version More information about Word field codes can be found in Word Help or at