Mailmerge keep formatting from Excel database or...

[JB reluctantly from WP]

Hello,

I have an Excel database file, and am merging into a Word document. I am
familiar with Word's merge process. I am using Excel 2002 and Word 2002.

I have a field in the database containing a cell phone number (ex:
555-1212), and need to differentiate between personal and company-provided
cell phones in my final Word document. I need your help to tell me the
easiest way to do this.

Can I format the text in the Excel file (i.e., underline or bold)? Or
should I instead have a separate field in the database indicating the
different ones, and then create an IF THEN statement in my Word document? In
documents created in the past (not using merge), the best result is for the
text to be underlined (or text color change) given the limited amount of
space in my document.

Any help will be very appreciated!

Thank you,
~Julie

 

[Peter Jamieson]

Can I format the text in the Excel file (i.e., underline or bold)?

You might be able to do that if you then copied/pasted your Excel worksheet
into a Word table and used that as the datasource. Then, instead of

{ MERGEFIELD myfield } fields in your Word document, you would need to use

{ myfield } fields. So probably not the simplest approach.

Or
should I instead have a separate field in the database indicating the
different ones, and then create an IF THEN statement in my Word document?

That's probably your best bet, and the effort involved could be a good
investment for the future.

Otherwise, if all your company cell numbers start with a specific prefix
(say 555 !) and none of the non-company mobiles start with that prefix, you
might be able to use an IF field with a wildcard comparison, e.g.

{ IF "{ MERGEFIELD myphonenumber }" = "555*" "the text/formatting you want
for your company phones" "the text/formatting you want for non-company
phones" }

Peter Jamieson

 

[Julie]

Thanks Peter,

Unfortunately, the company-provided cell phones are not all the same prefix,
not anything common at all, in fact. I also do not want to bring the
information over to a Word table. For me, that defeats the whole purpose of
having the merge in the first place.

What if I create a separate field ("FIELDNAME") in the database that I just
mark with an "X" indicating company-provided? How would I label my
mergefield in Word to indicate the formatting change needed for anything
including an "X" in the "FIELDNAME" field? Can you give me the specific
language to use?

Thank you,
~Julie

 

[Peter Jamieson]

Julie,

Yes, that i probably your best bet. Unless I have misunderstood...

Add an Excel column that distinguishes between "company-provided" and "not
company-provided". Let's call it "Provided" and let's suppose you put a "Y"
in the colum if it's company-provided, and "N" otherwise (there are other
possibilities...)

The in your mailmerge main document, use a nested field such as

{ IF "{ MERGEFIELD Provided }" = "Y"
"{ MERGEFIELD myphonenumber \*Charformat }"
"{ MERGEFIELD myphonenumber \*Charformat }" }

Each pair of {} is a pair of "field code braces" that you can insert using
ctrl-F9. The ordinary keyboard characters {} won't work.

Use Alt-F9 to toggle between "field code display" when you should see
something such as the above, and "results display".

Clearly as I have described it so far, there's no difference between the
second line

"{ MERGEFIELD myphonenumber \*Charformat }"

and the third line. However, you should be able to apply the formatting you
need to the text between the "" quotes. If nothing else, using the
\*Charformat switch should apply the formatting you applied to the " M" at
the beginning of the field.

Sorry if I haven't described it well, but see how far you get...

Peter Jamieson

 

[Julie]

Peter,

Thanks for the info so far, but I'm at an impasse.

I created a column titled "CO" for the cell numbers that are company
provided. I noted the ones that are with a "Y." Should I also note the rest
with an "N," or is that not necessary?

Also, I don't quite understand your comment about the 2nd and 3rd lines of
Mergefield text.

With what I have so far, only the company provided cell numbers are even
printing, but they ARE underlined! Woohoo! Please tell me how to get the
rest of the cell numbers to print.

Here's what I have so far:
{IF "{MERGEFIELD "CO"} = "Y" "{MERGEFIELD "CELL"\*Charformat}"}
(I have underlined the "M".)
This way only prints the CELL numbers with a "Y" in the "CO" column.

What else am I missing?

Thanks so much for your help!

~Julie

 

[Julie]

Hi Peter,

Well, just after I typed the last reply, I made it work!

Apparently, my version of Word doesn't like to update with F9 like it
should, so I simply closed Word and reopened it, and voila!

So, I ended up with:
{IF "{MERGEFIELD "CO"} = "Y" {MERGEFIELD "CELL"\*Charformat}" "{MERGEFIELD
"CELL"}" }

I am appreciative of all help received via these discussion forums. I've
learned so much about Word and Excel from your helpful advice.

THANK YOU!
~Julie