Use VBA to designate folder/filename saving of word mail merge result.
Hello. I am using Access 2003 and Word 2003. From a previous question I have the code below which is opening a Word mail merge document, producting a protected mail merge result, and saving that to a folder/filename. I need to adjust this code so that the folder and filename is referenced from fields in the access database tables. Many account execs will be creating contracts for different clients and I would like those to be saved in specific locations. I'm fairly new to VBA and the integration of Access/Word, so I'd be happiest with code I could cut/past/adjust references. The code below did require me to add the Microsoft Word reference and apply the Microsoft patch http://support.microsoft.com/kb/825765.
Thank you,
Lawrence Barnes
Thank you,
Lawrence Barnes
Private Sub btnMailMergeEarly_Click()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open("P:\Access Database\Contracts\BananaCt.doc")
wdDoc.MailMerge.Execute
wdApp.ActiveDocument.Protect wdAllowOnlyReading, , "jsic2633"
wdApp.ActiveDocument.SaveAs "C:\My Documents\Bananas.doc"
wdDoc.Close False
Set wdDoc = Nothing
wdApp.Quit
Set wdApp = Nothing
End Sub
Mail merge usually processes a number of records in a run, and if the output is a result document, it will contain a Section (part of a Word document) per record.
Will each run apply to a single client? If not we will have to devise some code to split up the output in order to distribute it.
Will each run apply to a single client? If not we will have to devise some code to split up the output in order to distribute it.
ASKER
Graham,
All the records on a particular run would be saved to the same location for each instance. For example, the account exec may want to produce a set of contracts for multiple accounts. The end path and files may be:
P:\AccountExecs\JSmith\Con
P:\AccountExecs\JSmith\Con
P:\AccountExecs\JSmith\Con
In the code above the path is hardcoded into it, so I want to call a few variables instead...which i where I need help. I'm expecting something like
" ' P:\AcctExecs\ ' "&tblMergeResult!AcctExec&
When the next batch of contracts is ready, the account exec runs his merge and they are saved, etc...
All the records on a particular run would be saved to the same location for each instance. For example, the account exec may want to produce a set of contracts for multiple accounts. The end path and files may be:
P:\AccountExecs\JSmith\Con
P:\AccountExecs\JSmith\Con
P:\AccountExecs\JSmith\Con
In the code above the path is hardcoded into it, so I want to call a few variables instead...which i where I need help. I'm expecting something like
" ' P:\AcctExecs\ ' "&tblMergeResult!AcctExec&
When the next batch of contracts is ready, the account exec runs his merge and they are saved, etc...
ASKER
Oh, lastly, if the folder does not exist, will it be created? And documents with the same path/name should be overwritten.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help.
please explain in details how do you want this done
as a starter
create a table with field names for file name and file path.. and what other info do you have in mind.