mail merge problem

[gwh]

Hi everyone

I have an excel database that I'm using in an envelope mail merge in
Word. Some of the entries don't have titles and others do and when I do
the merge there seems to be a space before the first name in the
entries that don't have titles (ie. (space)John Smith). There's no
space though in the entries that do have titles (which is correct). I
know that if I leave out the space between the title and firstname
fields it fixes the problem when there's no title, but when there is a
title the needed space is not there so the title and firstname merge
into one word.

Can anyone tell me if there's a workaround for this problem?

Any help appreciated.

 

[John McGhie [MVP - Word and Word Macintosh]]

Yes. You need an IF field to detect the presence of blank in the Title
field and suppress the space if true.

Details here:
http://word.mvps.org/faqs/mailmerge/MMergeIfFields.htm

Cheers

Hi everyone

I have an excel database that I'm using in an envelope mail merge in
Word. Some of the entries don't have titles and others do and when I do
the merge there seems to be a space before the first name in the
entries that don't have titles (ie. (space)John Smith). There's no
space though in the entries that do have titles (which is correct). I
know that if I leave out the space between the title and firstname
fields it fixes the problem when there's no title, but when there is a
title the needed space is not there so the title and firstname merge
into one word.

Can anyone tell me if there's a workaround for this problem?

Any help appreciated.

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Business Analyst, Consultant
Technical Writer.
Sydney, Australia +61 (0) 4 1209 1410

 

[Robert R. Rahl]

The link to the mailmerge page is enormously helpful, John. At the end
of Section 2 (Suppressing unwanted spaces and commas within a line of an
address) there is a reference to the "Suppress Blank Lines in Addresses"
field in the Mail Merge dialog. This would resolve the only remaining
problem I have but I don't see any such thing in the Mail Merge Manager
or anywhere else. I am hoping against hope that this isn't something
unique to Word for Windows. I'm using Word 2004 11.3 on Mac OS 10.3.9.
Please advise.

RRR

 

[Elliott Roper]

The link to the mailmerge page is enormously helpful, John. At the end
of Section 2 (Suppressing unwanted spaces and commas within a line of an
address) there is a reference to the "Suppress Blank Lines in Addresses"
field in the Mail Merge dialog. This would resolve the only remaining
problem I have but I don't see any such thing in the Mail Merge Manager
or anywhere else. I am hoping against hope that this isn't something
unique to Word for Windows. I'm using Word 2004 11.3 on Mac OS 10.3.9.
Please advise.

I never knew there was such a thing.
You *can* suppress missing address lines with creative use of IF in
your source doc.
Here's mine (sort of)
{ IF { MERGEFIELD surname } <> "" "{MERGEFIELD title }{
MERGEFIELD initials } {MERGEFIELD surname }
" ""}{IF {MERGEFIELD position} <> "" " {MERGEFIELD position}
" ""}{ IF { MERGEFIELD a1 } <> "" "{MERGEFIELD a1}
" ""}{ IF { MERGEFIELD a2 } <> "" "{MERGEFIELD a2}
" ""}{ IF { MERGEFIELD a3 } <> "" "{MERGEFIELD a3}
" ""}{ IF { MERGEFIELD a4 } <> "" "{MERGEFIELD a4}
" ""}{ IF { MERGEFIELD postcode } <> "" "{MERGEFIELD postcode
}" ""}

The trick is to place the paragraph marks inside the IFs so that Word
won't be tempted to insert one where it is not needed.

Note this was hand copied, so you might have to wrestle with it a bit
more than you ought if I made a typo.

Note also that it does not behave well if there is no surname (It adds
a space)

 

[Robert R. Rahl]

I never knew there was such a thing.
You *can* suppress missing address lines with creative use of IF in
your source doc.
Here's mine (sort of)
{ IF { MERGEFIELD surname } <> "" "{MERGEFIELD title }{
MERGEFIELD initials } {MERGEFIELD surname }
" ""}{IF {MERGEFIELD position} <> "" " {MERGEFIELD position}
" ""}{ IF { MERGEFIELD a1 } <> "" "{MERGEFIELD a1}
" ""}{ IF { MERGEFIELD a2 } <> "" "{MERGEFIELD a2}
" ""}{ IF { MERGEFIELD a3 } <> "" "{MERGEFIELD a3}
" ""}{ IF { MERGEFIELD a4 } <> "" "{MERGEFIELD a4}
" ""}{ IF { MERGEFIELD postcode } <> "" "{MERGEFIELD postcode
}" ""}

The trick is to place the paragraph marks inside the IFs so that Word
won't be tempted to insert one where it is not needed.

Note this was hand copied, so you might have to wrestle with it a bit
more than you ought if I made a typo.

Note also that it does not behave well if there is no surname (It adds
a space)

Thanks, Elliott. I started experimenting with that approach (embedded
paragraph marks) but I thought that before I invested too much time I'd
first better find out if there was a magic "Suppress Blank Lines in
Addresses" function as indicated on the Word MVP site. Your code saves
me from having to hack it out from scratch. RRR

 

[Peter Jamieson]

"Suppress Blank Lines in Addresses" is enabled by default in Word 2004 mail
merge documents, just as it is on Windows Word.

I don't see an obvious place in the Data Merge Manager to either disable it
or enable it. As it happens, there is no obvious place in the current
Windows Word dialog boxes to enable/disable this feature either - you have
to restore the old dialog boxes to do it.

However, you can enable/disable using VBA, using e.g.

Sub enableSuppressBlankLines()
ActiveDocument.MailMerge.SuppressBlankLines = True
End Sub

to enable it and

Sub disableSuppressBlankLines()
ActiveDocument.MailMerge.SuppressBlankLines = False
End Sub

to disable it. This feature also seems to be exposed in Word's Applescript
dictionary but I haven't tested it there.

All that said, to use blank line suppression successfully, you have to know
which "blank lines" Word actually suppresses (and if you really want to
delve deep, what Word thinks a "line" is). Roughly speaking, for a paragraph
to be suppressed
a. it has to contain at least one "standalone MERGEFIELD"
b. every MERGEFIELD field has to evaluate to an empty result (i.e. they
cannot even evaluate to a space character, as it would if the data comes
from an Excel cell that contains a space). However, other fields such as IF
fields can evaluate to spaces.
c. the resulting paragraph can only contain certain types of white space,
e.g. spaces and tabs, but not hard spaces (ctrl-shift-spacebar), which will
prevent suppression.

I certainly don't know all of the rules, but for example

<paragraphmark>

won't be suppressed.

<tab><paragraphmark>

won't be suppressed.

{ MERGEFIELD abc }<paragraphmark>

will be suppressed if { MERGEFIELD abc } is empty, but not if it evaluates
to <space>

{ IF "{MERGEFIELD abc }" = "" "" "{MERGEFIELD abc }" }<paragraphmark>

will not be suppressed even when { MERGEFIELD abc } is empty

{ IF "{MERGEFIELD abc }" = "" "" "{MERGEFIELD abc }" }{ MERGEFIELD
abc }<paragraphmark>

will be suppressed if { MERGEFIELD abc } is empty, but not if it evaluates
to <space>

and so on...

Also, in Word 2004 and some recent versions of Windows Word, blank lines are
never suppressed if they are generated as a result of another field such as
IF or INCLUDETEXT.

Because you get complete control using the kind of IF field construction
John McGhie and others pointed you to, there's a case for switching off
blank suppression unless you know it does exactly what you need.

Peter Jamieson

 

[CyberTaz]

In addition to the excellent info from Peter & Elliott (sounds like a
singing group) - especially re including the para mark inside the IF field -
here's another little tidbit that may be of use.

If the Word field (IF, ASK, etc.) is the only content in the para, select &
format the para mark (the mark only) as Hidden. Be sure to REMOVE the Hidden
Text check in Print Prefs. If the field has content the para will print but
if the field is empty the para is treated as hidden.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Robert R. Rahl]

"Suppress Blank Lines in Addresses" is enabled by default in Word 2004 mail
merge documents, just as it is on Windows Word.

I don't see an obvious place in the Data Merge Manager to either disable it
or enable it. As it happens, there is no obvious place in the current
Windows Word dialog boxes to enable/disable this feature either - you have
to restore the old dialog boxes to do it.

However, you can enable/disable using VBA, using e.g.

Sub enableSuppressBlankLines()
ActiveDocument.MailMerge.SuppressBlankLines = True
End Sub

to enable it and

Sub disableSuppressBlankLines()
ActiveDocument.MailMerge.SuppressBlankLines = False
End Sub

to disable it. This feature also seems to be exposed in Word's Applescript
dictionary but I haven't tested it there.

All that said, to use blank line suppression successfully, you have to know
which "blank lines" Word actually suppresses (and if you really want to
delve deep, what Word thinks a "line" is). Roughly speaking, for a paragraph
to be suppressed
a. it has to contain at least one "standalone MERGEFIELD"
b. every MERGEFIELD field has to evaluate to an empty result (i.e. they
cannot even evaluate to a space character, as it would if the data comes
from an Excel cell that contains a space). However, other fields such as IF
fields can evaluate to spaces.
c. the resulting paragraph can only contain certain types of white space,
e.g. spaces and tabs, but not hard spaces (ctrl-shift-spacebar), which will
prevent suppression.

I certainly don't know all of the rules, but for example

<paragraphmark>

won't be suppressed.

<tab><paragraphmark>

won't be suppressed.

{ MERGEFIELD abc }<paragraphmark>

will be suppressed if { MERGEFIELD abc } is empty, but not if it evaluates
to <space>

{ IF "{MERGEFIELD abc }" = "" "" "{MERGEFIELD abc }" }<paragraphmark>

will not be suppressed even when { MERGEFIELD abc } is empty

{ IF "{MERGEFIELD abc }" = "" "" "{MERGEFIELD abc }" }{ MERGEFIELD
abc }<paragraphmark>

will be suppressed if { MERGEFIELD abc } is empty, but not if it evaluates
to <space>

and so on...

Also, in Word 2004 and some recent versions of Windows Word, blank lines are
never suppressed if they are generated as a result of another field such as
IF or INCLUDETEXT.

Because you get complete control using the kind of IF field construction
John McGhie and others pointed you to, there's a case for switching off
blank suppression unless you know it does exactly what you need.

Peter Jamieson

Thanks for the lucid and very helpful information, Peter. I had realized
that obviously blank lines were being suppressed but I didn't know why
the results of some of my expressions were being interpreted as
non-blank. Now that I understand the rules I'm getting the desired results.

The various contributors to this thread have enabled me to abandon a
rather elaborate set of macros I used to have to run post-merge to clean
up a membership directory which I produce on a quarterly basis. My main
document now merges cleanly with the Excel data and produces
"camera-ready" copy.

 

[Elliott Roper]

The various contributors to this thread have enabled me to abandon a
rather elaborate set of macros I used to have to run post-merge to clean
up a membership directory which I produce on a quarterly basis. My main
document now merges cleanly with the Excel data and produces
"camera-ready" copy.

Thanks Robert. This has neatly illustrated why there are contributors
in the first place. We are all learning from each other.

This is far from the first time I have learned new levels of slyness
after posting what I originally thought was a cool recipe.

 

[Peter Jamieson]

Hi Elliott,

Now the really interesting question is "How did you find out about
that?"

Trial and error, basically. I've been dealing wth Word fields for 15
years and support in general for a lot longer than that - not a lot to show
for it except lots of snippets like this. But...
a. the "field language" is very poorly specified (well, it's not specified
at all, really), which is probably why its behaviour changes quite radically
each time MS rewrites the relevant portion of Word.
b. even the recent work to codify MS word XML document formats contains
numerous errors of fact and shortcomings when it comes to the "field
language." I've sent off numerous messages to MS in the past, but there you
go.

Since you're obviously dealing with Word fields and mailmerge issues quite a
bit, if you haven't done so already, it's probably worth having a trawl
through the microsoft.public.word.mailmerge.fields newsgroup. It's very
Windows Word oriented (as am I, if you like) but quite a lot of the info.
there is relevant to Mac as well as PC. The main differences are that
a. Word 2004 is more like Windows Word 2000 in most respects as far as
mailmerge is concerned
b. connectivity on Mac is, in practice, significantly different (no ODBC in
Word 2004, no OLEDB at all, no DDE, no Access, but there is provision for
FileMaker, no Outlook or MAPI, but there is Entourage)
c. The UI is significantly different (DMM v. "Mail Merge Helper" and/or
"Mail Merge Wizard"
d. Windows Word has "field mapping" from "Address fields" to "Database
fields". Not sure Mac has that stuff.
e. No MailMerge events.

As a side note. It would seem from what you wrote it would be harmless
to leave the "Suppress Blank Lines in Addresses" enabled if you were
doing suppression explicitly with the para endings in the IFs. You
would never give the auto suppressor a chance to do anything. Except
perhaps bewilder you while debugging.

I suppose this comes down to a choice between a "magic" approach that works
some of the time (perhaps even most of the time) in simple cases, but whose
results can be bewildering, and a more explicit approach where you spell out
using IF fields which lines (or paras ) you want to suppress. IMO if
you're supplying a template or some such that others are able to adapt, it's
probably better to head for the approach that avoids "magic". But in the
end, it's a matter of judgment.

.. and why "addresses". I guess *that* was meant in an illustrative
way. It has no way of knowing you are dealing with an address surely?

I think it's in Mac Word's favour that it still calls MailMerge "Data
Merge", and have argued - almost certainly unsuccessfully - that the Windows
version ought to adopt that approach. However, although I don't have any
stats. of my own, I suspect that the vast majority of MailMerge/DataMerge
users are in fact doing small-scale merges that are merges to snailmail
(i.e. to printed documents) and e-mail, at least on Windows, probably using
data stored in an address book of some kind (eiter platform).

The Windows version of Word does have a mapping "layer" that the Mac Word
doesn't, apparently primarily to support its ADDRESSBLOCK and GREETINGLINE
fields. So for example, the Windows version of Word looks for a number of
different field names that look like "FirstName" and tries to "map" the
field name to an internal name that it refers to as an "address" field name.
Unfortuantely, the implementation isn't as complete as it should be, which
makes the whole thing rather less useful than it could be, but referring
back to your original question
a. in theory, Word might decide that it is dealing with an "address" and do
somthing differently as a result
b. in practice, I don't think it does in the "line suppression" department.

 

[Elliott Roper]

Hi Elliott,


Trial and error, basically.

it's probably worth having a trawl through the
microsoft.public.word.mailmerge.fields newsgroup.

Thanks. I will.

 

[Peter Jamieson]

Trial and error, basically.

<snip>
Why am I not amazed?

You ask a question. You get an answer. Why on earth would you be amazed?

 

[gwh]

Thanks for the info - works great!

Yes. You need an IF field to detect the presence of blank in the Title
field and suppress the space if true.

Details here:
http://word.mvps.org/faqs/mailmerge/MMergeIfFields.htm

Cheers




--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Business Analyst, Consultant
Technical Writer.
Sydney, Australia +61 (0) 4 1209 1410

 

[Peter Jamieson]

Yeah Guess I need an extended break from these groups.

Thinking again - in the cold light of day - about what's in the
microsoft.public.word.mailmerge.fields group, there probably isn't really a
whole lot there to interest a Mac Word user. There are a very large number
of responses along the lines of "to make that work, go back to the way Word
2000 used to do it" which are completely irrelevant to Mac, as are the vast
majority of items concerning connectivity.

Peter Jamieson