Mailmerge and VBA Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jim - 15 Jan 2007 17:24 GMT

I work for a business that uses third-party software to merge data
from our customer database into individual letters in Word 2000. I
create a main document as a form letter, insert all of the appropriate
merge fields and identify the main document to the software. I do not
actually perform the mailmerge in Word; the third party software finds
the proper customer record, does the merge, and I get a new document
that is merged with all the data in the proper spots and in all
respects looks just like any formletter that I could merge through
Word 2000.

After the merge happens and I get a newly merged document, I would
like the user to be able to run a VBA macro that will allow the user
to save the new document with a filename that is based on the contents
of several of the mergefields. However, after the merge

ActiveDocument.MailMerge.DataSource.DataFields(index).Value

gives me a runtime error 5852 "Requested object not available" for any
correct value of index and therefore I have no data to massage into a
properly constituted filename. If I try to access this before the
merge is done, I have values in each of the DataFields and I can
massage that data in any way necessary to create a filename, but I
don't know how to save it for after the merge or how to pass it
through the merge to the other side so that it exists after the merge.

I had originally tried to set the title property in the merge document
in this manner {TITLE "{MERGEFIELD f15}{MERGEFIELD f12 \@ "' exam'
M-d-yyyy"}"} so that I could access it through using
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle), but setting
the title that way does not always work for me and I don't need the
title of the document showing up anywhere in the text, which happens
by using the {TITLE} field

How can I (or can I at all) carry the contents of the data fields that
I need to create my properly constituted filename past the mailmerge
process, or in the alternative, how and where can I save it
"pre-merge" so that I can use it "post-merge?"

Any hope for me?

jim

Reply to this Message

Peter Jamieson - 15 Jan 2007 20:59 GMT

It isn't exactly what you describe, but I suggest you have a look at

http://www.gmayor.com/individual_merge_letters.htm

The following is my own old macro that does one merge per source record. It
won't work if the merge consumes multiple records per merge. You'll need to
modify the code that sets up the output document name, and probably debug
the thing (sorry!)

Peter Jamieson

Sub ProduceOneDocPerSourceRec()
'

' NB, needs bettor error management and doubtless other things a VBA expert
' will point out.

Dim intSourceRecord
Dim objMerge As Word.MailMerge
Dim strOutputDocumentName As String
Dim TerminateMerge As Boolean

' Need to set up this object as the ActiveDocument changes when the
' merge is performed. Besides, it's clearer.

Set objMerge = ActiveDocument.MailMerge
With objMerge

' If no data source has been defined, do it here using OpenDataSource.
' But if it is already defined in the document, you should not need to
define it here.

' .OpenDataSource _
' Name:="whatever"

intSourceRecord = 1
TerminateMerge = False

Do Until TerminateMerge
.DataSource.ActiveRecord = intSourceRecord

' if we have gone past the end (and possibly, if there are no records)
' then the Activerecord will not be what we have just tried to set it to

If .DataSource.ActiveRecord <> intSourceRecord Then
TerminateMerge = True
' the record exists
Else

' while we are looking at the correct activerecord,
' create the document path name
' e.g. - you will need to change this -
strOutputDocumentName = "c:\mydoc\" &
.DataSource.Datafields("myfield").Value &
".doc"

.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Destination = wdSendToNewDocument
.Execute

' The Activedocument is always the output document
' Add any parameters you need to these calls
ActiveDocument.SaveAs strOutputDocumentName
ActiveDocument.Close
intSourceRecord = intSourceRecord + 1
End If
Loop
End With
End Sub

>I work for a business that uses third-party software to merge data
> from our customer database into individual letters in Word 2000. I
[quoted text clipped - 37 lines]
>
> jim

Reply to this Message

jim - 16 Jan 2007 17:59 GMT

Peter -

Thanks for the response. I had found Mayor's site after I posted my
query and I saw his solutions. I think it can be modified to what I
need. If that doesn't work, then perhaps I can use
System.PrivateProfileString and just save what I need to a separate
file, but that whole idea of making a whole separate file just seems
so kludgey. I would love to do it as you did but just like you note,
ActiveDocument before the merge is not the ActiveDocument after the
merge. I guess I can't get there from here!

Jim

>It isn't exactly what you describe, but I suggest you have a look at
>
[quoted text clipped - 108 lines]
>>
>> jim

Reply to this Message

Peter Jamieson - 16 Jan 2007 18:59 GMT

Jim,

I've read your message again and realised that I'd answered a different
question than the one you asked.

Given that you have to go via an existing pre-programmed merge process, I'd
suggest either
a. you define your mail merge main document to wrap up the output file name
in an unambiguous way - for example, if you want to build your output
filename from field1,field2, and field3, put something like the follwing at
the beginning of your merge main document:

@@@{ MERGEFIELD field1 }@@@@@@{ MERGEFIELD field1 }@@@@@@{ MERGEFIELD
field1 }@@@

then use a macro to extract the results for each section of your output
document and delete the stuff within "@@@"

Snags:
1. can you guarantee that "@@@" is unambiguous?
2. can you guarantee that you can remove the stuff within "@@@" without
messing up the layout/pagination of your layout?

or
b. (maybe) do two merges, one with the output you really want, and the
other with the data you want to use to create filenames in an unambiguous
format. Then use a macro to step through each pair of output records in
turn...

or
c. (perhaps) do one merge to create a list of the records you need to
merge, then for each entry in the list, create a new merge request to your
merge software to merge a single record, providing the output file name
(might work depending on how your merge software works)

rather obvious ideas but the best I can do right now...

Peter Jamieson

> Peter -
>
[quoted text clipped - 125 lines]
>>>
>>> jim

Reply to this Message

jim - 17 Jan 2007 13:42 GMT

Peter -

You did answer my question.....well, sort of, anyway!

I think you confirmed that I cannot carry over a variable from
pre-mailmerge to post-mailmerge unless I control the mail merge
programmatically thru VBA as the word user and not as the third-party
software user. Unfortunately, I cannot access the data in the
database in a practical and "user-proof" way without using the
third-party software, so any other way is pointless.

I will have to insert the text into the document by manipulating
mergefields and then take it out and use it as you and Mayor suggest.

Thank you for all of the input. I really appreciate being able to
bounce ideas off somebody else.

jim

>Jim,
>
[quoted text clipped - 164 lines]
>>>>
>>>> jim

Reply to this Message

jason@napavintners.com - 25 Jan 2007 17:29 GMT

jim,

can you share the name of the third party software you're using to
merge? we are converting to a new db schema that is making merging more
difficult and would like to know if there are solutions out there that
could make life easier. as of now our data lives in access but is
making its way to sql very soon.

any suggestions would be greatly appreciated.

jason

> Peter -
>
[quoted text clipped - 186 lines]
>
> >>>> jim

Reply to this Message