?
Solved

Print Word Mail Merge using VBA from Access

Posted on 2012-09-12
7
Medium Priority
?
1,247 Views
Last Modified: 2012-09-12
I'm using the below code on an access 2007 form to perform a word 2007 mail merge. The merge works find however I'm need code to print all documents and close documents and quit the application


Option Compare Database
Dim WithEvents oApp As Word.Application
Private Sub Command0_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.OpenQuery "qryMail1"
DoCmd.OpenQuery "qryUpdMailSent"
DoCmd.SetWarnings True



Set oApp = CreateObject("Word.Application")

 Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String

    Set oMainDoc = oApp.Documents.Open("\\v07.med.va.gov\atg\Psych\Homeless Program\Interest List Database\InterestLetter2.doc")
oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "\\v07.med.va.gov\atg\Psych\Homeless Program\Interest List Database\Interest List V2.accdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblMail1]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
   
 
 
Set oApp = Nothing

End Sub
0
Comment
Question by:shieldsco
  • 5
  • 2
7 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 38390979
The result document is always the ActiveDocument, so you can do:

With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
 End With
oMainDoc.Close wdDoNotSaveDocument

oApp.ActiveDocument.Printout

oApp.Close wdDoNotSaveDocument
oAPP.Quit

Open in new window


However if you don't want to save the result document, you could just merge directly to the printer

With oMainDoc
        .MailMerge.Destination = wdSendToPrinter
        .MailMerge.Execute
 End With
oMainDoc.Close wdDoNotSaveDocument

oApp.Quit

Open in new window

0
 

Author Comment

by:shieldsco
ID: 38391126
When I try to compile I a compile error : method or data member not found
0
 

Author Comment

by:shieldsco
ID: 38391241
There are two problems - after printing the user is prompted : Do you want to save changes?
The are two letters that open - the template and the populated document. The template closes ok however the populated document prompts the user.
The second problem is when I run the merge again I get a run-time error 462 remote server does not exist or is unavailable. It appears that word is not being released. I can close and reopen the database and then the merge works again
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:shieldsco
ID: 38391277
I moved the quit statement and it works find with the exceptio of the user being promted to save.
0
 

Author Comment

by:shieldsco
ID: 38391349
made a few adjustments and it works find - I just need to put some error checking in.

Thanks

Private Sub Command0_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.OpenQuery "qryMail1"
DoCmd.OpenQuery "qryUpdMailSent"
DoCmd.SetWarnings True



Set oApp = CreateObject("Word.Application")

 Dim oMainDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String

    Set oMainDoc = oApp.Documents.Open("\\v07.med.va.gov\atg\Psych\Homeless Program\Interest List Database\InterestLetter2.doc")
oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "\\v07.med.va.gov\atg\Psych\Homeless Program\Interest List Database\Interest List V2.accdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblMail1]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With

With oMainDoc
        .MailMerge.Destination = wdSendToPrinter
        .MailMerge.Execute
 End With
oMainDoc.Close wdDoNotSaveDocument
oApp.ActiveDocument.Close wdDoNotSaveDocument




    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
   ' oApp.ActiveWindow.WindowState = 1
   
 oApp.Quit
 
 Set oApp = Nothing



End Sub
0
 

Author Closing Comment

by:shieldsco
ID: 38391355
Thanks very much
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38391449
Sorry, I had to go out.

I glad that you have been able to sort it out.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question