+ Reply to Thread
Results 1 to 32 of 32

Print mail merge document

  1. #1
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Print mail merge document

    Hi guys

    Can anyone please help me to write vba code that will print the ms word mail merge document when a button is pressed on Excel userform.

    Any help would be much appreciated.

    Thanks

  2. #2
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    The mail merge document is already connected to Access table so I just want the code that will run when the user press print button and then it will print off the mail merge document .

    It seems very simple, like open the word document and print it off but hope anyone send me the code for this.

    Thanks.

  3. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Print mail merge document

    Hi
    The following code should work with MS Word document. You need to enter the full path of the document between the Quotation marks after sPath. It will ask you for how many copies you require.

    Please Login or Register  to view this content.
    Hope this helps

  4. #4
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi DBY

    Thanks for your reply. I have written the following code. As the word document is already linked to the Access query so Whenever we want to change the Access query I use the following code:

    Please Login or Register  to view this content.
    Now the following code is to open the mail merge document and print it off:

    Please Login or Register  to view this content.
    But the code doesn't print the right mail merge document. I hope you can figure out my prob.
    Thanks

  5. #5
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Print mail merge document

    Hi
    Is "C:\B.doc" the full path of your document? Is it within a folder for example, My Documents?

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    I hope you realise that your code doesn't do any merging more than 1 document.
    Your code can be reduced to:

    Please Login or Register  to view this content.


    If you want to use mailmerge the code should be:
    Please Login or Register  to view this content.
    But in spite of this I don't see any connection between the result of the AccessQuery and the mergemaindocument.
    Last edited by snb; 10-26-2011 at 09:50 AM.



  7. #7
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi Snb

    The code is working absolutely fine. CAn you tell me how to quit MS word document after printing automatically.

    Thanks a lot.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    before the last 'end with'
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Snb, If I write the Quit as below in code then it gives me "Object invoked has disconnected from its clients" error message.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    You didn't do what I suggested:

    Please Login or Register  to view this content.
    if you analyse the code:
    a document has no quit method,
    an application has.

    An alternative:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi snb

    The code is working but again one problem sometimes it gives me Runtime Error 5631"Word couldn't merge the main document with the datasource because the data records were empty or no data records matched your query option".

    But in actual I am using the right access query and data records are not empty.

    Thanks for your help so far.

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    Without seeing your database it's impossible to help you with that.

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    There was still something left to improve:

    Instead of using

    Please Login or Register  to view this content.
    this suffices and is faster:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi Snb

    When I use this code then it opens the word document and print it off but it doesn't close the word document after printing and if close it by pressing X on the top right hand side of the word windown then it gives me the following error message:

    Runtime Error 4605
    This method or property is not available because a document window is not active.

    at the following line of code:
    Please Login or Register  to view this content.

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    So what happens with:

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi snb

    Thanks its working now. But can you also tell me as I am writing the following code to change the Query in Access first before merging but what if two or more users updating the same query and the same word document at the same time then it will give the wrong result and lock the word document. Any idea to get rid of this problem???
    Please Login or Register  to view this content.

  17. #17
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    You can do the sql-instruction in the Word document:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi snb

    I am using the following code but it gives me "Command failed" message

    Please Login or Register  to view this content.

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    You should check the connectionstring:

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Snb, It gives me the error message "Object doesn't support this property or method" at the line marked red

    Please Login or Register  to view this content.

  21. #21
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    do not use connectionstring but connectstring.

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Snb, now it gives me runtime error 9121 "Buffer for return string is too small"

  23. #23
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    What if

    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Again the same error message appears.

  25. #25
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Print mail merge document

    This routine is a bit more involved than snb's routine but it might help. The first part is sheet code that shows two examples for running MergeRunMDB.

    Please Login or Register  to view this content.

  26. #26
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    I'd say: why not posting this document ?

  27. #27
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Thanks a lot Kenneth Hobson. I will try the code on Monday in the office and let you know how it goes.

    Thanks snb too for your help.

  28. #28
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Kenneith hobson, I tried that code but it doesn't do anything and not even it prints off something.

    Please help me to solve it.

    Thnaks

  29. #29
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Print mail merge document

    You will have to help us to help you: post a sample workbook, a sample worddocument & a sample database.

  30. #30
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Hi Snb and Kenneth

    I am writing the following code and its working perfectly now:
    Please Login or Register  to view this content.
    Thanks guys for your help.

  31. #31
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Print mail merge document

    I am glad that we could help.

    I used early binding which requires the reference as shown in my comment. Nothing is usually not the the case. If you step through code with F8, you will see why nothing might happen. In mine, it exits if a file or folder's path does not exist. I could have put a message in debug.print or a MsgBox for those cases I guess. I recommend that you always Compile a project before running.

    Your MergeRunMDB should be in a Module.

    Running my MergeRunMDB would have probably been:
    Please Login or Register  to view this content.
    Last edited by Kenneth Hobson; 10-31-2011 at 09:40 AM.

  32. #32
    Registered User
    Join Date
    08-09-2011
    Location
    Nottingham
    MS-Off Ver
    Excel 2003
    Posts
    51

    Re: Print mail merge document

    Thanks Kenneth for your help.

    Could you please also see "Moving focus back to first textbox " thread and help me to solve it.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1