?
Solved

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

Posted on 2007-11-30
6
Medium Priority
?
1,780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

777 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