Word VBA Mail Merge (1 Viewer)

[danian]

Hi,

I am tring to create a Word VBA Mail Merge. I have a MySQL database that runs on a local machine and have a view setup called vwprintqueue. I am able to manually run the mail merge from inside Word but want the mail merge to complete automatically from a click of the button.

I have created the VBA code but can not get the merge to run as i get the errors:
"The operation can not be complete because of dialog or database engine failures. Please try again later"
&
"Word was unable to open the data source"

This happens when the code runs the line:

Call .OpenDataSource("C:\connection.dsn", , , , , , , , , , , sConnection, sSQL, , , wdMergeSubTypeWord2000)

and i have not got a clue why. The code i use is:

    Option Explicit

    Sub AutoOpen()
    Dim oForm   As New frmReportMonth
    
    'Open the form
        With oForm
            .Show
            'If iReportMonth is greater than 1 run the report
            If iReportMonth <> -1 Then
                Call RunReport
            End If
        End With
    End Sub


    Private Sub RunReport()
        Dim sConnection As String
        Dim sSQL        As String
            sConnection = GetConnectionString
            sSQL = GetSQL

        'Run the mail merge
        With ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            MsgBox ("STOP")
            Call .OpenDataSource("C:\connection.dsn", , , , , , , , , , , sConnection, sSQL, , , wdMergeSubTypeWord2000)
            
            Call .Execute
            
        End With
        
        Me.Close False, False, False
    End Sub


    Private Function GetRecords() As Recordset
        Dim adoConn         As ADODB.Connection
        Dim adoRs           As ADODB.Recordset
        Dim sSQL            As String
        Dim sConnStr        As String
        
        sConnStr = GetConnectionString()
            
        Set adoRs = New ADODB.Recordset
        
        Set adoConn = New ADODB.Connection
            Call adoConn.Open(sConnStr)
        
            sSQL = GetSQL
            
            Call adoRs.Open(sSQL, adoConn)
            
            Set GetRecords = adoRs
        Set adoRs = Nothing
        Set adoConn = Nothing
        
    End Function


    Private Function GetSQL() As String
                
        GetSQL = "SELECT * from vwprintqueue;"
        
    End Function

Do you know why I get those errors and how i can stop them?

Please see attached file.

Thanks in advance,
DW

 

[danian]

Please see attachment...

 

[ajetrumpet]

what do you have the CALL statement before the mailmerge actions in your code?

 

[danian]

what do you have the CALL statement before the mailmerge actions in your code?

I did not write the originla code, i am trying to use the existing code on another project, so not a clue why the call is being used. Why do you ask? is it done better a different way? What do you suggest?

thanks,
DW

 

[ajetrumpet]

take the call words out of the code. they shouldn't be there.

 

[danian]

take the call words out of the code. they shouldn't be there.

When i remove the Call the line goes red i then removed the call . and still the line goes red....

.OpenDataSource("C:\connection.dsn", , , , , , , , , , , sConnection, sSQL, , , wdMergeSubTypeWord2000)

 

[ajetrumpet]

the CALL words should not be in there, period. (i'm pretty sure about this). as for the ODBC, I'm not experienced enuf in it to you help you with it, but the errors tell me that the problem lies there. try some googling