digest
Short Summary
Main recommendation: Convert your Excel customer data into an official Excel Table (named "Customer Balances"), then connect a Word letter to it using Mailings > Select Recipients > Use an Existing List so the mail merge stays dynamic when records change. The video demonstrates building a past-due balance letter that filters to customers owing more than zero, formats the balance as currency in Word, and can print or email each letter via Outlook.
Key Points
- Using an Excel Table rather than a plain range makes the mail merge update automatically when you add, remove, or replace records.
- Word's Edit Recipient List filter (Balance Due > 0) limits letters to customers who owe money, and the filter stays live as the data changes.
- Merge fields such as Address Block, First Name, Last Name, and Balance Due pull directly from the table; Match Fields can fix mismatched column names.
- Balance Due numbers need a numeric format switch in Word because merged numbers do not display currency formatting by default.
- The email option requires an Email column in the Excel data and Microsoft Outlook installed on the computer.
Details Worth Noting
- Concrete instructions:
- In Excel, click any single cell in the data, then press Ctrl+T (or Insert > Table), and confirm with Enter.
- Go to Table Tools > Design and name the table "Customer Balances," then save and close the Excel file.
- In Word, go to Mailings > Select Recipients > Use an Existing List, navigate to the workbook, double-click it, and click OK.
- Click Edit Recipient List, open the Balance Due column drop-down, choose the advanced condition option, set Field = Balance Due, comparison = greater than, value = 0, and click OK.
- Remove the sample inside address text, then insert Mailings > Address Block; use Match Fields only if the fields do not map correctly.
- Place the cursor after "Dear" and before the period, then use Insert Merge Field for First Name and Last Name, adding a space between them.
- Enable nonprinting characters (Home tab) to position merge fields precisely.
- For currency formatting, press Alt+F9 (or right-click > Toggle Field Codes) to view the merge code, add a space and the switch # "$#,##0.00" after the Balance Due field, then toggle back and use Preview Results to confirm.
- To print, choose Finish & Merge > Print and select all records; to email, choose Send Email Messages, select the Email field, enter a subject line such as "Past Due Balances," and choose the mode (attachment, plain text, or HTML).
- Save the Word document; it will prompt to update the merge data when reopened, and clicking Yes refreshes the letters.
- The presenter says the email option only works with Outlook; the sample emails shown are not real, so he advised not to actually click send during the demo.
- The video also tests dynamic updating: changing balances to zero reduces the letter count from seven to five, and adding a new record in the Excel Table (June, Seattle) adds a sixth letter after refreshing.
- For number formats beyond currency, the presenter suggests searching for "Word mail merge currency format" or "Word mail merge percentage format" online.
Bottom Line
Building the letter around a named Excel Table and a filtered Word merge gives you a reusable, self-updating past-due notice system that scales as your customer list changes.
