Mail merge

[Kathleen]

Hi,

I have an Excel file with many columns (up to EA so far). It includes an
amount due per month for each person. Using January - December, some may
have only amounts due beginning in June. With the merge into Word, there is
a customized letter with their relating coupons. Is there a way to have only
the months due rather than having some blank months?

Kathleen

 

[macropod]

Hi Kathleen,

How are the merge data laid out in the Word document? Are you trying to supress the month names for which there are no data, or just
the space associated with the empty records?

 

[Kathleen]

Hi Macropod,

The merge involves a 2 page letter and pages for coupons. All 3+ pages have
merge fields in them. John Smith owes money for Jan - June. Dave Smyth owes
money only for Feb. If its possible, John's packet would have coupons for
Jan - June and Dave's would have only one coupon for Feb. The coupon sheet
holds 3 coupons which would then give John two pages of coupons.

Kathleen

Hi Kathleen,

How are the merge data laid out in the Word document? Are you trying to supress the month names for which there are no data, or just
the space associated with the empty records?

--
Cheers
macropod
[Microsoft MVP - Word]

Hi,

I have an Excel file with many columns (up to EA so far). It includes an
amount due per month for each person. Using January - December, some may
have only amounts due beginning in June. With the merge into Word, there is
a customized letter with their relating coupons. Is there a way to have only
the months due rather than having some blank months?

Kathleen

 

[macropod]

Hi Kathleen,

The answer depends on how the source data are laid out. The two most likely possibilities are:

Given_Name, Family Name, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
John, Smith, 10, 10, 10, 10, 10, 10, 0, 0, 0, 0, 0, 0
Dave, Smyth, 0, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
(excuse the spacing)
or
Given_Name, Family Name, Month, Amount
John, Smith, Jan, 10
John, Smith, Feb, 10
John, Smith, Mar, 10
John, Smith, Apr, 10
John, Smith, May, 10
John, Smith, Jun, 10
Dave, Smyth, Feb, 10

For the first scenario, which seems to match the data layout you've described, you would use an IF field (inserted via 'Insert Word
field' on the mailmerge toolbar) for each month, coded along the lines of:
{IF«Jan»<> "" "Jan: «Jan»¶
"}{IF«Feb»<> "" "Feb: «Feb»¶
"}{IF«Mar»<> "" "Mar: «Mar»¶
"}{IF«Apr»<> "" "Apr: «Apr»¶
"}
etc, where the '¶' is a paragraph mark or line-feed, depending on which you're using. If you're not printing the month names next to
each amount, you can omit the 'Jan: ', 'Feb: ', etc portions of the above field codes.

Note that the fields bounded by the chevrons are part of the mergefields inserted via 'Insert Merge field' on the mailmerge
toolbar - you can't type them in or copy & paste them from this message. Note too that each field starts on the same line as its
predecessor concludes on.

For the second scenario, you can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word
version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod,

The merge involves a 2 page letter and pages for coupons. All 3+ pages have
merge fields in them. John Smith owes money for Jan - June. Dave Smyth owes
money only for Feb. If its possible, John's packet would have coupons for
Jan - June and Dave's would have only one coupon for Feb. The coupon sheet
holds 3 coupons which would then give John two pages of coupons.

Kathleen

Hi Kathleen,

How are the merge data laid out in the Word document? Are you trying to supress the month names for which there are no data, or
just
the space associated with the empty records?

--
Cheers
macropod
[Microsoft MVP - Word]

Hi,

I have an Excel file with many columns (up to EA so far). It includes an
amount due per month for each person. Using January - December, some may
have only amounts due beginning in June. With the merge into Word, there is
a customized letter with their relating coupons. Is there a way to have only
the months due rather than having some blank months?

Kathleen

 

[Kathleen]

Hi Macropod,

Thank you! Maybe you might be able to help with this question as well. The
merge is done and saved - each record is five pages. Now they need to be
printed. The printer is set up to to pick tray one for the pages one and
two, tray two for page 2, and tray 3 for pages 4 and 5. The first record
prints and then it seems not to know that the next page is a new record. I
went back to the merge set up and insert the "next record" field and that
didn't help. Also tried to put in a page break or a section break which
didn't work either. The file holds 155 records with each being 5 pages.
There was one post that set up a macro but I can't figure out how to adapt it
to my file or if it will even work. Any idea how to make this work?

Kathleen

Hi Kathleen,

The answer depends on how the source data are laid out. The two most likely possibilities are:

Given_Name, Family Name, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
John, Smith, 10, 10, 10, 10, 10, 10, 0, 0, 0, 0, 0, 0
Dave, Smyth, 0, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
(excuse the spacing)
or
Given_Name, Family Name, Month, Amount
John, Smith, Jan, 10
John, Smith, Feb, 10
John, Smith, Mar, 10
John, Smith, Apr, 10
John, Smith, May, 10
John, Smith, Jun, 10
Dave, Smyth, Feb, 10

For the first scenario, which seems to match the data layout you've described, you would use an IF field (inserted via 'Insert Word
field' on the mailmerge toolbar) for each month, coded along the lines of:
{IF«Jan»<> "" "Jan: «Jan»¶
"}{IF«Feb»<> "" "Feb: «Feb»¶
"}{IF«Mar»<> "" "Mar: «Mar»¶
"}{IF«Apr»<> "" "Apr: «Apr»¶
"}
etc, where the '¶' is a paragraph mark or line-feed, depending on which you're using. If you're not printing the month names next to
each amount, you can omit the 'Jan: ', 'Feb: ', etc portions of the above field codes.

Note that the fields bounded by the chevrons are part of the mergefields inserted via 'Insert Merge field' on the mailmerge
toolbar - you can't type them in or copy & paste them from this message. Note too that each field starts on the same line as its
predecessor concludes on.

For the second scenario, you can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word
version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod,

The merge involves a 2 page letter and pages for coupons. All 3+ pages have
merge fields in them. John Smith owes money for Jan - June. Dave Smyth owes
money only for Feb. If its possible, John's packet would have coupons for
Jan - June and Dave's would have only one coupon for Feb. The coupon sheet
holds 3 coupons which would then give John two pages of coupons.

Kathleen

Hi Kathleen,

How are the merge data laid out in the Word document? Are you trying to supress the month names for which there are no data, or
just
the space associated with the empty records?

--
Cheers
macropod
[Microsoft MVP - Word]


Hi,

I have an Excel file with many columns (up to EA so far). It includes an
amount due per month for each person. Using January - December, some may
have only amounts due beginning in June. With the merge into Word, there is
a customized letter with their relating coupons. Is there a way to have only
the months due rather than having some blank months?

Kathleen

 

[macropod]

Hi Kathleen,

Assuming it's the same as in the PC environment (which is what I work with), I'd have thought you could control the print paramters
by setting up the paper source definitions in your mailmerge main document - before executing the merge. See under File|Page
Setup|Paper Source. If it doesn't carry over from the mailmerge main document to the merged output, try setting the parameters on
the merged output (with the 'selected section' option) before saving.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod,

Thank you! Maybe you might be able to help with this question as well. The
merge is done and saved - each record is five pages. Now they need to be
printed. The printer is set up to to pick tray one for the pages one and
two, tray two for page 2, and tray 3 for pages 4 and 5. The first record
prints and then it seems not to know that the next page is a new record. I
went back to the merge set up and insert the "next record" field and that
didn't help. Also tried to put in a page break or a section break which
didn't work either. The file holds 155 records with each being 5 pages.
There was one post that set up a macro but I can't figure out how to adapt it
to my file or if it will even work. Any idea how to make this work?

Kathleen

Hi Kathleen,

The answer depends on how the source data are laid out. The two most likely possibilities are:

Given_Name, Family Name, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
John, Smith, 10, 10, 10, 10, 10, 10, 0, 0, 0, 0, 0, 0
Dave, Smyth, 0, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
(excuse the spacing)
or
Given_Name, Family Name, Month, Amount
John, Smith, Jan, 10
John, Smith, Feb, 10
John, Smith, Mar, 10
John, Smith, Apr, 10
John, Smith, May, 10
John, Smith, Jun, 10
Dave, Smyth, Feb, 10

For the first scenario, which seems to match the data layout you've described, you would use an IF field (inserted via 'Insert
Word
field' on the mailmerge toolbar) for each month, coded along the lines of:
{IF«Jan»<> "" "Jan: «Jan»¶
"}{IF«Feb»<> "" "Feb: «Feb»¶
"}{IF«Mar»<> "" "Mar: «Mar»¶
"}{IF«Apr»<> "" "Apr: «Apr»¶
"}
etc, where the '¶' is a paragraph mark or line-feed, depending on which you're using. If you're not printing the month names next
to
each amount, you can omit the 'Jan: ', 'Feb: ', etc portions of the above field codes.

Note that the fields bounded by the chevrons are part of the mergefields inserted via 'Insert Merge field' on the mailmerge
toolbar - you can't type them in or copy & paste them from this message. Note too that each field starts on the same line as its
predecessor concludes on.

For the second scenario, you can use Word's Catalogue/Directory Mailmerge facility for this (the terminolgy depends on the Word
version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Macropod,

The merge involves a 2 page letter and pages for coupons. All 3+ pages have
merge fields in them. John Smith owes money for Jan - June. Dave Smyth owes
money only for Feb. If its possible, John's packet would have coupons for
Jan - June and Dave's would have only one coupon for Feb. The coupon sheet
holds 3 coupons which would then give John two pages of coupons.

Kathleen



:

Hi Kathleen,

How are the merge data laid out in the Word document? Are you trying to supress the month names for which there are no data,
or
just
the space associated with the empty records?

--
Cheers
macropod
[Microsoft MVP - Word]


Hi,

I have an Excel file with many columns (up to EA so far). It includes an
amount due per month for each person. Using January - December, some may
have only amounts due beginning in June. With the merge into Word, there is
a customized letter with their relating coupons. Is there a way to have only
the months due rather than having some blank months?

Kathleen