In this post, we will see how to mail merge Excel to Word. Let’s say you have data in Excel file, and you want to create multiple Word documents from that, each individualized based on data from Excel, then this tutorial will help you do that.
Mail Merge is a very useful feature of MS Word which lets you quickly create Custom Letters, Email Messages, Envelopes, Labels, and more. You can merge the information from Excel Sheet to Word document with few clicks. This tutorial explains two simple methods about How to mail merge from Excel to Word.
You can add all the data to different fields of Excel sheet, run these mail merge tutorials, and get output Word documents. In case you add new records to Excel, then you can run mail merge on only those records as well by just providing start and end number of the records.
How to Mail Merge Excel to Word by using Wizard?
This method lets you merge Excel Data into Word file easily and safely. You can follow the below steps to create a mail merged document from Excel to Word.
Step 1: Just click the Mailings Menu and then click Start Mail Merge button. Now from various options, choose “Step-by-Step Mail Merge Wizard” from the list as shown in the below screenshot.
Step 2: First of all, create a database in the Excel Worksheet and save it to any location on your PC.
Step 3: Follow the simple wizard and create your own Letter or Email Message, Envelope, or Label by clicking the desired button. In this case, I am selecting “Letters” (step 1 of 6) and click Next button.
Step 4: Now Type a new letter or use the existing one or start a new one using a template (step 2 of 6) by clicking the Next button.
Step 5: Now choose the existing list (step 3 of 6) from the Next screen.
Step 6: In this step, you have to click the More Items and place your cursor at the desired position and select the fields from the sub menu (step 4 of 6).
Step 7: On the screen (step 5 of 6), you can find the particular recipients or edit the recipient’s list.
Step 8: Finally, you can “Print” or “Edit Individual Letters” (step 6 of 6) to start the merging process.
How to Mail Merge Excel to Word Manually?
This manual method for Mail Merge is also as easiest as above for merging Excel data into Word.
Step 1: In this step, you have to first create an Excel Database file as usual. Now after entering the data in it, save it to any location.
Step 2: In step 2 type your word document (Custom Letter, Email Message, Envelope, or Label, etc.).
Step 3: Now click Mailings menu and click Select Recipients and then finally Use an Existing List. A new dialog box will open and here you can browse for a database file (Excel File). Select the file and click the Open button. Now select the desired sheet (if more than one sheets are present in the Excel file).
Step 4: Now place your cursor at the desired position in the Word document and click Mailings menu and then Insert Merge Field. Choose the desired field and click it to place at the current cursor location. Similarly, add more fields at the required position.
Step 5: Finally, you can Preview the results by clicking Preview Results button. After that, you can Merge the Final Document to Printer, Edit Individual Documents, and/or Send Email Messages to the respective persons by clicking the Finish & Merge button as shown.
You also have an option to choose the All Records, Current Record, or Record no. x to Record no. y (here x and y are integers) when finishing merging.
How to Split Each Document to a Separate Word Document:
Method 1:
Here is a simple method which you can use to create separate documents from Single Mail Merged document. To do this, open the main document whom you want to use for Mail Merge. Now at top of the page apply Heading 1 to the First word or few words. In this case, I applied Heading 1 to Dear <<Name>>, because I want to save each document with different name automatically.
Now to match the Heading 1 text formatting with rest of the document by changing its default properties temporarily, or you can select the Heading 1 text and press Ctrl+D and change font settings and font colors as required. Now from Mailing Tab, use the mail merge function and create a new Master Document (combined document).
You go to the View Menu and change the view to Outline. Here Outlining Menu will open. Now select the whole document by pressing Ctrl+A key and click Show Document on the menu. Now click Create button to create Sub-documents.
Now save this Master Document with any name and click Save button. All the letters will be saved in the same location with different names in split documents separately.
Method 2:
In this method, you can use Merge and Split Add-in to split the merged document into separate documents. Just download and install this Add-in by following the instructions. Now run the MS Word and create your main document as stated in the above “How to Mail Merge from Excel to Word” tutorial.
Now after creating the main document and connecting Excel as a database to it and save the document. Now click the Merge & Split button of this Add-in on Mailings Menu. and select the output location for splitted documents and choose the desired field for file naming. Now choose DOCX or DOC from the Documents Options section and click Split to Documents. Now all the splitted documents will be saved in the location of your choice with different names.
Method 3:
Here is one another method to do the split job easily with the help of VBA Macro. You can go to this link and select and copy the code. Now open MS Word and press Alt+F11 key from your keyboard to open the Word’s own Visual Basic editor. Now paste this code in a blank space. Close the editor and open the final merged document. Now Press Alt+F8 and select BreakOnPage macro from the list and click Run button. You can also assign the shortcut key to this macro if required.
Your document will be splitted by page breaks and automatically named to “test_1.doc, test_2.doc, test_3.doc” and so on. You can change the prefix “test_” with any word of your choice by going to VBA Editor and change the “test_” with the desired word. All the splitted documents, in this case, will be saved in C Drive and you can change this output path by going to VBA Editor again by supplying your own path to the below code.
ChangeFileOpenDirectory "C:\YourFolder\"
Method 4:
In this method, I am using a free template to split multi page Word Document. As in our case, it is final merged output file in Word format. First of all, just copy the “Document Splitter.dot” file in the MS Word Startup folder. You can find Word Startup folder in your PC easily by searching or going to the below location.
C:\Users\CurrentUser\AppData\Roaming\Microsoft\Word\STARTUP
Here CurrentUser is your Profile folder in C:\Users folder\.
After copying the file, open the final merged file and now click Alt+F8 and choose CallUF Macro and run it. The below dialog box will open.
Here you can choose the Delimiter from predefined (Section Break or Pages or Heading 1) as well as a custom delimiter (user defined). Choose the output directory by clicking the Pick Directory button on the shown dialog box. Now type a Prefix word you want to give to the output files. Press Validate/Confirm Actions button to check the command for errors (if any) and also for a number of parts. Now click OK button to start the splitting process. Your final Mail Merged document will be splitted into multiple parts based upon the delimiter you choose in the output folder.
Conclusion:
In my opinion, both of the above Mail Merge from Excel to Word processes are quite good. You can try any of the above to achieve your goal of mail merge. I usually prefer a Manual method to do Mail Merge from Excel to Word whenever required.
You can also Split the final document into multiple Word Documents by using this tutorial. I have discussed 4 methods, and I personally prefer Merge and Split Add-in method to do the splitting. Rest of the methods are also good and easy to understand.
Also Read:
