No Data For Mail-Merge 1

[lars7]

Hi Guy's,
I am trying to open a mail merge file with the following code:

Dim oApp As Object

Set oApp = CreateObject("Word.Application")

oApp.Visible = True

'Open the document.
oApp.Documents.Open ("H:\UserName\Review Letter.doc")

the word document opens fine but it does not prompt me to run the SQL command, as it does when I open it maually, so there is no datasource for the letters.

Does anyone know why this is not working?

Thanks in advance.

 

[lameid]

This is code I have to merge a particular query to a Word document. In this case I can merge to different Word documents based on a combobox. I store the hyperlink and then pull the filename out based on the fact that hyperlink elements are separated by # and the second element is the primary address. Getfield is a function I wrote that finds the specified element number in a string with a specified delimeter. I bet you could find it in the forums if you searched.

Private Sub cmdMerge_Click()
On Error GoTo Err_cmdMerge_Click
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim WordMerge As Word.MailMerge
    Dim strlnk As String
    strlnk = Me!cboDocID.Column(1)
    Set WordApp = New Word.Application
    
    Set WordDoc = WordApp.Documents.Open(FileName:="""" & getField(strlnk, "#", 2) & """", _
        ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
        WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
        wdOpenFormatAuto, XMLTransform:="")
    Set WordMerge = WordDoc.MailMerge
      WordMerge.OpenDataSource _
        Name:=CurrentProject().FullName, _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="Query qry Contract Letter Merge", _
        SQLStatement:="SELECT * FROM [qry Contract Letter Merge] Where [Contract Letters Customers].ProcessNow = Yes And [Contract Letters Customers].DocID = " & Me!cboDocID
    
    With WordMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    
    Set WordMerge = Nothing
    WordDoc.Close
    Set WordDoc = Nothing

    WordApp.Visible = True
    
Exit_cmdMerge_Click:
    Exit Sub

Err_cmdMerge_Click:
    MsgBox "Error: " & Err.Number & ": " & Err.Description
    
    Select Case Err.Number
    Case 5631
        If WordApp.Visible = False Then
            WordApp.Quit (False)
            Set WordMerge = Nothing
            Set WordDoc = Nothing
        End If
    Case Else
    End Select
    Resume Exit_cmdMerge_Click
    

End Sub

I hope this helps.

 

[lars7]

Hi lameid,

Thanks for responding,

I got your code to work but it does not populate the document with the SQL statement, insteed it supplies a box for the user to select a table from a pick list.

I think I'm missing somthing here but not sure how what:

"Connection:="Query qry Contract Letter Merge", _
SQLStatement:="SELECT * FROM tblmailMerge"

Thanks again.

 

[lameid]

qry Contract Letter Merge is the name of a query.

My code merges the specified document with the data to a new document.

Perhaps this would work?

"Connection:="Table tblmailMerge", _
SQLStatement:="SELECT * FROM tblmailMerge"

 

[lars7]

Hi LameId,

I changed my table to a query and removed the connection part and it works great, I also had to remove XMLTransform:="" from your original, not sure why.

Here is my working code:

Private Sub ListJobSubFamily_Click()
On Error GoTo Err_ListJobSubFamily_Click
Dim stDocName As String

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordMerge As Word.MailMerge

Set WordApp = New Word.Application

Set WordDoc = WordApp.Documents.Open(FileName:="H:\Doc Path\Review Letter.doc", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto)

Set WordMerge = WordDoc.MailMerge
WordMerge.OpenDataSource _
Name:=CurrentProject().FullName, _
LinkToSource:=True, AddToRecentFiles:=False, _
SQLStatement:="SELECT * FROM [QryMailMerge]"

With WordMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Set WordMerge = Nothing
WordDoc.Close
Set WordDoc = Nothing

WordApp.Visible = True

Exit_ListJobSubFamily_Click:
Exit Sub

Err_ListJobSubFamily_Click:
MsgBox "Error: " & Err.Number & ": " & Err.Description

Select Case Err.Number
Case 5631
If WordApp.Visible = False Then
WordApp.Quit (False)
Set WordMerge = Nothing
Set WordDoc = Nothing
End If
Case Else
End Select
Resume Exit_ListJobSubFamily_Click

End Sub

again thanks for the help.