Hi guys,

I'm a beginner with using mail merge.
I want to start mail merge data from Excel to MS word. I have to create or produce a letter for each record/ name. On top of that I need to create or insert a table format for each record/name. I have been searching for ages already and come across different tutorials from gmayorlinks,msoffice tutorials but couldn't fully grasp it. Hard for me to understand. Does anyone have a simple explanation or simple code i could follow? Also, anyone knows how to insert tables when doing mail merge but the rows are not fixed. It will depend on how many brother's the client has.

Need Advice please.
Much appreciated!

Example

in Excel format

first name Surname brother 1 age1 brother 2 age2 brother 3 age3
Jane Smith Sarah 18 Carlo 25 Christ in 28
Alex Curtis Cameron 37
Ben Arandt Tim 40 Simon 45

OUTPUT in Word

Client Name: Jane Smith

Brother's name | brother's Age
Sarah 18


carlo 25
Christin 28


* new page or new latter

Client Name: Alex Curtis

Brother's name | brother's Age
cameron 37

Your description suggests all you need is an ordinary letter merge, with a mergefield for each field in your data source, along the lines of:

Client Name: «Given_Name»«Family_Name»
Brother's name | Brother's Age
«Brother1» | «Age1»
«Brother2» | «Age2»
«Brother3» | «Age3»

where the words between the chevrons are your field names.

Note: You can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

Thanks for the reply. I actually wanted the records to be inserted in a table. And each CLIENT will have its own page or letter. With the above example the problem im encountering is that if some clients has only one brother and the others has many. Then there will be rows that are are empty. Which I don't want to be shown. Rows will depend on how many brothers the client has.


I was following some of your examples from other threads and have managed to kind of put the data inside the table but it's creating a space between each row.

I also did a brute force if else statement for every client. But it's not really ideal. Will only be ok for few records.




Thanks Paul

Hi paul,

I forgot to attached the screenshot for reference. Both codes resulted in a not quite right output that I want.

Any help will be greatly appreciated.

Thanks

Attached Images

	sample3.PNG (13.3 KB, 23 views)
	ifelse.PNG (14.6 KB, 22 views)

Instead of that approach, what you need is something along the lines of:

Client Name: «Given_Name»«Family_Name»
{IF«Brother1» <> "" "Brother's name | Brother's Age
|«Brother1» | «Age1»|"}
{IF«Brother2» <> "" "|«Brother2» | «Age2»|"}
{IF«Brother3» <> "" "|«Brother3» | «Age3»|"}

where:
{IF«Brother1» <> "" "Brother's name | Brother's Age
|«Brother1» | «Age1»|"}
represents both the heading row and the first data row, followed by separate 1-row tables for each of:
{IF«Brother2» <> "" "|«Brother2» | «Age2»|"}
and
{IF«Brother3» <> "" "|«Brother3» | «Age3»|"}
and so on, up to the maximum number of rows needed. This will leave an empty paragraph between each row, but formatting can be used to minimise its appearance.

The alternative is to use as many separate complete tables as you need, embedded in nested fields like:

Client Name: «Given_Name»«Family_Name»
{IF«Brother3» <> "" "Brother's name | Brother's Age
|«Brother1» | «Age1»|
|«Brother2» | «Age2»|
|«Brother3» | «Age3»|" {IF«Brother2» <> "" "Brother's name | Brother's Age
|«Brother1» | «Age1»|
|«Brother2» | «Age2»|" {IF«Brother1» <> "" "Brother's name | Brother's Age
|«Brother1» | «Age1»|"}}}
In this case, there are three embedded tables.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]