Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge from Access (Access 97)

    Hello Access Gurus,

    I am wanting to automate a Mail Merge from Access 97 for a particular query. This process is to run the query in the background and then link it to a Mail Merge where the user can select the required document to link. It may be a different document each time or even a new document. I have tried using a macro where I open the query and then run the command to perform a mail merge but it isn't eligant!
    Can anyone help with either codes or macros?

    Thanks,

    Jocelyn Browning

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

    Re: Mail Merge from Access (Access 97)

    How do you mean "run the query in the background"? The mail merge process will open the query, you don't need to have it running in Access. The best way to mail merge (I think) is by starting (or activating) an instance of Word from Access by Automation, and initiate the mail merge from Access by executing Word VBA within Access.

    I don't know how familiar you are with VBA in Access and Word, and with Automation to control one application from another.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge from Access (Access 97)

    Thanks for your response Hans,

    I am familiar with VBA in Access but not so good in Word. I thought I would have to run the query in the background because the user is not sure which letter they will use for the Mail Merge and it also may be a new letter. I would like it to be implemented from the Access end. For instance, in Access application, the user clicks a button and this will start the Mail merge process ready for the user to locate the appropriate letter or create a new one from scratch. The user can then insert the merge fields and set up query options from within Word using the Mail Merge Helper Query options. What do you suggest?

    I look forward to your response.

    Cheers, Jocelyn

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge from Access (Access 97)

    I found the MS KB article here

    http://support.microsoft.com/default.aspx?...kb;en-us;209882

    to be useful in making Access automate Word for merge documents, but finally decided on restricting the user to predetermined files and gave them short cuts and 'Standard Work Procedures' to use the Word mail merge files.

    Hope this is useful

    Ian

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail Merge from Access (Access 97)

    You might find our tutorial on doing Mail Merges from Access to be of use.
    Wendell

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

    Re: Mail Merge from Access (Access 97)

    I have attached a function I use to initiate a mail merge from Access. The function takes 3 arguments:
    strTemplate is a string containing the path/filename of a Word document or template to be used as basis for the mail merge.
    strSQL is a string containing the SQL string that will act as data source of the mail merge. It must refer to tables/queries in the current database.
    blnDirect is a boolean; if True, the mail merge is executed immediately (this is possible only if strTemplate already contains all merge fields you need); if False, the user can edit the merge document.

    What I usually do is prepare a document for mail merge, then make it into a normal document again while leaving the merge fields in the document. This makes the document open much faster. The code will turn it into a mail merge document again and set the data source.

    The code expects an ODBC data source named Access; this name can be replaced by another one. And, of course, you must set a reference to the Microsoft Word 8.0 Object Library (in any module, select Tools | References...)
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge from Access (Access 97)

    Couldn't a Parameter query in Access work in this situation and then just use the Office links icon to "Merge to Word" whoever he wants letters sent to?
    If its many people, then do a regular select query, and the Merge to word with Office links?
    Am I missing something in between the lines?
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge from Access (Access 97)

    Merge to Word starts a wizard. You wouldn't want to do that from code, and the purpose was to automate the process. Parameter queries require a response.
    Charlotte

Posting Permissions

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