Mail Merge from Excel

Mail Merge from Excel - Skip If?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KevinM - 07 Jan 2004 09:07 GMT

I have been successfully using mail merge in a basic way for some time,
however I thought I would try and be a bit smarter and now I am stuck!

I am merging salary letters, however certain benefits to not apply to
all employees. Eg Car Allowance. So in Excel I have a spreadheet
showing
Salary Car Allowance
B Smith 10,000 3,000
S Jones 15,000 0

In Word I have a letter with the following merge fields eg

I am pleased to confirm your salary as <<salary>>
and your car allowance will remain as <<car allowance>>

What I would like to do is only add the second line relating to car
allowance if the allowance shown in Excel is greater than zero.

Can anyone help - I have tried the help pages and looked at the FAQs
but I cannot seem to find a simple answer - Thanks?

------------------------------------------------

Reply to this Message

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS - 08 Jan 2004 09:53 GMT

Hi Kevin,

Using Ctrl+F9 to insert each pair of field delimiters { }

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "."

and Alt+F9 to toggle them off.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

> I have been successfully using mail merge in a basic way for some time,
> however I thought I would try and be a bit smarter and now I am stuck!
[quoted text clipped - 18 lines]
>
> ------------------------------------------------

Reply to this Message

Cindy M -WordMVP- - 08 Jan 2004 10:49 GMT

Hi KevinM,

What you'll need is an IF field that suppresses the complete line if
the value for the field is zero. It would look something like this:

{ IF { Mergefield Car_Allowance } <> 0 "The text here, press ENTER¶
" "" }

Note that the paragraph mark ¶ is also part of the conditional in the
"quotes".

Use Ctrl+F9 to insert empty pair of field brackets for the IF. The
Mergefields you can insert in the usualy way. You'll need to use Alt+F9
to toggle the field codes on/off. (Inserting a merge field will
probably toggle them off, and you'll need to turn them back on to
finish.)

> I am merging salary letters, however certain benefits to not apply to
> all employees. Eg Car Allowance. So in Excel I have a spreadheet
[quoted text clipped - 10 lines]
> What I would like to do is only add the second line relating to car
> allowance if the allowance shown in Excel is greater than zero.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :-)

Reply to this Message

hkbs - 09 Jan 2004 18:47 GMT

I would be really interested to hear if the solutions given worked for
KevinM; I could not get any to work! I typed the instructions exactly as
stated and just replaced the brackets with Ctrl + F9

> Hi KevinM,
>
[quoted text clipped - 35 lines]
> This reply is posted in the Newsgroup; please post any follow question
> or reply in the newsgroup and not by e-mail :-)

Reply to this Message

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS - 10 Jan 2004 03:12 GMT

Hi hkbs,

There was a missing closing } in my field construction

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "."

should have been

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "}."

It does rely on there being a 0 in the CarAllowance field of the data source
which I assume would be the case with the data source being Excel.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

>I would be really interested to hear if the solutions given worked for
> KevinM; I could not get any to work! I typed the instructions exactly as
[quoted text clipped - 39 lines]
>> This reply is posted in the Newsgroup; please post any follow question
>> or reply in the newsgroup and not by e-mail :-)

Reply to this Message