asked on

Print Word Mail Merge using VBA from Access

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

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shieldsco

ASKER

When I try to compile I a compile error : method or data member not found

shieldsco

ASKER

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

shieldsco

ASKER

I moved the quit statement and it works find with the exceptio of the user being promted to save.

shieldsco

ASKER

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

shieldsco

ASKER

Thanks very much

GrahamSkan

Sorry, I had to go out.

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