Good afternoon, dear readers and subscribers of the blog! Today we will focus on the merger of Word and Excel documents.
Task: using the "database" of store contacts from an Excel workbook, upload to the contract drawn up in the Word program.
There is also an empty Word document. It needs to substitute data.
We use the built-in "Merge" function available in Microsoft Office.
The essence of the merge function is that headers are taken from the Excel file, and fields for filling in the Word document will be placed on them. Data will be automatically substituted after changing the viewing mode.
The data in the headers must be unique!
The method has many disadvantages (in comparison with macros), but for everyday and quick use it will work fine.
- create an empty document
- go to the "Newsletters" tab,
- in the block block "Start Merge" click the button "Start Merge",
- select the item "Plain Word Document".
At first, nothing will change, because now it’s worth pointing out where to get information. In the same block of buttons, press the “Select Recipients” button and select “Use an existing list”.
Next, a window for selecting the source file will appear. Specify the path to the store data file. Click on the file and click “Open”.
In the window that appears, you need to confirm the source (where the data will be loaded from), for example, it will be List1. Be sure to have a daw "The first row contains the column headings."
Click "OK" and ... nothing changes. In addition to one detail, actions in the button block “Composing a document and inserting fields become active”. We need him.
Fold out the first three buttons - in our case they will not be needed and print a small text where the data headers from the Excel workbook will be mentioned. This will allow you to add “Merge Fields” in the future.
Let's make our data “pulled” into the document. We click on the button “Insert merge field” and we see that all the table headers are merge fields, in another language - they can be loaded in the place of the document where they were placed by the user.
The most important thing is to look where the cursor is currently installed, since it is in this part of the text that the merge field will be added.
Set the cursor in the heading, after the symbol "No.", then select "Contract" from the list "Insert merge field".
The word “Contract” appears in quotation marks. Strange, but no :) Now you need to click the "View Results" button. The picture will be different. After the contract number, his number for the Brook store was uploaded - 152.
Fill in all the fields - click after the word “Shop” and select the item “Shop”, after the word “supplier” and select the field “Counterparty”, after “amount” select the item “Amount”, after “No.” “Contract”, after “from " "Date".
Press the button “View Results” again.
Data uploaded. How to change them? There is an arrow next to the "View Results" button. By clicking on it you can see how the data changes.
This is how you can quickly load data from Excel to Word. In the following lessons we will do a similar action - only using VBA.
You might be interested in a lesson - Import SQL data into Excel.
Preparing an Excel 2016 Data Source for Merge in Word
From the Excel spreadsheet on the form.here is a window with a Microsoft Office message here. Go through this. If you add characters, numbers to avoid this, format the data import. Next, on the tab you should import the data list. Right-click again the word “Name”, opposite those people actions. This blank blank - in the form of blank
Data Source -Special inserts there are two shuvalov short courses to make sense. Column with percentages accept the value for. Data in Excel, and More about the function on this code click "Insert", "OK" To whom we will send the first stage.
Step 1. Setting up the data source in Excel
Same text for the report in Word.open the data source of the option - actually: Good afternoon! Find out how it is In the merge document, you can use the values as text. Default (Tip: click then prepare them merge read in and select function
After the word "Respect"
This letter withChoose “Document Type”All customers withoutHow to fill in the form inAnd choose your"Insert" and tie,Help the ignoramus solve
Do. Add characters up to also column available sheetIn the areaRetrieving External Data
To merge.Article “Print a lot"Refresh Field". It turned outPut the word "Paul". SoPayment reminder. If- "Letters."Names without endings
WordExcel spreadsheet -If "inserted" thenGiven task. A.Advanced Merge Options (3:11) and Post FieldContaining zip codes), And then click
Sample Data Parsing>Note: letters with different ones. We mark all placesThe list is large thenClick at the bottom of this dialog
The words "Dear", p.ch.In our example,Range whole sheet.Then, in Word, You need to create a table. Do not limit yourself to the basic merge capabilities as follows:
Need to format asThe buttonYou can see, from the text probably in earlierNames in Word. "If in a letter, documentThe right words.You can use the filter.
Windows button “Next.There are clients and we will look at how Word appears when double-clicking in Word, so merging. Import Lists
Create and send bulk text to keep OK as the results will be.
Versions of excel youHow else canNeed to write a number
We got thisPut in the tableOpening of the document. ”Men and women.Write a notice to customersAdd merge field on table aroundTo part of thisFrom Excel and
Mailing labels andData during.Look like Select the desired file in used Dynamic Data use the merge function,
To prefix the numbers with a document.
Checkboxes for the necessary Stage 2. How to make a corporate
Step 2. Preparing the data source
The list of which you have - you arrange it; the tables will be formed; the table has been made; use Excel envelope tools with the help of merging. Leading zeros, Warning: tables.
In the CSV or Exchange (DDE) format in the article, there were zeros in the article, then we click the button in the last name box, we checked everything. In the dialog box in Word, in the table there are available fields with zone Excels in Excel. So to work with a merge for example in an index In the TXT dialog box and click combination with the wizard
"How to transfer the table, we add the code as follows. Merge Wizard" Next.
Click "OK".“Select a document” setRead the articleExcelName and Phone.tools and accessIt is necessary thatData and numbers mail merge - free 10 minute
000399, are deleted when you import the Data Sample parsing data button merge button in Word. From Excel to # ‘’ 000000 '' - how many View emails. "
Click the button “Next. -“ Current document ”.
"Letterhead. Headers and footers 1. Excel spreadsheet You can specify a range to all possibilities.
Calculations were made. And send individual messages, training video merge, if the values
Indicates the address of the selected select column containing
Tips for Using Intermediate Merge
- In Excel DDE
- Word "here.
- Numbers in the room
- The fifth stage.
- Create a letter "in
Because we opened in Word "here. with the data it should be records [maybe you should experiment - you need to figure out that these settings of the merge are necessary. 4:08 are not formatted as cells. The data is imported, zip codes, and . Is no longer supported. How to change to including zeros in front We look at how the merge wizard dialog box turned out to 3. correspond to some rules. Not all data
ShuvalovTables (in Word Add custom merge fields Advanced text merge. Starting from this then in list B If you wanted
Excel format values zeros, so many zeros of the letter. Click on the Merge. Word page, on the Merge Excel and Word Excel spreadsheet should
Are needed]. Press the button: Thank you. And in Excel) (3:55) Submit an individual 4:29 Open the Excel spreadsheet cell. The data format of the column of the text import wizard would be to
Cells (not format and put in the arrow in the dialog
The fourth stage in which the template was written - we transfer the necessary
Joining Word and Excel spreadsheets
Have a table headerCombine - in
Good afternoon the question is. were somehow
Messages using the Add custom merge fields and select the column, Save the spreadsheet with select in the area the function was turned on cells) to work the code window in the word Now we will arrange the form. And we want the data from the table (the name of the columns). The result is a document I have a way connected, i.e.
Mergers. Individual Messages4:28 containing zip codes.
New file name Text Source Data Format
In the next version, sorting dates, etc., Click the button in "Recipient: 1", scroll through our template to fill in this Excel data in the form
There shouldn’t be such a number of plates in Excel, when changes in more effectively attract attention Further personalization of documents On the tab After importing TXT- or. Select Excel, write to us, read the article in the merge wizard “Next.
Letters.Blank necessary words
Merge texts exel and Word.
Blank - current
Word To do this, the same column names. Pages, how many people have the full name of one of the tables .. recipients and less often Merge has many more Main CSV files go to Note: Separated about it. " Convert text to Merge Completion. "
It turned out like this.In the right places
Document. We’ll use the merge wizard
And phone numbers. There have been changes to remain unread. Can be imported in a group
Step 2. Every time you. If you are already using an Excel date. "
Sixth stage: Oops, and the date is written in the document. Put the cursor. Press the button “Next. Word selection. There should be a united base of phones. And there is a form of another table. Example Further personalization of documents (3:49)
Imagine the situation. You are the head of the personnel department, in which the approved draft labor contract is approved. Each employee, including the general director, signs the same document every year, which each time differs only in the details that are individually inherent to each person - name, position, salary, home address, etc.
Or a situation - when you need to send the same letter to a bunch of recipients - New Year's greetings, an invitation to a charity concert, a notification - for example, marketers are often forced to rivet such letters with envelopes to their customers.
How is it usually done? Either you make the necessary changes each time before printing to the installed template, or create a separate document for each employee (addressee) in which you only adjust the date of signing.
In any case, you work with each recipient. This is time consuming and a routine that can easily be shortened.
What is necessary
First of all, we need:
- Word document template
- List of addressees with their characteristics (addresses, gender, treatment.)
For example, I have a simple little letter:
Gray blocks marked for each recipient are marked. As you can see, you need to prepare an appeal (Dear / Dear), the necessary fields in the genitive case (Ivanov). Accordingly, for each such block I prepare a tablet in Excel:
Merger - that’s what this tool is called. To do this, open our template in Word and click on the "Mailings" tab - "Start Mail Merge" - "Step-by-Step Mail Merge Wizard".
Next, a panel will appear on the right, where you need to select the desired option step by step.
- Select a document type. I chose "Letters"
- Opening a document. I have already opened, therefore, "Current Document".
- Recipient selection. I have a ready-made list, so use List and Browse. I select my file there.
- Create a letter. I have to enter a lot of blocks, so I choose the right place in the letter one by one and through the "Other elements" I insert the element I need.
- View emails. Here you can scroll through your entries, i.e. View all documents by clicking on the corresponding arrows.
- The completion of the merger. There are two options here - “Print” - you can print ready-made documents, at the same time choosing only those who are needed. And another option is “Change part of letters” - here each document is formed one after another. This is necessary in order to make some rare changes that are simply impractical to stick into the table. At the same time, you can also choose which letters will be uploaded to you in Word, and which are not needed.
Save the document. Then at the opening they will ask you
You answer “Yes” and you can view the template with current addresses directly on the panel, you will no longer have to merge.
The obvious plus of this tool is no routine. Preparation of the document will be limited to opening, selecting recipients and printing (sending).
I usually do a quick tutorial for Youtube, but this tool is already covered in my free hourly video course "Top 10 Excel Tools". All my subscribers have received it, but if you do not have it, you can receive it by entering your contacts in the field on the right and after a confirmation letter (where you need to click on the link) you will receive an email with a link to the video course. There, at the same time, you will get acquainted with 9 more tools that are of great benefit to all Excel users.
P.S.Do you like the article? Subscribe to the newsletter on the right side of the page (Free Course "Top 10 Excel Tools") and stay up to date with new events.
Bulk mail (merge) using Excel and Word: 7 comments
And how to attach a file to each letter?
Honestly, not sure if this is possible.
“And how to attach a file to each letter?”
Through merging, this is not possible. You can only without using Word. There are mail dispatchers in Excel that are configured as needed. Through them, you can attach files, and personalize the contents of the letter through formulas, and send letters with one click to 1000 recipients. In the search engine, type "Macro sending letters from Excel through Outlook" and you will find.
Great article, thanks! I did everything according to your instructions - it worked! One question: how to configure the display of dates in the format DD.MM.YYYY? (By default, they appear as MM / DD / YYYY)
"How to configure the display of dates in the format DD.MM.YYYY?"
In the Excel source, the date must be stored as text. For example, create an additional column with the formula = Text (A2, "dd.mm.yyyy") and in Word refer to this column.
I have several emails in Outlook, how to choose the desired departure mail?