Word 2000 Automate save and specify filename in a mail merge I'm

[Jeanne Moos]

I am completely new to word vba script so please explain step by step.

There are two things I'm trying to accomplish.

1.Automate saving via a mail merge.
2.Ensure each document is saved automatically with a specified filename in
specific location

I have a mail merge set up between word and excel and it works fine. After I
run my mail merge I will end up with 60 separate word documents opened on my
pc. I want to be able to automate the save so that each document gets saved
to my harddrive instead just opening up on my computer screen.

I also want the filename to be specific to each document. I have 2 bookmarks
one is called filenb (a.k.a the application number) and the other is emp
(a.k.a. employee name. They always gets filled in on each document. I want
each document to save with its application number-employee name (i.e.
App1-smith) to my harddrive.

Is this possible?
Any suggestions? Thanks so much.

 

[Doug Robbins]

A couple of things that you mention are not consistent with actually
executing a mailmerge. Specifically, the bookmarks - they will not survive
the execution of a mailmerge and what do you mean by "They always gets
filled in on each document"? Anyway, if you have the application number and
the employee name in the data source, you can use the following method that
I have used that involves creating a separate
catalog type mailmerge maindocument which creates a word document containing
a table in each row of which would be your data from the database that you
want to use as the filename.

You first execute that mailmerge, then save that file and close it. Then
execute the mailmerge that you want to create the separate files from and
with the
result of that on the screen, run a macro containing the following code
and when the File open dialog appears, select the file containing the table
created by the first mailmerge

' Throw Away Macro created by Doug Robbins
'
Dim Source As Document, oblist As Document, DocName As Range, DocumentName
As String
Dim i As Long, doctext As Range, target As Document
Set Source = ActiveDocument
With Dialogs(wdDialogFileOpen)
.Show
End With
Set oblist = ActiveDocument
Counter = 1
For i = 1 To oblist.Tables(1).Rows.Count
Set DocName = oblist.Tables(1).Cell(i, 1).Range
DocName.End = DocName.End - 1

'Change the path in the following command to suit where you want to save
the documents.
DocumentName = "I:\WorkArea\Documentum\" & DocName.Text
Set doctext = Source.Sections(i).Range
doctext.End = doctext.End - 1
Set target = Documents.Add
target.Range.FormattedText = doctext
target.SaveAs FileName:=DocumentName
target.Close
Next i


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Jeanne Moos]

Thank you for the information but I think I'm doing something wrong. It
doesn't seem to be saving the files at all. Please help, I'm a newbie to VB &
mail merges.

I have an excel spreadsheet with following data:
Documentname City State
APP1-DR Princeton NJ
APP2-JM NYC NY
APP3-LR Miami Florida

I also have a form letter.
Form Letter:
<<Documentname >>

Location: <<city>> <<state>>

I placed the macro you provided into my form letter: Note I only changed the
location where I want the each file to be saved.

Sub
Dim Source As Document, oblist As Document, DocName As Range, DocumentName
As String
Dim i As Long, doctext As Range, target As Document
Set Source = ActiveDocument
With Dialogs(wdDialogFileOpen)
.Show
End With
Set oblist = ActiveDocument
Counter = 1
For i = 1 To oblist.Tables(1).Rows.Count
Set DocName = oblist.Tables(1).Cell(i, 1).Range
DocName.End = DocName.End - 1

'Change the path in the following command to suit where you want to save
the documents.'
DocumentName = "C:\data\temp\" & DocName.Text
Set doctext = Source.Sections(i).Range
doctext.End = doctext.End - 1
Set target = Documents.Add
target.Range.FormattedText = doctext
target.SaveAs FileName:=DocumentName
target.Close
Next i
End Sub

I would like a separate form letter to be saved in C:\data\temp. I'd like
each filename to be the documentname listed in the excel spreadsheet.
For example:
APP1-DR
APP2-JM
APP3-LR


Again thank you in advance Jeanne.

 

[Doug Robbins]

Did you follow the whole procedure, INCLUDING creating the catalog type
mailmerge document in which you would just have the <<Documentname>> field
in a single cell table. You must execute that mailmerge, thereby creating a
new document containing a table of the "filenames". Then you save and close
that document. Next you execute your original mailmerge and with the
document so create on the screen, you then run the macro. It will cause the
file open dialog to be displayed and when that happens, you must select the
document created by the catalog mailmerge. The macro will then run,
creating each of the separate files.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP