Generating Letters from Excel List

rockstar1989 · #1 10-13-2024, 05:23 AM

Hello,

I need help in creating a grouping mail-merge for generating letters from Excel list.

Please check the example sheet attached. I need to generate letters for each company in column C. The column C is the primary key. The number of rows for each company will vary.

The format of letters (word document) is attached as "Example Output" and the column references is also attached. From the example sheet, 3 letters will be generated and saved to a folder.

I don't want to use the add-in. I tried to setup the mail-merge using the instructions in this thread but I was not able to generate accurate results, may be I am doing something wrong https://www.msofficeforums.com/mail-...-tutorial.html

Could anyone please help setting up the mail-merge.

Thanks in advance for your support.

macropod · #2 10-13-2024, 01:19 PM

See the Microsoft Word Catalogue/Directory Mailmerge Tutorial 'Sticky' thread at the top of this forum:
https://www.msofficeforums.com/mail-...-tutorial.html
For most people, the DATABASE field approach is probably the simplest to implement.

Since there are no fields of any kind in your attached document, it's impossible to know what the problem with it might be.

rockstar1989 · #3 10-13-2024, 06:22 PM

Thank you for your kind response.

I am now able to use the DATABASE field approach successfully. Just stuck in switches to format the table. How can I format the table as per screenshot?

The screenshot shows both, the table I want and the table it is creating. Please help.

macropod · #4 10-13-2024, 07:59 PM

Assuming your second screenshot is what you want in terms of table formatting, you can get that result with the switches:
\l "36" \b "187" \h
and with the SELECT FROM statement written as:
SELECT [#] AS FER, `Year`, [InstrumentNo ] AS `'Instrument No'`, [DATE] AS`'Date'`, `Terms`, `CCY`, Format([Amount],'0') AS `Amount` FROM

As for the font used for the table, that is controlled by whatever font is applied in your mailmerge main document's Normal Style.

rockstar1989 · #5 10-13-2024, 08:11 PM

Thank you. I am almost there.

Just stuck with the table formatting. The first screenshot is what I want in terms of table formatting. Currently I am using \l "36" \b "49" \h which is giving me the result as second screenshot. How can I format it as per first screenshot?

Also how can I center-align all the values in the table?

Thank you again!

macropod · #6 10-13-2024, 09:04 PM

In that case, the SELECT FROM statement becomes:
SELECT [#] AS `FER`, `Year`, [InstrumentNo ] AS `Instrument No`, `Date`, `Terms`, `CCY`, Format([Amount],'#,0') AS `Amount` FROM
And, for the switches use:
\l "1" \b "16" \h

As for the centering & header row formatting, add the following macro in your mailmerge main document to drive the process:

Code:

Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
      .Rows.Alignment = wdAlignRowCenter
      .Rows(1).HeadingFormat = True
      .Rows(1).Range.Font.Bold = True
      .Rows(1).Range.Shading.BackgroundPatternColor = wdColorAqua
    End With
  Nexxt
End With
Set Tbl = Nothing
Application.ScreenUpdating = True
End Sub

Clicking on the 'Edit Individual Documents' button in Word will intercept the merge and run the macro automatically. The potential disadvantage of intercepting the merge this way is that you no longer get to choose which records to merge at that stage. However, you can still achieve the same outcome - and with greater control - via the 'Edit Recipient List' tools.

I haven't been able to match your exact shading using Word's presets. The full set of presets for blue shades of various kinds are:
wdColorAqua, wdColorBlue, wdColorBlueGray, wdColorLightBlue, wdColorLightTurquoise, wdColorPaleBlue, wdColorSkyBlue, wdColorTeal, wdColorTurquoise
Other than using one of those, you could use:

Code:

.Rows(1).Range.Shading.BackgroundPatternColorIndex =

with the numeric value or RGB values of the color you want.

Note: You will need to save your mailmerge main document in the .docm format.

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: https://wordmvp.com/Mac/InstallMacro.html

rockstar1989 · #7 10-14-2024, 04:06 AM

Thank you so much for your support. I have followed your instructions and added a macro as well. The table is generating fine.

The only problem I am facing is: the letters are generated multiple times. If there are 4 records of 1 company then it is generating 4 letters with all same information. How can I get rid of these duplicate letters?

Please check attached files and help! Thanks in advance.

macropod · #8 10-14-2024, 05:32 AM

You're getting multiple outputs per recipient because you haven't followed the instructions in the tutorial and as per the examples given in the links there. What you need is a separate Excel Worksheet that has fields for everything that is to appear once per letter and use that as the primary data source.

Your Excel data should also not span multiple lines with breaks - you should use a separate column for each line.

See the attached workbook, to which I've added a 'Debtors' sheet.

I've updated the mailmerge main document and the macro, which now also outputs the case #s on the letter. You will need to re-connect the document to the Excel workbook, selecting the 'Debtors' sheet as the data source.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Generating a vertical classification list	AP41-at-OfficeFORUM	Excel	11	01-27-2023 11:59 AM
Batch generating word template with CC from excel data	Caerleon	Excel Programming	1	11-15-2022 08:46 PM
Mail Merge - generating multiple letters	myspoonistoobig	Mail Merge	8	10-16-2015 12:03 AM
Generating Fresh Copies Of an Excel Document	callumwk	Excel	2	04-09-2012 06:13 AM
Letters change size in folders list	nsv	Outlook	1	08-19-2010 03:48 AM