Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge fiasco (access 2003)

    I have tried mightily, but ultimately, unsuccessfully to use code to set up a mail merge by automation. The fault is mine in that I do not believe that I adequately understand the code and references. I've tried to look at all the resources that this site has to offer and again to no avail.

    I have noticed that if i try to set up a manual mail merge from an access query with more than 1 condition I run into this problem. In access the query returns multiple records. However when I look at the same list in word there are no entries.

    Some help with this situation would be appreciated preferably with automation. I will happy to send the database if it would be helpful.

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

    Re: Mail Merge fiasco (access 2003)

    It would be helpful if you could provide detailed information about what you're trying to do: whcih table or query is to be the data source for the mail merge, and what are the conditions you're having problems with; a stripped-down copy of your database would help too.

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

    Re: Mail Merge fiasco (access 2003)

    You might also look at a couple of the tutorials on our site - there is one on Automation, and another on running Mail Merges using automation. Parameter queries are a notorious source of problems when running mail merges from Access, especially if you have Access User Security active. If that is your situation, you might want to consider doing a make table query and using that as your data source.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    I have to problems; 1- automating the merge and 2- getting the query to reliably work for the merge. I have tried looking at a variety of sites and examples of automation, unfortunately without success. I am trying to upload a stripped down copy of the database (192K) but it appears to be too large and the wrong format. I am waiting for the lounge administrator for some advice on the attachment.

    nebbia

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

    Re: Mail Merge fiasco (access 2003)

    See <post#=401925>post 401925</post#> for instructions on how to attach a database to a post.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    Thanks for the lead. Here is the attachment

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

    Re: Mail Merge fiasco (access 2003)

    The attached version shows how I would do it. The Automation code uses late binding, so it isn't necessary to set a reference to the Microsoft Word object library, and it uses ODBC to connect to the database.

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    The code that you suggested opens a 'new' word document, which will fill in any new fields that I insert with the proper results from the query. Two questions remain. First, can a previously constructed merge document be opened and merged automatically . This is preferable because I can then merge new query output with a canned merge document. Secondly I am unable to update this 'new' word document if I should change the query output.(form data) If I run the merge from word the original merge data is displayed in the merge fields seemingly in disregard for the new (form) query output. Is this resolvable?

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

    Re: Mail Merge fiasco (access 2003)

    I prefer to specify the data source each time dynamically. This may seem less flexible, but the advantage is that the merge will work even if the form (or even the entire database) is closed. If the data source refers to the form, you have a problem if the form is closed. In fact, I always store the merge document without a link to the data source, since the data source is set using the code each time it is needed.

    To open an existing merge document, change the line

    Set wrdDoc = wrdApp.Documents.Add

    to

    Set wrdDoc = wrdApp.Documents.Open("C:WordMerge.doc")

    with, of course, the appropriate path and filename substituted. To merge to a new document, add the following lines immediately above End With:

    .Destination = 0 ' wdSendToNewDocument
    .Execute

    If you want to merge directly to the printer, use 1 instead of 0.

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    I apologize that it has taken me so long to respond to your help, which I appreciate.

    The code worked flawlessly for opening a new merge document. However I would like to use a pre-made document which will add the merged data. When I try to make the changes that were suggested the merge document opens but I still need to match the fields each time and then preview the merged letters. It would be optimal If it were possible to automate the process so that clicking on the merge command button would result in a data merged pre-made document.

    There were some references that needed to be changed for my particular database. Since I made those changes I get an ODBC login in failure of either of the file in use type or login in authorization screen which asks for a password and database source.

    Thanks
    nebbia

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

    Re: Mail Merge fiasco (access 2003)

    After you have made a change to the database design, however small, you must close it before you can run a mail merge. Any design change locks the database exclusively, preventing a mail merge from succeeding.

    If the merge fields in the document correspond exactly to the fields in the SQL statement, it should be possible to merge directly to a new document or to the printer. This technique is used by many users in many of my databases every day without problems. Check the merge document and the SQL statement carefully.

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    The code ultimately worked just fine. I did make a small, but significant error in syntax.

    Another mail merge is based on a query which has a number of calculated fields. Once again I am at a loss for the appropriate syntax- hopefully I can get some direction. Thanks

    nebbia

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

    Re: Mail Merge fiasco (access 2003)

    There is a problem in your query - in record # 9, Duration = 0, so PerMonth (aka Loss/Month) is invalid. I would change the definition to
    <code>
    PerMonth: IIf([Duration]=0,Null,[MaxWtLoss]/[Duration]*28)
    </code>
    If you want to initiate a mail merge from Access, you could use code similar to that posted earlier by me, with the following change:
    <code>
    strSQL = "SELECT * FROM Summary"
    </code>
    Note: in the code I posted earlier, I had <code>wrdApp.Quit SaveChanges:=Nothing</code>, this should have been <code>wrdApp.Quit SaveChanges:=False</code>. Sorry about that.

  14. #14
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    I to have had my problems working with Word mail merge. I especially had problems when I needed to set criteria for queries. The solution that I normally use is a "make table query" and have the mail merge document look for the table as opposed to the query. It seems to stabilize the whole process for me. The query takes the criteria from a user front end form, makes the table then opens the word document.

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge fiasco (access 2003)

    My apologizes for the belated thanks. Everything worked out perfectly

Posting Permissions

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