Automate Mail Merge with Word using ADP Project

[puppy39]

I have a main form with a subform. User selects the state and the suform displays the contacts for that state. I would like to add a button to the main form called "Mail Merge". When users click on this button it will then create a new mail merge with the filtered list of the subform with microsoft word to create labels. Can this be done using an ADP project connected to an SQL database. Their are several solution on this site but they are all for mdb.

 

[PWise]

It should work

 

[puppy39]

I opened two threads and in both of them got sample codes from programmers. Went back and forth making modifications based on their advise to later discover that the reason the codes were not working is because I am using an ADP project connected to SQL which is why I opened this thread under ADP Project.

 

[PWise]

Try this on for Size

Public Function MailMergeFromADP()
Dim a
Dim Result  As Integer
Dim woobj As Word.Application
Dim rst As ADODB.Recordset

Result = vbNo
Set woobj = CreateObject("Word.Application")
With woobj
    .Visible = True
    .Documents.Open "YourDocumentname", , 1
    .ActiveDocument.MailMerge.OpenDataSource "YourODCDatasource.odc", , , 
, , , , , , , , , "Select * from yourview"
    .ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
    .ActiveDocument.MailMerge.Execute 0
    .ActiveDocument.Fields.Update
    Do While Result = vbNo
        .ActiveDocument.PrintOut
        Result = MsgBox("Did Letter Print OK?", vbYesNoCancel + 
vbQuestion, CurrentProject.Properties("AppTitle"))
    Loop

    .Documents.Close 0
    .Quit
End With

End Function

 

[puppy39]

It has taken me a while and I have tried using pieces of the code provided but I still can't get the data to the merge. All I need is to open the word template I created and include the data selected. From there the user can take care of the rest. This is what I have so far.

Private Sub bttnWord_Click()
Dim LWordDoc As String
Dim oApp As Object

'Path to the word document
LWordDoc = "C:\MailMergeLabels.doc"

If Dir(LWordDoc) = "" Then
MsgBox "Document not found."

Else
'Create an instance of MS Word
Set oApp = CreateObject(Class:="Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Open filename:=LWordDoc
End If

End Sub