Mail Merge & Find/Replace

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nschiffelbein@gmail.com - 08 May 2006 01:26 GMT

Hi,

I came accross the gmayor macro to split a mail merge file into
serparate files as the merge is occuring and this fixed a big problem I
was running into. What I'm trying to do now is perform a find/replace
on each document as it's created. I would prefer to just add some code
to the macro which gmayor wrote but me being a bit new at this I'm not
sure where to place the code within his. Any help would be greatly
appreciated. The replace is to change a set character into a line break
incase anyone is wondering. The code used in gmayor's macro is
available at http://www.gmayor.com/individual_merge_letters.htm near
the bottom of the page. Thanks in advance.

Reply to this Message

Graham Mayor - 08 May 2006 06:36 GMT

It might help if you explained what you are trying to replace. Wouldn't it
be simpler to create the merge document correctly in the first place?

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Hi,
>
[quoted text clipped - 9 lines]
> http://www.gmayor.com/individual_merge_letters.htm near the bottom of
> the page. Thanks in advance.

Reply to this Message

what4893 - 08 May 2006 16:30 GMT

Sorry for not listing what I'm replacing. I have an Access database
that is tied to some ASP .NET pages I have written. Each month I need
to create letters with some of the data that was entered from the
previous month. The problem is that I need the data split into nice
even columns and the data is coming out in one long stream. So, what I
did is where ever I need a line break I have programmed my ASP .NET
page to output <CR> then once in Word I just do a find and replace to
convert all of the <CR> tags to ^l which is the new line character
recognized by Word. Unfortunately if I just use ^l in my ASP output
code Word considers it as text and doesn't interpret the character.

As you mentioned it would be much easier to execute a single long mail
merge and then run the find and replace. This is true except that I
need all the letters broken up into separate files so I can save them
to different locations AND if the letters aren't separated the pages
numbers inserted by Word will say 1 of 76 or however many letters are
created in the one long mail merge. The reason I need the dynamic page
numbers is that the letters are of differing lengths each month
depending on how much information is inputed. Thanks for your quick
response.

Reply to this Message

Doug Robbins - Word MVP - 08 May 2006 18:38 GMT

If the page numbering is really the thing that is controlling this, format
the page numbering in the mailmerge main document so that it starts at one,
rather than continuing from the previous section (you may already have that)
AND instead of using a { NUMPAGE } field to get the total number of pages in
each letter, use a { SECTIONPAGES } field. Then your individual letters
will be numbered correctly.

However, adding the following code

With NewDoc
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<CR>", Replacewith:="^l",
MatchWildcards:=False, Wrap:=wdFindContinue, _ Forward:=True) = True
Loop
End With
End With

before the commands

NewDoc.Range.Fields.Update
NewDoc.SaveAs FldrPath & fnames(i)

in the

Private Sub app_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult
As Document)

event in the Addin, should (I haven't tested it) do the replacement on each
individual document. I would suggest however that you try and avoid having
to do it.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Sorry for not listing what I'm replacing. I have an Access database
> that is tied to some ASP .NET pages I have written. Each month I need
[quoted text clipped - 16 lines]
> depending on how much information is inputed. Thanks for your quick
> response.

Reply to this Message

what4893 - 08 May 2006 20:28 GMT

Thanks for your detailed response. I went ahead and placed the code you
recommended into the MailMerge macro written by GMayor. The mail merge
still works perfectly but it doesn't actually perform the find and
replace. Any ideas? The reason I'm going forward with this approach is
because ultimately I need the letters in separate files. The page
numbers were just a bonus in splitting the files and I thank you for
letting me know about the section page numbers. I didn't know that and
that may come in handy in the future.

No errors occur during the merge, the files are created and when I go
into the files they all still have my <CR>'s in them. I didn't think it
crucial before, and I appologize if this makes a difference, the <CR>
tags all appear in a table. That is why I'm trying to get them to
become line breaks, so that the columns each have one line in them. For
a little visual of my situation see below:

|--------------|-----------------|
|ABC<CR>ABC |123<CR>123<CR>123|
|<CR>ABC<CR> |<CR>123<CR>123 |
|--------------|-----------------|

The mail merge puts that data into the table cells and then I'm
replacing the <CR> tags with ^l (new lines). Thanks again for your help
on this project.

Reply to this Message

Doug Robbins - Word MVP - 09 May 2006 05:01 GMT

Eactly which macro on Graham's site are you using?

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thanks for your detailed response. I went ahead and placed the code you
> recommended into the MailMerge macro written by GMayor. The mail merge
[quoted text clipped - 20 lines]
> replacing the <CR> tags with ^l (new lines). Thanks again for your help
> on this project.

Reply to this Message

what4893 - 09 May 2006 07:40 GMT

I'm using the Word Add-In Template. It is a .dot file with the macro
for the mail merge built in. The macro intercepts the mail merge call
from Word and uses his code to perform the merge. Thanks again for your
help.

Reply to this Message

Doug Robbins - Word MVP - 09 May 2006 20:00 GMT

With the following insertion into the code in the Private Sub
app_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult
As Document) event,

NewDoc.Range.Fields.Update
'Begin insert
With NewDoc
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="<CR>", Replacewith:="^l",
MatchWildcards:=False, Wrap:=wdFindContinue, Forward:=True) = True
Loop
End With
End With
'End insert
NewDoc.SaveAs FldrPath & fnames(i)

When I execute a mailmerge main document containing a table with each cell
containing:

The quick brown fox jumps over the lazy dog.<CR>The quick brown fox jumps
over the lazy dog.<CR>The quick brown fox jumps over the lazy dog.<CR>The
quick brown fox jumps over the lazy dog.<CR>The quick brown fox jumps over
the lazy dog.

to a new document, and make use of the add-in, the contents of the each cell
in the resulting documents created by the add-in become:

The quick brown fox jumps over the lazy dog.
The quick brown fox jumps over the lazy dog.
The quick brown fox jumps over the lazy dog.
The quick brown fox jumps over the lazy dog.
The quick brown fox jumps over the lazy dog.

So it certainly works.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> I'm using the Word Add-In Template. It is a .dot file with the macro
> for the mail merge built in. The macro intercepts the mail merge call
> from Word and uses his code to perform the merge. Thanks again for your
> help.

Reply to this Message

what4893 - 10 May 2006 22:52 GMT

I tried pasting the code again, I deleted the Add-In file and used a
new copy. Everything I could think of and ultimately it looks like I'm
having trouble with Word or VB6. I thank you for your support on the
issue. If you happen to have a copy of the Add-In file with the code
pasted into it perhaps if you could email that to me I could try that
but everything I have tried hasn't worked. Thanks again for all your
support.

Reply to this Message

what4893 - 11 May 2006 06:35 GMT

Thank you sooooo much Doug. You are truly a Microsoft Word MVP Geneous.

Reply to this Message

Graham Mayor - 09 May 2006 07:03 GMT

Doug's additional code is meant to go in his add-in linked from the same
plage and not the original macro. The add-in is a much more elegant solution
where it can be used than the original macro (also based on Doug's code).

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Thanks for your detailed response. I went ahead and placed the code
> you recommended into the MailMerge macro written by GMayor. The mail
[quoted text clipped - 20 lines]
> replacing the <CR> tags with ^l (new lines). Thanks again for your
> help on this project.

Reply to this Message

Rate this thread: