Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / November 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
John in Saratoga - 14 Nov 2006 17:31 GMT
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 - 14 Nov 2006 21:44 GMT
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.

Signature

macropod
[MVP - Microsoft Word]

> I am mailmerging into a word document a text field such as this:
>
[quoted text clipped - 8 lines]
>
> Any suggestions appreciated.
John in Saratoga - 15 Nov 2006 00:14 GMT
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,
>
[quoted text clipped - 43 lines]
> >
> > Any suggestions appreciated.
macropod - 15 Nov 2006 02:47 GMT
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

Signature

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
[quoted text clipped - 22 lines]
> > for the date; and
> > {IF{DATE \@ yyyyMMdd}= {MERGEFIELD MyDate \@ yyyyMMdd} {MERGEFIELD
MyAmount
> > \# $,#} {MERGEFIELD MyAmount \# $,#}}
> > for the amount.
[quoted text clipped - 28 lines]
> > >
> > > Any suggestions appreciated.
John in Saratoga - 15 Nov 2006 04:29 GMT
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,
>
[quoted text clipped - 17 lines]
> >
> > John
Peter Jamieson - 15 Nov 2006 14:01 GMT
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,
>
[quoted text clipped - 47 lines]
>> >
>> > John
John in Saratoga - 15 Nov 2006 15:53 GMT
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

> What is the "original" data source? and what is actually in the string? i.e.
> is it
[quoted text clipped - 13 lines]
>
> Peter Jamieson
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.