View Single Post
  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
srid srid is offline
external usenet poster
 
Posts: 15
Default how to retrieve mailmerge field name using vb.net

thks for the info. I was able to do mailmerge using vb.net. i have found two
ways.both are working
-------
Imports Microsoft.Office.Interop

'method 1
Private Sub MailMerge_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click

'open the word template using openfiledialog box
OpenFileDialog1.InitialDirectory =
My.Computer.FileSystem.SpecialDirectories.Desktop & "\word Template"
'By default .dot file
OpenFileDialog1.Filter = "Template Files (*.dot)|*.dot|All Files
(*.*)|*.*"
OpenFileDialog1.Title = "Open a Word Template file--- *.dot"

'if file is selected
If Me.OpenFileDialog1.ShowDialog =
System.Windows.Forms.DialogResult.OK Then

Dim m_FileName As String
'open word application
Dim mWord As New Word.ApplicationClass
'open word document
Dim mWordDoc As New Word.Document
'create mailmerge fields
Dim mMergeField As Word.MailMergeField
'assign the path value
m_FileName = OpenFileDialog1.FileName

'Dim wordTemplateName As Object
Dim wordTemplateName As String
'assign word template name
wordTemplateName = m_FileName


' Dim destinationFileName As Object
Dim destinationFileName As String
'word is not visible
mWord.Visible = False

Dim i As Integer = 1

'create sql connection
Dim objConn As SqlClient.SqlConnection
Dim ds As New DataSet
Dim m_strConnection As String = "Data Source=ServerName;initial
catalog=databasename;Integrated security=true;"

objConn = New SqlClient.SqlConnection
objConn.ConnectionString = m_strConnection
objConn.Open()

'to pass query
Dim objCommand As SqlClient.SqlCommand
Dim strSQL As String
'read the data
Dim myreader As SqlClient.SqlDataReader

'select onlt top 10 rows for testing purpose
strSQL = "select top 10
FirstName,LastName,Dear,Addr1,City,State,Zip,Count ry,SalesAssociate from
contactpipe"
objCommand = New SqlClient.SqlCommand(strSQL, objConn)
myreader =
objCommand.ExecuteReader(CommandBehavior.CloseConn ection)

Dim num As Integer = 0

'--------------------------------
'read row by row
While myreader.Read()
'counter
num = num + 1
'create a new document
mWordDoc = mWord.Documents.Add(wordTemplateName, , , )

'loop thru word merge fields and assign values
For Each mMergeField In mWordDoc.MailMerge.Fields

mMergeField.Select()
'get the merge field name with «name» ; »this sign
copied and pasted from word
''MsgBox((CStr(CInt(AscW("«"c)))))---187,171
If mWord.Selection.Range.Text = "«" & "FirstName" & "»"
Then

mWord.Selection.TypeText(myreader("FirstName").ToS tring())
ElseIf mWord.Selection.Range.Text = "«" & "LastName" &
"»" Then

mWord.Selection.TypeText(myreader("LastName").ToSt ring())
ElseIf mWord.Selection.Range.Text = "«" & "Street" & "»"
Then
mWord.Selection.TypeText(myreader("Addr1").ToStrin g())
ElseIf mWord.Selection.Range.Text = "«" & "City" & "»"
Then
mWord.Selection.TypeText(myreader("City").ToString ())
ElseIf mWord.Selection.Range.Text = "«" & "State" & "»"
Then
mWord.Selection.TypeText(myreader("State").ToStrin g())
ElseIf mWord.Selection.Range.Text = "«" & "PostalCode" &
"»" Then
mWord.Selection.TypeText(myreader("Zip").ToString( ))
ElseIf mWord.Selection.Range.Text = "«" & "Country" &
"»" Then

mWord.Selection.TypeText(myreader("Country").ToStr ing())
ElseIf mWord.Selection.Range.Text = "«" & "Date" & "»"
Then
mWord.Selection.TypeText(Today.ToShortDateString)
ElseIf mWord.Selection.Range.Text = "«" & "Dear" & "»"
Then
mWord.Selection.TypeText(myreader("Dear").ToString ())
ElseIf mWord.Selection.Range.Text = "«" & "Sender" & "»"
Then

mWord.Selection.TypeText(myreader("SalesAssociate" ).ToString())
Else
mWord.Selection.TypeText("No_data_found")
End If

Next mMergeField

''save the template as document ;todo check for same file
name before save the doc if found append some character
''to do:send to printer or email,dispaly the document
destinationFileName = "C:\word Template\" &
myreader("FirstName").ToString() & num & ".doc"
mWordDoc.SaveAs(destinationFileName)

End While

'--------------------------------
'clear the objects from memory
myreader.Close()
objConn.Close()
myreader = Nothing
objCommand = Nothing

mWord.Quit()
mWord = Nothing
mWordDoc = Nothing
mMergeField = Nothing

End If
================================================== ================
'method 2
Private Sub MailMerge_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button6.Click

'initialize word
oApp = CreateObject("Word.Application")
'create word document
Dim oMainDoc As Word.Document

'Start a new main document for mail merge ;assign the word template
path
oMainDoc = oApp.Documents.Add("C:\word Template\MyTemplate.dot")

With oMainDoc.MailMerge
'mail document type
.MainDocumentType = WdMailMergeMainDocType.wdFormLetters
'sql statement
Dim strSQL As String = "select top 10
FirstName,LastName,Dear,Addr1,City,State,Zip,Count ry,SalesAssociate from
contactpipe"
'pass the DSN info;"DSN=Connect_To_DB"--already created using
ODBC
.OpenDataSource(Name:="", Connection:="DSN=Connect_To_DB",
SQLStatement:=strSQL)
End With

'Perform the mail merge to a new document.
With oMainDoc
.MailMerge.Destination =
WdMailMergeDestination.wdSendToNewDocument
.MailMerge.Execute(Pause:=False)
End With

'close the template
oMainDoc.Saved = True

oMainDoc.Close(False)
oApp.Visible = True
MessageBox.Show("Mail Merge Complete: " & oApp.ActiveDocument.Name,
"Mail Merge", MessageBoxButtons.OK, MessageBoxIcon.Information,
MessageBoxDefaultButton.Button3, MessageBoxOptions.DefaultDesktopOnly)

End Sub
===============
Questions:
1)in method 1 ,i am saving each merged record as a file
a)How to show all the merged records in one document with new pages or read
the saved files and put them in one word document. Each file info should be
in a new page?
2)in method 2,How to replace DSN info wth slq server connection string
like "Data Source=servername;initial catalog=dbname;Integrated security=true;"
or the provider name. I am using sql server 2000
3)if I give the user an option to create a new template;
a)how to restrict the merge field values:what i mean is user only able to
see the mergefields that i created(in the dropdown box)

thanks in advance