How to Use Mail Merge with conditions to show some features

raiya · #1 10-14-2012, 01:12 AM

Hi,
I have an excel sheet that contains records of students names, classes & their chosen subjects out of the subjects taught in the school. So, some subjects are taken by all the students and other differ for each student.
I need to output a report for each student that contains:
1- Student's name & class
2- Subjects chosen by this particular student. No other subjects
3- In addition, I need to show in the report a table for each subject. The tables will be filled with marks manually.

I used the mail merge in Ms Word to output a report for each student containing only part 1. I need a help to complete the report with the other two parts.

macropod · #2 10-15-2012, 01:33 AM

You will need to do a fair bit of work before your document can be used for the mailmerge. For starters, the tables should not have 'around' wrapping, as they need to be embedded in IF tests that evaluate whether the corresponding fields in your workbook have data, so that the tables can be conditionally displayed. I suggest you consider a two-column page layout or, if you don't want the page arrangement to change when a given table isn't present, a two-column table layout with the mergefields and subject tables in their owwn cells within it.

It's not particularly difficult to do, but your document has tables in it that don't exist in the data source and vice-versa, so I don't know what is supposed to happen in each case.

macropod · #3 10-15-2012, 05:20 PM

Cross-posted at: http://windowssecrets.com/forums/sho...-some-features
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

raiya · #4 10-15-2012, 08:56 PM

Hi macropod,
First of all, thank u for ur reply and help

Second, thank u for the notice about "cross posting" and sorry for inconvenience. Actually I posted my problem into 2 forums with almost a day in between since I'm new in the forum and with no experience in using codes with mail merge in Ms Word. I did use simple mail merge before, but all the stuff with coding not yet dealing with.

raiya · #5 10-15-2012, 11:13 PM

Quote:

Originally Posted by macropod

You will need to do a fair bit of work before your document can be used for the mailmerge. For starters, the tables should not have 'around' wrapping, as they need to be embedded in IF tests that evaluate whether the corresponding fields in your workbook have data, so that the tables can be conditionally displayed. I suggest you consider a two-column page layout or, if you don't want the page arrangement to change when a given table isn't present, a two-column table layout with the mergefields and subject tables in their owwn cells within it.

It's not particularly difficult to do, but your document has tables in it that don't exist in the data source and vice-versa, so I don't know what is supposed to happen in each case.

Hi macropod,
The tables I was using didn't have 'around' wrapping.
I've changed the layout as you suggested in 'a two-column table layout with the mergefields and subject tables in their own cells within it'
please chek the new document "FollowUp2"
Please, can u tell me what to do next?

macropod · #6 10-16-2012, 12:01 AM

See attached demo.

The document has a main table containing what appear to be some of your static tables, plus others embedded in IF fields that test the mergefields to detemine whether these option tables should appear/not appear.

macropod · #7 10-16-2012, 12:30 AM

Quote:

Originally Posted by raiya

The tables I was using didn't have 'around' wrapping.

All of the following tables in your first attachment do have 'Around' wrapping:
Social Studies, Biology, Geography, Chemistry, Science, History, English elective, Physics

Guess which tables in your second document have 'Around' wrapping ...

raiya · #8 10-16-2012, 12:49 AM

Sorry

, it looks like I didn't see it well, some of the tables were 'Around' wrapping

raiya · #9 10-16-2012, 12:59 AM

Wow it looks soo good!, Thanks alot
but

where can I found more information or good tutorial on fields in mail merge? I tried the help in Ms Word, it wasn't enough.

macropod · #10 10-16-2012, 04:03 AM

You could do a web search for 'Word mailmerge tutorial'.

For the technique I used, see: http://word.mvps.org/FAQs/MailMerge/MMergeIfFields.htm
See also: http://support.microsoft.com/kb/214827

If you really want to explore what can be done with fields, check out:
1. my Microsoft Word Date Calculation Tutorial, at:
http://lounge.windowssecrets.com/ind...owtopic=249902
or
http://www.gmayor.com/downloads.htm#Third_party
2. my Microsoft Word Field Maths Tutorial, at:
http://windowssecrets.com/forums/sho...l=1#post320143
or
http://www.gmayor.com/downloads.htm#Third_party
3. my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...ngs-(2000-2010)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

raiya · #11 10-16-2012, 10:37 PM

Thanks a lot Macropod,
Can I ask one stupid quistion?, why do the column headers of my data source"std2.xlsx" do not show up when I try to 'Insert Merge Field' from 'Database Fields'? Only their reference shows up, how can show them?

macropod · #12 10-16-2012, 11:04 PM

You need to either delete the two empty rows at the top of your workbook (as I did with the sample in my post) or define a named range for the mailmerge data, which you can then reference when you connect to the data source.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge Fields Show Up As Track Changes When its Turned Off	zoysiaspp	Mail Merge	1	09-28-2012 08:23 PM
Mail Merge Duplication of address on merge	RICKY	Mail Merge	1	09-26-2012 03:14 PM
Conditional merge fields in mail merge	Aude	Mail Merge	1	01-06-2012 07:38 PM
From a beginner: Mail Merge using Windows Mail	mhalpern705	Mail Merge	3	12-16-2011 04:58 AM
Saving INDV mail merges During the mail merge	sedain121	Mail Merge	2	10-04-2011 07:52 PM