Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trouble Understanding a Mail Merge From Access (Access 2000)

    I'm trying to get Access to open Word and run a Mail Merge. I have it partly working, but having a little trouble understanding what to put in the code. Here's the code that I have:

    Function MergeIt()
    Dim objWord As Word.Document
    Set objWord = GetObject("G:Labels.doc", "Word.Document")
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the Northwind database.
    objWord.MailMerge.OpenDataSource _
    Name:="G:WorkingCopy.mdb", _
    LinkToSource:=True, _
    Connection:="????????", _
    SQLStatement:="SELECT * FROM [??????]"
    ' Execute the mail merge.
    objWord.MailMerge.Execute
    End Function

    I'm just not sure what to put in the Connection, and the SQLStatement. If my query for the Mail Merge is called Labels Query, do I put that in the Connection? Do I put that same query name as the from (in brackets) in the SQLStatement?

    I also want it to run the Mail Merge to a new document so the user just has to press the print button in Word to print off the labels. How do I do that?

    Now, I've done this function in a Module. But I want to have a button on one of my switchboards that opens up Word and does my labels. Do I just do a button, then type the function in code behind the button?

    Just a little confused (when am I not). <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    If you want to merge from a query, you can specify it in the Connection argument and omit the SQL statement:

    objWord.MailMerge.OpenDataSource _
    Name:="G:WorkingCopy.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY Labels Query"

    You can specify that you want to merge to a new document by inserting

    objWord.Mailmerge.Destination = wdSendToNewDocument

    The Mailmerge.Execute method will perform the merge, provided that G:Labels.doc already contains valid merge fields.

    You can call the function from the On Click event procedure of the command button.

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

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    You might also find our Word Merge Tutorial of help in understanding things. What you are really doing is automation, so it also helps to understand the Word object. Our Automation Tutorial goes in to it a bit, and also contains references to a number of useful MS Knowledge Base articles.
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Works great! Thanks again, Hans

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Hi Jen
    Can you post your working solution so I can see how you got this to work.
    I need to do something similar in the near future.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    I have copied the code from above and it's correction from Hans but I get an error:
    Automation Error
    Unspecified Error
    This error appeared on the line in brown.

    The function is:
    <pre>Function MergeIt()
    Dim objWord As Word.Document
    <font color=d2691e>Set objWord = GetObject("c:dataGrantsLetter for Mailmerge.doc", "Word.Document")</font color=d2691e>
    ' Make Word visible.
    objWord.Application.Visible = True

    ' Set the mail merge data source as the Northwind database.
    objWord.mailmerge.OpenDataSource _
    Name:="c:dataGrantsNEW Community Database.mdb", _
    LinkToSource:=True, _
    Connection:="Rhonda's mailing list All orgs03"
    ' SQLStatement:="SELECT * FROM [??????]"

    objWord.mailmerge.destination = wdSendToNewDocument

    ' Execute the mail merge.
    objWord.mailmerge.Execute
    End Function
    </pre>


  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Patt

    I tried using your code and it worked for me once I changed:

    Connection:="Rhonda's mailing list All orgs03"

    to
    Connection:="Query Rhonda's mailing list All orgs03"

    But without this change, it didn't object to the line you highlighted.

    It did once actually, but mostly not, so I wasn't sure why it objected.
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Thanks John
    I need to make that correction so that it knows it's a query it's dealing with.

    However, I still get the message I described. I have checked the Microsoft Office Word 10 Object Library. I am using Access 2002, does this make a difference?

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Patt

    For 2002 you need to addd

    , SubType:=wdMergeSubTypeWord2000 after the connection value.

    But without this, I get an error, but not where you get it.
    Regards
    John



  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Thanks again John
    I still get the original error.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Another thought Patt

    Do you run multiple versions of Office on that computer?

    I have a machine that runs both 97 and 2000, and whenever I run a merge, it tries to use the version of Word that have last used. However it only works with the version that goes with that version of Office. So if I open Word 97y then try a merge from an Access 2000 db I get an automation error.

    So, when you get the erro, which version of Word is it trying to open?
    Regards
    John



  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    Hi John

    I have found what the error was.

    I was referencing the c: drive and not the d: drive. Silly me.

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Trouble Understanding a Mail Merge From Access

    I was going to ask whether you were sure you had the path/filename right but...!
    Regards
    John



  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    >>You can specify that you want to merge to a new document by inserting
    objWord.Mailmerge.Destination = wdSendToNewDocument>>
    I have a couple of questions:
    1. Where does the merged output go to?
    2. If you specify the above, where does this merged output go to?

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

    Re: Trouble Understanding a Mail Merge From Access (Access 2000)

    There are four options for the destination of mail merge output:
    - To a new document in Word (wdSendToNewDocument in VBA); this is the default.
    - Directly to the printer (wdSendToPrinter)
    - To e-mail (wdSendToEmail); there must be a merge field that contains the e-mail address.
    - To fax (wdSendToFax)

Page 1 of 2 12 LastLast

Posting Permissions

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