Apply highlighting (or a style) selectively in a mail merge field

[John in Saratoga]

I am mailmerging into a word document a text field such as this:

Due: $100 on 11/5/2006; $120 on 12/6/2006; $80 on 1/5/2007

If one of the dates meets a certain criteria, e.g., if it is within the
current month, I want to highlight that date and its associated amount
yellow.

Any ideas how to highlight or apply a style to only part of a mailmerge
field's content based on that content?

Any suggestions appreciated.

 

[macropod]

Hi John,

You could perform an IF test on your mergefields to achieve the desired
result. For example:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#}
on {MERGEFIELD MyDate \@ MM/dd/yyyy}"}
where the first set of mergefields is formatted with the yellow colour and
'MyDate' and 'MyAmount' are the names of your mergefields.
If you want to control the colouring of the date and amount results
independently of each other, you could use:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyDate \@
MM/dd/yyyy} {MERGEFIELD MyDate \@ MM/dd/yyyy}}
for the date; and
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyAmount
\# $,#} {MERGEFIELD MyAmount \# $,#}}
for the amount.

The field coding works by comparing today's date in ISO format against the
merged date, also in ISO format. The results of this comparison determines
whether the output should be displayed in yellow.

You could extend this to cater for amounts that are overdue, like:
{IF{DATE \@ yyyyMMdd}> {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" {IF{DATE \@ yyyyMMdd}=
{MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD
MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD MyDate
\@ MM/dd/yyyy}"}}
where the first set of mergefields is formatted with say, red, and the
second set of mergefields is formatted yellow.

Cheers
PS: the field braces (i.e. '{ }') are created in pairs via Ctrl-F9 - you
can't use normal braces for this.

 

[John in Saratoga]

Thanks macropad, good idea, but as is it won't work for me here. My
mergefield is one text string, not a series of separate mergefields. By the
time the field gets to my Word document, the original database fields have
been put together into one string.

The only thing I can be sure of is that the substring I want to highlight
will appear only once in the whole string. I can't be sure where it will be,
how long it will be, or how long the whole string will be.

John

Hi John,

You could perform an IF test on your mergefields to achieve the desired
result. For example:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#}
on {MERGEFIELD MyDate \@ MM/dd/yyyy}"}
where the first set of mergefields is formatted with the yellow colour and
'MyDate' and 'MyAmount' are the names of your mergefields.
If you want to control the colouring of the date and amount results
independently of each other, you could use:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyDate \@
MM/dd/yyyy} {MERGEFIELD MyDate \@ MM/dd/yyyy}}
for the date; and
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyAmount
\# $,#} {MERGEFIELD MyAmount \# $,#}}
for the amount.

The field coding works by comparing today's date in ISO format against the
merged date, also in ISO format. The results of this comparison determines
whether the output should be displayed in yellow.

You could extend this to cater for amounts that are overdue, like:
{IF{DATE \@ yyyyMMdd}> {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" {IF{DATE \@ yyyyMMdd}=
{MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD
MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD MyDate
\@ MM/dd/yyyy}"}}
where the first set of mergefields is formatted with say, red, and the
second set of mergefields is formatted yellow.

Cheers
PS: the field braces (i.e. '{ }') are created in pairs via Ctrl-F9 - you
can't use normal braces for this.

--
macropod
[MVP - Microsoft Word]

I am mailmerging into a word document a text field such as this:

Due: $100 on 11/5/2006; $120 on 12/6/2006; $80 on 1/5/2007

If one of the dates meets a certain criteria, e.g., if it is within the
current month, I want to highlight that date and its associated amount
yellow.

Any ideas how to highlight or apply a style to only part of a mailmerge
field's content based on that content?

Any suggestions appreciated.

 

[macropod]

Hi John,

In that case, you'll need to run a macro over the merged document to search
out the date strings, plus the preceding text up to and including the '$'
symbols. Might be a problem if the same date appears anywhere else in the
body of the document, though.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks macropad, good idea, but as is it won't work for me here. My
mergefield is one text string, not a series of separate mergefields. By the
time the field gets to my Word document, the original database fields have
been put together into one string.

The only thing I can be sure of is that the substring I want to highlight
will appear only once in the whole string. I can't be sure where it will be,
how long it will be, or how long the whole string will be.

John

Hi John,

You could perform an IF test on your mergefields to achieve the desired
result. For example:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#}
on {MERGEFIELD MyDate \@ MM/dd/yyyy}"}
where the first set of mergefields is formatted with the yellow colour and
'MyDate' and 'MyAmount' are the names of your mergefields.
If you want to control the colouring of the date and amount results
independently of each other, you could use:
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyDate \@
MM/dd/yyyy} {MERGEFIELD MyDate \@ MM/dd/yyyy}}
for the date; and
{IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD MyAmount
\# $,#} {MERGEFIELD MyAmount \# $,#}}
for the amount.

The field coding works by comparing today's date in ISO format against the
merged date, also in ISO format. The results of this comparison determines
whether the output should be displayed in yellow.

You could extend this to cater for amounts that are overdue, like:
{IF{DATE \@ yyyyMMdd}> {MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount
\# $,#} on {MERGEFIELD MyDate \@ MM/dd/yyyy}" {IF{DATE \@ yyyyMMdd}=
{MERGEFIELD MyDate \@ yyyyMMdd} "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD
MyDate \@ MM/dd/yyyy}" "{MERGEFIELD MyAmount \# $,#} on {MERGEFIELD MyDate
\@ MM/dd/yyyy}"}}
where the first set of mergefields is formatted with say, red, and the
second set of mergefields is formatted yellow.

Cheers
PS: the field braces (i.e. '{ }') are created in pairs via Ctrl-F9 - you
can't use normal braces for this.

--
macropod
[MVP - Microsoft Word]

I am mailmerging into a word document a text field such as this:

Due: $100 on 11/5/2006; $120 on 12/6/2006; $80 on 1/5/2007

If one of the dates meets a certain criteria, e.g., if it is within the
current month, I want to highlight that date and its associated amount
yellow.

Any ideas how to highlight or apply a style to only part of a mailmerge
field's content based on that content?

Any suggestions appreciated.

 

[John in Saratoga]

Thanks macropod,

I think you're right that a macro could do it. I've never written a macro
that runs once for each merged document between the merging and the printing.
I'd have to research how to do that.

One approach that failed was to apply the formatting to the data source
file. I couldn't find a format with which Word would preserve
formatting/highlighting during the merge.

Another idea: In the source file, split the string into three substrings,
(1) before the search substring, (2) the search substring, (3) after the
search substring. Then mailmerge three fields instead of one, with the center
one appropriately formatted.

I don't yet know if this pre-merge processing of the source file would be
easier than writing the merge-time macro you suggest.

Thanks for your suggestions. I wish I had one that's as simple as your
original proposal. I'll need to do this frequently, and I'd rather not do
outside-of-Word processing.

John

Hi John,

In that case, you'll need to run a macro over the merged document to search
out the date strings, plus the preceding text up to and including the '$'
symbols. Might be a problem if the same date appears anywhere else in the
body of the document, though.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks macropad, good idea, but as is it won't work for me here. My
mergefield is one text string, not a series of separate mergefields. By the
time the field gets to my Word document, the original database fields have
been put together into one string.

The only thing I can be sure of is that the substring I want to highlight
will appear only once in the whole string. I can't be sure where it will be,
how long it will be, or how long the whole string will be.

John

 

[Peter Jamieson]

What is the "original" data source? and what is actually in the string? i.e.
is it

Due: $100 on 11/5/2006; $120 on 12/6/2006; $80 on 1/5/2007

$100 on 11/5/2006; $120 on 12/6/2006; $80 on 1/5/2007

or what? Are there always three values separated by ";" ? Are the dates
always M/D/YYYY/ etc.? Is one of them always highlighted, or is /at most/
one highlighted?

Personally I would consider
a. trying to "re-split" the data into three fields in an SQL statement and
using the approach macropod first suggested, or
b. using mail merge events to process the data record-by-record

Peter Jamieson

Thanks macropod,

I think you're right that a macro could do it. I've never written a macro
that runs once for each merged document between the merging and the
printing.
I'd have to research how to do that.

One approach that failed was to apply the formatting to the data source
file. I couldn't find a format with which Word would preserve
formatting/highlighting during the merge.

Another idea: In the source file, split the string into three substrings,
(1) before the search substring, (2) the search substring, (3) after the
search substring. Then mailmerge three fields instead of one, with the
center
one appropriately formatted.

I don't yet know if this pre-merge processing of the source file would be
easier than writing the merge-time macro you suggest.

Thanks for your suggestions. I wish I had one that's as simple as your
original proposal. I'll need to do this frequently, and I'd rather not do
outside-of-Word processing.

John

Hi John,

In that case, you'll need to run a macro over the merged document to
search
out the date strings, plus the preceding text up to and including the '$'
symbols. Might be a problem if the same date appears anywhere else in the
body of the document, though.

Cheers

--
macropod
[MVP - Microsoft Word]

Thanks macropad, good idea, but as is it won't work for me here. My
mergefield is one text string, not a series of separate mergefields. By the
time the field gets to my Word document, the original database fields
have
been put together into one string.

The only thing I can be sure of is that the substring I want to
highlight
will appear only once in the whole string. I can't be sure where it
will be,
how long it will be, or how long the whole string will be.

John

 

[John in Saratoga]

Thanks Peter.

Right now the source is a string with "Due" already in it and always with
three values. I think I will experiment with both approaches -- parsing the
string and splitting it into its components (using SQL or otherwise), and
also using mail merge events.

Thanks for the suggestions. I'm sure one of them can work out.

John