Solved

Use VBA to designate folder/filename saving of word mail merge result.

Posted on 2007-11-30
6
1,771 Views
Last Modified: 2013-11-27
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

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

Open in new window

0
Comment
Question by:Lawrence Barnes
  • 3
  • 2
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 20384165
<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.  >

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.

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20384578
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.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20398662
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\Contract1\Client1Contract
P:\AccountExecs\JSmith\Contract1\Client2Contract
P:\AccountExecs\JSmith\Contract1\Client3Contract

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&" ' \ ' "&tblMergeResult!Contract&" ' \ ' " & tblMergeResultContractNm&" ' .doc ' "  My guess is the code is wrong ( I struggle with it ).

When the next batch of contracts is ready, the account exec runs his merge and they are saved, etc...

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20398676
Oh, lastly, if the folder does not exist, will it be created?  And documents with the same path/name should be overwritten.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 20399581
It is difficult to pick out data from the result document, but we could gather it from the datasource before we start. Note that the code also creates any elements of the path as necessary. I haven't tested it, I'm afraid (It's my bedtime)

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")

    With wdDoc.MailMerge

        With .DataSource

            .ActiveRecord = wdFirstDataSourceRecord

            strPath = "P:\AcctExecs\" & .DataFields(AcctExec) & "\" & .DataFields(Contract)  

            strFile = strPath & "\" & .DataFields(ContractNm) & ".doc"

        End With

        .Execute

    End With

    wdApp.ActiveDocument.Protect wdAllowOnlyReading, , "jsic2633"

    CreatePath strPath

    wdApp.ActiveDocument.SaveAs strFile

    wdDoc.Close False

    Set wdDoc = Nothing

    wdApp.Quit

    Set wdApp = Nothing

End Sub

Sub CreatePath(strPath As String)

    Dim strFolders() As String

    Dim strDir As String

    Dim i As Integer

    

    strFolders = Split(strPath, "\")

    strDir = strFolders(0) 'drive

    For i = 1 To UBound(strFolders)

        strDir = strDir & "\" & strFolders(i)

        If Dir(strDir, vbDirectory) = "" Then

            MkDir strDir

        End If

    Next i

End Sub

Open in new window

0
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
ID: 31411976
Thank you for your help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now