Mail Merge from VB 2005

[diston52]

Good afternoon. My name is Don, and I'm working with my first dealings with Mail Merge.
I have been reading a discussion started by a user called Chubbly Geezer, and have been trying different solutions presented to him.
I am using VB from Visual Studio 2005, and SQL Server 2005 Express as a backend, with Office 2003.
My SQL Server is set up with Mixed security.
I am trying to use the doc.MailMerge.OpenDataSource in my code to cause the work document to query the database when I open the document.

The code I am using is as Follows:

My objects are declared at the class level:

Protected appWord As Object
Protected doc As Microsoft.Office.Interop.Word.Document
Protected bFormClosed As Boolean = True
Protected SqlConnectionString = My.Settings.MountainRailMarketingConnectionString
Protected connectionString As String = SqlConnectionString
Protected connection As New SqlConnection(connectionString)
Protected sql As String
Protected cmd As New SqlCommand(sql, connection)
Protected daMarketingClient As New SqlDataAdapter(cmd)
Protected dsMarketingClient As New DataSet
Protected pRow As DataRow
Private myNull = System.DBNull.Value

and my code is as follows:

Private Sub btnMailingLabels_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMailingLabels.Click
'---------------------------------------------------------------------------------------------------------------------------
' Source: btnMailingLabels_Click
'
' Change Log Modification
'
' Date Author SCN Description
' ---- ------ -------- ------------------------------------------------------------------------------------
' 5/12/2007 djd Initial form
'---------------------------------------------------------------------------------------------------------------------------


Try
appWord.Visible = True
OpenFileDialog1.Filter = "Text files (*.DOC)|*.DOC"
OpenFileDialog1.ShowDialog() 'display Open dialog box
doc = appWord.Documents.Open(OpenFileDialog1.FileName)
doc.MailMerge.OpenDataSource("C:\Documents and Settings\Don\My Documents\My Data Sources\Durbin Greenbrier Marketing List\localhost_sqlexpress MountainRailMarketing ClientList.odc", _
Connection:="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=DurbinMarketingUser;Password=letmein!@#;Initial Catalog=MountainRailMarketing;Data Source=localhost\sqlexpress;", _
SQLStatement:="select * from ""ClientList"" WHERE cltPrintFlag = 0", _
SubType:=Word.WdMergeSubType.wdMergeSubTypeOther)
appWord.Documents(1).Activate()
doc.Activate()
bFormClosed = False
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

When I try to execute this, I get presented with a SQL Server Login window
with the login information in place, when I try to "test the connection" I get a message Test connection failed because of an error in the initializing providor. Login failed for DurbinMarketingUser.
And I get a message that Windows was unable to open the datasource.

I would appreciate any help that you might be able to give.

Thank You

[Dököll]

Good afternoon. My name is Don, and I'm working with my first dealings with Mail Merge.
I have been reading a discussion started by a user called Chubbly Geezer, and have been trying different solutions presented to him.
I am using VB from Visual Studio 2005, and SQL Server 2005 Express as a backend, with Office 2003.
My SQL Server is set up with Mixed security.
I am trying to use the doc.MailMerge.OpenDataSource in my code to cause the work document to query the database when I open the document.

The code I am using is as Follows:

My objects are declared at the class level:

 
Protected appWord As Object
    Protected doc As Microsoft.Office.Interop.Word.Document
    Protected bFormClosed As Boolean = True
    Protected SqlConnectionString = My.Settings.MountainRailMarketingConnectionString
    Protected connectionString As String = SqlConnectionString
    Protected connection As New SqlConnection(connectionString)
    Protected sql As String
    Protected cmd As New SqlCommand(sql, connection)
    Protected daMarketingClient As New SqlDataAdapter(cmd)
    Protected dsMarketingClient As New DataSet
    Protected pRow As DataRow
    Private myNull = System.DBNull.Value
 
and my code is as follows:
 
Private Sub btnMailingLabels_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMailingLabels.Click
        '---------------------------------------------------------------------------------------------------------------------------
        '   Source:     btnMailingLabels_Click
        '
        '   Change Log Modification    
        '
        '   Date        Author        SCN        Description
        '   ----        ------      --------    ------------------------------------------------------------------------------------
        '   5/12/2007   djd                     Initial form
        '---------------------------------------------------------------------------------------------------------------------------
 
 
        Try
            appWord.Visible = True
            OpenFileDialog1.Filter = "Text files (*.DOC)|*.DOC"
            OpenFileDialog1.ShowDialog() 'display Open dialog box
            doc = appWord.Documents.Open(OpenFileDialog1.FileName)
            doc.MailMerge.OpenDataSource("C:\Documents and Settings\Don\My Documents\My Data Sources\Durbin Greenbrier Marketing List\localhost_sqlexpress MountainRailMarketing ClientList.odc", _
            Connection:="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=DurbinMarketingUser;Password=letmein!@#;Initial Catalog=MountainRailMarketing;Data Source=localhost\sqlexpress;", _
            SQLStatement:="select * from ""ClientList"" WHERE cltPrintFlag = 0", _
            SubType:=Word.WdMergeSubType.wdMergeSubTypeOther)
            appWord.Documents(1).Activate()
            doc.Activate()
            bFormClosed = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
 
    End Sub
 
 

When I try to execute this, I get presented with a SQL Server Login window
with the login information in place, when I try to "test the connection" I get a message Test connection failed because of an error in the initializing providor. Login failed for DurbinMarketingUser.
And I get a message that Windows was unable to open the datasource.

I would appreciate any help that you might be able to give.

Thank You

Hello diston52!

Looks like you're almost there, assuming you have the same problem I had a couple months ago. You should configure the connection string to reflect the datasource being used. The code may be looking for a source and cannot find it.

Again, provided you're having a similar issue:

(1) Hit that wonderful button near the provider info textbox
(2) Test your connection again

Also, what extension is .odc?

Good luck!

Dököll

[truptidalia]

Hi Don,

I am looking for working on Mail Merge from my VB.Net aplication. I use MS Access as my DB. I have saved templates in rtf format, have dataset to whom I have to send letters. But, I can't figure out how to perform thhe further steps to complete Mail Merge. It would be nice of you, if you can give me some idea on how to move ahead or proceed to perform Mail Merge from my application. I believe you can help to some extend as you have already worked on it. I am looking for any guidance from days, but couldn't get started yet.

I would appreciate, if your solution can be a good start up for my problem. Please help me or guide me atleast.

Thanks