Parameter query to mailmerge (Automated)

nathangoldhill

Registered User.
Local time
Today, 00:44
Joined
Aug 6, 2001
Messages
12
I have set up an automatic mail merge to run using a parameter query. The problem is that when i do it automatically using the code below it crashes with a doctor watson. The weird thing is if i do it manually from word it works. I have also set it up using dde instead of obdc and it still also opens another instance of access.
Any ideas?
Cheers Nathan. (Code See below)

Function MergeIt3()
Dim objWord As Word.Document
Set objWord = GetObject("y:\stats database\notices.doc", "Word.document")
' Make Word Visable.
objWord.Application.Visible = True
'set the mail merge data source as statstatus#2 database
objWord.MailMerge.OpenDataSource _
Name:="y:\stats database\statstatus#2.mdb", _
LinkToSource:=True, _
Connection:="QUERY notice", _
SQLStatement:="Select * from [notice]"
' Execute the mailmerge.
objWord.MailMerge.Execute
End Function
 
Try this. It works for me. I'm not able to change the location tho, if I try to put it anywhere other than directly on the C:/ it can't seem to find it. Not sure about that, but other than that one small issue it works wonderfully. I am using it to only merge the record that I am currently viewing, so of course your strCriteria would be different.
Option Compare Database
Dim WithEvents oApp As Word.Application
Private Sub Command37_Click()
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Set oMainDoc = oApp.Documents.Open("c:\Template Letter")
oApp.Visible = True

With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
sDBPath = "C:\Breach Notification.mdb"
strCriteria = [Forms]![All Breach Records]![ID#]
.OpenDataSource Name:=sDBPath, _
SQLStatement:="SELECT * FROM [Spreadsheet Breach Info Query]" & _
"WHERE [Spreadsheet Breach Info Query].[ID#] IN(" & strCriteria & ");"



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

oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Documents(2).Close wdDoNotSaveChanges
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1
End Sub
Private Sub Command47_Click()

End Sub
Private Sub Form_Load()
Set oApp = New Word.Application
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub

Hope this helps!!
 

Users who are viewing this thread

Back
Top Bottom