Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Automating mail merge (Word 2003)

    I am redesigning a function caterer's call centre application originally written in Access 97 but now in Access 2003. All of the many documents were created as reports, which meant that the client had to rely on programmers to make any text changes or to create new reports. The intention is to make the client as independent as possible of outside assistance and I am trying to use Word's mail-merge capabilities as document preparation platform. The underlying queries take their parameters from data in the Access form. The operator will select the document (booking confirmation, invoice or whatever) from a combo box (drop-down list) and then click on a button to launch the review or print operation.
    From MSDN and other sources, there seems to be two ways of specifying and opening the document. One is to use the SystemObject model. The other is to use the Application model internal to MS Office. However, MS Access does not seem to be fully aware of these (my VBA list of references includes MS Office 11 Object Library and MS Word 9 Object Library) and i am having difficulty in starting up Word. The four things I need are (1) advice on the preferred Objects and Methods to get Word going (it displays the correct information when launched by hand while Access is running), (2) a methodology for opening word with a parameter (Record ID) to search the query so that I can run it in a multi-user mode and not have the query itself limited by parameter and (3) a method of bypassing the dialogue box asking for confirmation that the query is ready to go and (4) the ability to control whether Word simply opens for review or proceeds directly to print. In an ideal world, Access will simply open the document and pass the parameters and the rest will be done with VBA or macros in Word. This will assist me in preparing templates which the client can use to create his own documents.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating mail merge (Word 2003)

    Welcome to Woody's Lounge!

    You'll find some useful information and links in these tutorials by our moderator Wendell Bell:
    Automation 101
    Managing Merges with Access

    Short answers to your questions:
    1) Create an object of type Word.Application to start Word.
    2) Create the SQL on the fly, in Visual Basic, and specify this SQL string in the OpenDataSource method of the MailMerge object.
    3) Specify SubType:=wdMergeSubTypeWord2000 for the OpenDataSource method.
    4) Specify Destination = wdSendToNewDocument or Destination = wdSendToPrinter for the MailMerge object.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Automating mail merge (Word 2003)

    Thank you for this very useful info, Hans. I have built a process which sends the right data to the mailmerge document and it works with one small proviso.
    Actually, I adopted a slightly different procedure from the one you recommended. The system which this stuff is intended for is a call centre with multiple users. To avoid conflicts within Access, each workstation logs into a different copy of the front-end application, and each of these has its own working directory. Mailmerge-type templates (written by the client who is thus able to bypass Access's report processing) are copied into the workspace, the results of the query are exported to a .csv file in the same workspace and this data source is built into the template. Hence the mailmerge file always knows where to look for data. Now to the proviso.
    Even though it knows where to look, the mail-merge file insists on asking the user to confirm that it is getting the data from the .csv file and is this OK. Is there any way in which I can suppress this message and tell it to go ahead anyway ? I would like to have the document become visible when in a preview mode or to print out silently otherwise. The process "as is" conflicts somewhat with these intentions.
    Apologies for the delay in acknowledging your assistance Hans. I have been away on my holidays. I took my laptop with me and commissioned a wireless connection from a local provider. Typically, we only got it going on the day I came home !

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating mail merge (Word 2003)

    The attached zip file contains a .reg file that can be used to suppress the warning. You'll have to double-click the .reg file on each of the PCs where the mail merge is run.

    You can inspect the contents of the .reg file in Notepad; this version is for Word 2003 only. See You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source for more info.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Automating mail merge (Word 2003)

    What splendid service one gets in this establishment ! I will set up the bypass on the prompt message, but having read the MS cautionary note, i will discuss the security issues with the client before implementing it. Now, I know I am not the brightest cookie in the jar, but I am new to this forum and its architecture. I pressed every button in sight but could not find the .zip file !
    While I am picking your brains, Hans, can i copy the mailmerge file to a straight Word document without the link back to the database (for archival purposes) ? There is some sort of TextStream command, but I want to maintain the formatting and I am not sure whether this will do that. In other words, I would like to keep the document as it is but with the data in the fields pulled out and deposited in the same place in the document ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating mail merge (Word 2003)

    I apologize, I simply forgot to attach the zip file. Here it is.

    You can keep the mailmerge fields but sever the connection with the data source as follows:
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Immediate window (Ctrl+G)
    - Type ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
    - Press Enter.

    If yoiy want to replace the mailmerge fields with data, simply merge to a new document.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Automating mail merge (Word 2003)

    Thanks a lot, Hans. I am so relieved to find that I am not left looking like a total idiot !
    The mail merge operation (really just writing a letter to a specific client) is run from the usual Preview/Print buttons. I will include your code to create the archive file after the review/print process is completed and then clean up. The intention is to hide as much of the mechanics of MS Office as possible from the end users (and hence my reliance on expert outside advice, such as yours).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •