Winword Mail Merge Using VB.net

[danasegarane]

This code helps to AutoMate WinWord Using Mail Merge

Main Method

Imports System.Data
Imports System.IO
Imports System.Reflection
Imports Microsoft.Office.Interop
'Mark a Reference to Microsoft word Object from Com Tab
Module Module1
 
    Sub Main()
 
        Dim sqlQry As String = "SELECT TOP 2 ContactName,ContactTitle,Address from customers"
        'Get the values from table
        Dim dttable As DataTable = DatabaseOperation.DatabaseOperation(sqlQry, DataBaseQueryType.Datatable)
        Dim sTemplateFileName As String = "DocumentTemplate.doc"
        'Word merging
        Dim moApp As Object
 
        moApp = CreateObject("Word.Application")
        If IsNothing(moApp) = False Then
            moApp.visible = False
            Dim sDocFileName As String
            For Each row As DataRow In dttable.Rows
                'Open the Document
                moApp.Documents.Open(Path.Combine(ApplicationPath, sTemplateFileName))
                sDocFileName = Convert.ToString(row(0)) & ".doc"
                Dim intCount As Integer = 0
                For Each MergeField As Word.MailMergeField In moApp.ActiveDocument.MailMerge.Fields
                    'Select the text
                    MergeField.Select()
                    'Type the text
                    moApp.Selection.TypeText(Convert.ToString(row(intCount)))
                    intCount += 1
                Next
                'Save the document
                moApp.ActiveDocument.SaveAs(Path.Combine(ApplicationPath, sDocFileName))
                'Close the document
                moApp.Documents.Close()
            Next
            'Dont save the template changes
            moApp.Quit(Word.WdSaveOptions.wdDoNotSaveChanges)
            'Dispose 
        End If
        moApp = Nothing
        dttable.Dispose()
        dttable = Nothing
 
    End Sub
 
    Private Function ApplicationPath() As String
        Return _
        Path.GetDirectoryName([Assembly].GetEntryAssembly().Location)
    End Function
 
End Module

Database Operations

Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.StringBuilder
Imports System.Configuration
Module DatabaseOperation
 
 
    Public Enum DataBaseQueryType
        Datatable = 1
 
    End Enum
 
 
 
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim sConstring As String
 
    Private Sub OpenConnection()
        Dim connectionString As String = _
        "Integrated Security=SSPI;Persist Security Info=False;" + _
        "Initial Catalog=Northwind;Data Source=localhost"
        con = New SqlConnection(connectionString)
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
    End Sub
 
    Private Sub CloseConnection()
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
    End Sub
 
    Public Function DatabaseOperation(ByVal qry As String, ByVal type As DataBaseQueryType) As Object
        Dim objDB As New Object
        Dim dt As DataTable
        Dim da As SqlDataAdapter
        If type = DataBaseQueryType.Datatable Then
            OpenConnection()
            Dim cmd As New SqlCommand(qry)
            cmd.Connection = con
            dt = New DataTable
            da = New SqlDataAdapter(cmd)
            da.Fill(dt)
            objDB = dt
            dt.Dispose()
            CloseConnection()
            Return objDB
        End If
 
        Return objDB
 
    End Function