How to Use Mail Merge for Variable Quantities and Container Labels in Word
Question details
The user needs to generate one delivery label per container using mail merge, accommodating varying quantities, container sizes, and total packages for different customer orders.
- Product
- Word
- Device & OS
- not provided
- Scenario
- Generating accurate shipping or delivery labels where each customer order consists of a different number of containers or packages.
- Observed behavior
- Standard mail merge maps one row of data to one label, meaning orders with multiple quantities must be dynamically expanded into multiple rows before merging.
Ensure your original data source is clean and formatted clearly, and make a backup copy of your database or spreadsheet before running any macros to transform the data.
Normalize the Data Source Using a VBA Macro
To generate multiple labels for a single order, you must first use a VBA macro to expand your dataset so that each individual container has its own row in the data source.
Word's mail merge feature maps a single row of data from Excel to a single label. When an order involves multiple containers (for example, 3 boxes for one order), you must expand the original row into three separate rows in a normalized table.
To automate this process, you can write an Excel VBA macro that reads the quantity column of each order, calculates the required number of rows, and populates a new worksheet with duplicated and sequentially numbered data before you perform the mail merge in Word.
Open your source data in Excel. Ensure you have clear columns for Customer Name, Item Name, Container Type, and Quantity.
Press Alt + F11 to open the VBA Editor, go to Insert > Module, and paste or write your custom VBA script designed to loop through the orders.
Execute the macro so that it copies the order details into a new worksheet, duplicating the row for as many containers as specified in the quantity column.
Ensure your macro adds sequential numbering to the new rows (e.g., Container 1 of 3, Container 2 of 3) so each label indicates the specific package number.
Save the new expanded worksheet. Open your label document in Word, go to the Mailings tab, click Select Recipients, and choose Use an Existing List to link to your newly generated worksheet.
Create Container Labels Easily with WPS Office
WPS Office offers a fully featured Spreadsheet and Writer suite. You can easily manage your data, run advanced macros to expand variable quantities, and seamlessly connect to the Mail Merge wizard in WPS Writer to generate professional shipping labels.
- 1. Prepare your data in WPS Spreadsheet: Open your initial order dataset in WPS Spreadsheet and use your data expansion script or manual duplication to create one row per container.
- 2. Save the expanded data list: Save the newly normalized worksheet that contains the individual container rows.
- 3. Open a label template in WPS Writer: Launch WPS Writer and open a blank document or a pre-formatted label template.
- 4. Link the data source: Navigate to the References tab, click Mail Merge, choose Open Data Source, and select your saved WPS Spreadsheet file.
- 5. Insert fields and merge: Insert your desired merge fields (Customer, Item, Container Number) onto the label template and click Merge to New Document to generate the final labels.
Frequently Asked Questions
Why does Mail Merge only print one label per order instead of multiple containers?
Mail merge is designed to generate exactly one output (like a label) per row in the linked data source. If an order with a quantity of five is written in a single row, the system will only produce one label. The data must be split into five separate rows beforehand.
Can I achieve variable quantity labels without using VBA macros?
Yes, but it requires manual effort. You would need to manually copy and paste the order row in your spreadsheet for each container required before running the mail merge. For large datasets, using a macro is highly recommended to save time and prevent errors.
What fields should I include in my normalized container table?
Your expanded source table should include all the information you want printed on the label. Typically, this includes Customer Name, Shipping Address, Item Name, Container Type, Current Container Number (e.g., 1, 2, 3), and Total Containers (e.g., 3).
