Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / November 2004

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jeanne Moos - 15 Nov 2004 22:19 GMT
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 - 16 Nov 2004 00:45 GMT
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

Signature

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

>I am completely new to word vba script so please explain step by step.
>
[quoted text clipped - 21 lines]
> Is this possible?
> Any suggestions? Thanks so much.
Jeanne Moos - 17 Nov 2004 17:54 GMT
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.:)

> A couple of things that you mention are not consistent with actually
> executing a mailmerge.  Specifically, the bookmarks - they will not survive
[quoted text clipped - 64 lines]
> > Is this possible?
> > Any suggestions? Thanks so much.
Doug Robbins - 18 Nov 2004 01:01 GMT
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.

Signature

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

> 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
[quoted text clipped - 134 lines]
>> > Is this possible?
>> > Any suggestions? Thanks so much.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.