Need help on

[momsaidnogf]

Hi Guys,
I need some help on VB console app, and iam new to VB. Here we go, the project , I have a word template, where iam gonna perform a mail merge and send it to the printer. This merge process have to created like a batch or exe file, which will be scheduled to run accordingly. So i started creating a console app using VB. This console should connect to the SQL database and produce me some filtered rows based upon my SQL query. Now i need to create an array for the data rows which i received from the SQL query and save it as a excel or odc format. So that i can use this excel or odc file as my word template data source for mail merge.

Following is the code i did so far to retrieve the data rows from my SQL query.

Imports System.Data.SqlClient
Imports System.Console
Imports System.NullReferenceException
Imports Microsoft.Office.Interop
Imports System
Imports System.Data

Module Module1

    Sub Main()

        Dim sConnectionString As String = "Server= ServerName; Database = DbName;" & "Integrated Security = SSPI"

        Dim Connection As New SqlConnection()
        Connection.ConnectionString = sConnectionString
        Connection.Open()
       
 Dim Dastudent As New SqlDataAdapter("SELECT student.last_name, student.first_name, student.ssn, student.address1_line1, student.address1_line2, student.city1, student.state1, student.zip_code1, student.Graduation_date, awarddata.status_code FROM student INNER JOIN awarddata ON student.person_id = awarddata.person_id WHERE (awarddata.status_code = 'O') and (student.Graduation_date >= '2010/05/01') order by first_name", sConnectionString)

        Dim Dsstudent As New DataSet("Student")
        Dastudent.FillSchema(Dsstudent, SchemaType.Source, "Student")
        Dastudent.Fill(Dsstudent, "Student")

        Dim tblstudent As DataTable
        tblstudent = Dsstudent.Tables("Student")

        Dim drCurrent As DataRow
        For Each drCurrent In tblstudent.Rows
            Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8}", _
                drCurrent("first_name").ToString, _
                drCurrent("last_name").ToString, _
                drCurrent("ssn").ToString, _
                drCurrent("address1_line1").ToString, _
                drCurrent("address1_line2").ToString, _
                drCurrent("state1").ToString, _
                drCurrent("city1").ToString, _
                drCurrent("zip_code1").ToString, _
                drCurrent("Graduation_date").ToString)
        Next
        Console.ReadLine()
        Connection.Close()
    End Sub

End Module

As a result it displays all the data row on the exe. I want the the displayed list to be saved in a excel format , so that i can use the excel as my word recipient list and populate the word template and send it to printer.

The flow looks like touching my nose keeping a fat girl in the middle. but thats the best i came up with.

Iam using mail merge because , iam generating form letters to be populated. So for sure, i will apreciate if anyone can help me.

You guys rok..

[momsaidnogf]

As mentioned above, i made some code changes to my app and now i can store the sql results as a excel document. Now i have to write the code to start word document and use my abc.dot template to perform mail merge and send it to my network printer.

I tried searching on net, i did some coding work too, able to create the exact code , but the word never opens up. dunno why, here is my code. Please help me.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core

Module Module1
    Public conn As New SqlConnection
    Public Filename As String
    Public chkexcel As Boolean
    Public oexcel As Excel.Application
    Public obook As Excel.Workbook
    Public osheet As Excel.Worksheet
    Public R As Integer
    Dim WithEvents oApp As Word.Application

    Sub Main()
        Try
            Dbopen()
            'File name and path, here i used abc file to be stored in Bin directory in the sloution directory
            Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
            'check if file already exists then delete it to create a new file
            If File.Exists(Filename) Then
                File.Delete(Filename)
            End If
            If Not File.Exists(Filename) Then
                chkexcel = False
                'create new excel application
                oexcel = CreateObject("Excel.Application")
                'add a new workbook
                obook = oexcel.Workbooks.Add
                'set the application alerts not to be displayed for confirmation
                oexcel.Application.DisplayAlerts = True
                'check total sheets in workbook
                Dim S As Integer = oexcel.Application.Sheets.Count()
                'leaving first sheet delete all the remaining sheets
                If S > 1 Then
                    oexcel.Application.DisplayAlerts = False
                    Dim J As Integer = S
                    Do While J > 1
                        oexcel.Application.Sheets(J).delete()
                        J = oexcel.Application.Sheets.Count()
                    Loop
                End If
                'to check the session of excel application
                chkexcel = True


                oexcel.Visible = True
                'this procedure populate the sheet
                Generate_Sheet()
                'save excel file
                obook.SaveAs(Filename)
                'end application object and session
                osheet = Nothing
                oexcel.Application.DisplayAlerts = False
                obook.Close()
                oexcel.Application.DisplayAlerts = True
                obook = Nothing
                oexcel.Quit()
                oexcel = Nothing
                chkexcel = False
                'mail excel file as an attachment
                automail("dinesh.kumar@isac.illinois.gov", "Auto Excel File", "any message", Filename)
            End If
        Catch ex As Exception
            'mail error message
            automail("err.mail@somedomain.com", "Error Message", ex.Message, "")
        Finally
            Dbclose()
        End Try
    End Sub
    Public Sub automail(ByVal mail_to As String, ByVal subject As String, ByVal msg As String, ByVal filename As String)
        Dim myOutlook As New Outlook.Application()
        Dim myMailItem, attach As Object
        myMailItem = myOutlook.CreateItem(Outlook.OlItemType.olMailItem)
        myMailItem.Body = msg
        If File.Exists(filename) Then
            attach = myMailItem.Attachments
            attach.Add(filename)
        End If
        If Trim(mail_to) <> "" Then
            myMailItem.to = Trim(mail_to)
        End If
        myMailItem.SUBJECT = subject
        myMailItem.send()
        myMailItem = Nothing
        myOutlook = Nothing
    End Sub
    Public Sub Dbopen()
        'open connection for db.mdb stroed in the base directory
        conn.ConnectionString = "Server= isacsqlsrvrqa; Database = TprogQA;" & "Integrated Security = SSPI"
        conn.Open()
    End Sub
    Public Sub Dbclose()
        'check and close db connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
            conn.Dispose()
            conn = Nothing
        End If
        'check and close excel application
        If chkexcel = True Then
            osheet = Nothing
            oexcel.Application.DisplayAlerts = False
            obook.Close()
            oexcel.Application.DisplayAlerts = True
            obook = Nothing
            oexcel.Quit()
            oexcel = Nothing
        End If
        End
    End Sub
    Sub Generate_Sheet()
        Console.WriteLine("Generating Auto Report")
        osheet = oexcel.Worksheets(1)
        'format headings
        osheet.Range("A1:I1").Font.Color = RGB(255, 255, 255)
        osheet.Range("A1:I1").Interior.ColorIndex = 5
        osheet.Range("A1:I1").Font.Bold = True
        osheet.Range("A1:I1").Font.Size = 10
        'columns heading
        osheet.Range("A1").Value = "last_name"
        osheet.Range("A1").BorderAround(8)
        osheet.Range("B1").Value = "first_name"
        osheet.Range("B1").BorderAround(8)
        osheet.Range("C1").Value = "ssn"
        osheet.Range("C1").BorderAround(8)
        osheet.Range("D1").Value = "address1_line1"
        osheet.Range("D1").BorderAround(8)
        osheet.Range("E1").Value = "address1_line2"
        osheet.Range("E1").BorderAround(8)
        osheet.Range("F1").Value = "city1"
        osheet.Range("F1").BorderAround(8)
        osheet.Range("G1").Value = "state1"
        osheet.Range("G1").BorderAround(8)
        osheet.Range("H1").Value = "zip_code1"
        osheet.Range("H1").BorderAround(8)
        osheet.Range("I1").Value = "Graduation_date"
        osheet.Range("I1").BorderAround(8)

        'populate data from DB
        Dim SQlQuery As String = "SELECT student.last_name, student.first_name, student.ssn, student.address1_line1, student.address1_line2, student.city1, student.state1, student.zip_code1, student.Graduation_date, awarddata.status_code FROM student INNER JOIN awarddata ON student.person_id = awarddata.person_id WHERE (awarddata.status_code = 'O') and (student.Graduation_date >= '2010/05/01')"
        Dim SQLCommand As New SqlCommand(SQlQuery, conn)
        Dim SQlReader As SqlDataReader = SQLCommand.ExecuteReader
        Dim R As Integer = 1
        While SQlReader.Read
            R = R + 1
            osheet.Range("A" & R).Value = SQlReader.GetValue(0).ToString
            osheet.Range("A" & R).BorderAround(8)
            osheet.Range("B" & R).Value = SQlReader.GetValue(1).ToString
            osheet.Range("B" & R).BorderAround(8)
            osheet.Range("C" & R).Value = SQlReader.GetValue(2).ToString
            osheet.Range("C" & R).BorderAround(8)
            osheet.Range("D" & R).Value = SQlReader.GetValue(3).ToString
            osheet.Range("D" & R).BorderAround(8)
            osheet.Range("E" & R).Value = SQlReader.GetValue(4).ToString
            osheet.Range("E" & R).BorderAround(8)
            osheet.Range("F" & R).Value = SQlReader.GetValue(5).ToString
            osheet.Range("F" & R).BorderAround(8)
            osheet.Range("G" & R).Value = SQlReader.GetValue(6).ToString
            osheet.Range("G" & R).BorderAround(8)
            osheet.Range("H" & R).Value = SQlReader.GetValue(7).ToString
            osheet.Range("H" & R).BorderAround(8)
            osheet.Range("I" & R).Value = SQlReader.GetValue(8).ToString
            osheet.Range("I" & R).BorderAround(8)
        End While
        SQlReader.Close()
        SQlReader = Nothing     
    End Sub

Until here everything works fine and i get the result for what i coded. But after when i run the mergeit code, when i compile according to the code, it has to open the word doc, and mail merge. But instead result will extract to the excel and excel closes. but word never even opens.

Public Sub MergeIt()
        'Open the word application
        Dim oApp As Word.Application
        Dim oMainDoc As Word.Document
        'Opening the Word Template
        oApp = CreateObject("word.Application")
        oMainDoc = GetObject("C:\Documents and Settings\DKumar\My Documents\Downloads\Excel_Automation_src\bin\August_15.doc", "Word.Document")
        oMainDoc.Application.Visible = True
        'Perform mail merge
        With oMainDoc.MailMerge
            .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters

            'Set up the mail merge data source
            oMainDoc.MailMerge.OpenDataSource _
    (Name:="C:\Documents and Settings\DKumar\My Documents\Downloads\Excel_Automation_src\bin\abc.xls", _
    LinkToSource:=True, Connection:="Entire Spreadsheet", _
 SQLStatement:=("SELECT * FROM `Sheet1$`"))
            : oMainDoc.MailMerge.Execute()

        End With
        'Confirming the 
        With oMainDoc
            .MailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
            .MailMerge.Execute(Pause:=False)
        End With
    End Sub
    
    Public Sub oMainDoc_MailMergeAfterMerge(ByVal Doc As Word.Document, ByVal DocResult As Word.Document)

        'When the mail merge is complete, 1) make Word visible,
        '2) close the mail merge document leaving only the resulting document
        'open and 3) display a message.
        Doc.Close(False)
        oApp.Visible = True
        'I will add the message later
    End Sub
    Public Sub final(ByVal cancel As Integer)
        oApp = Nothing
    End Sub
End Module

[JBourgeois]

A lot of work, reading in SQL Server, transferring to Excel and then mail merge in Word.

Word is able to read it's Mail Merge information directly from a database, without any code. A table or a view can become the source for a Mail Merge. You do not need to translate everything to Excel. You could prepare everything in Word and simply open and print the document. You would save a lot of code, the thing would be faster and won't require as much maintenance when you move from one version of Office to another.

Unfortunately, showing you how to create a mail merge in Word would take too long and be out of context in this forum. Try to find a Word forum.

[Hack]

http://www.mailmergeguide.org/