how to retrieve mailmerge field name using vb.net

S

srid

i am writing a vb.net application. what i want to do is:
1)get the name of mailmerge field name from a word template
2)assign values to them
for example one of my template has following merge fields:
«First»«Last»
«Address»

Dear «Last»
3)if user selects 10 records, the application should be able to create ten
letters from the template

i have been looking for code/reference/advice for last three days.no
luck(google search and msdn search).any help would be appreciated

what is this mean:
mMergeField.Code.Tex
how to use this to assign value:
ActiveDocument.MailMerge.Fields

thanks
 
P

Peter Jamieson

First, if you need to "roll your own" merge, be aware that Mailmerge
actually does quite a lot of stuff that is non-trivial and not easy to code.
If you are creating all the templates, that's one thing. If you are still
allowing users to create templates,
a. it is highly unlikely that your own code will behave the same way as
Microsoft's. That may confuse some users
b. unless you constrain users to a very simple use of MERGEFIELD and other
field types, the chances are that they will create templates that your code
will not proces correctly or sensibly unless you put a greate deal of effort
in. For example, evaluating nested fields is, in the general case,
non-trivial. It isn't just a case of writing a recursive algorithm as some
programmers might imagine when they first approch the problem.

So it may be better to try to use Word's own mailmerge feature if possible,
even if it means that you have to write the selected data to a data source
that Word can use before doing so. NB, you can't use disconnected recordsets
as a data source, and you can't subclass the DataSource object to define
your own methods for reading data source data.

I can't point you to VB.NET examples, but it shouldn't be hard to convert
VBA samples to VB.NET. So for example, this is a way to cycle through the
fields in a document - detecting /all/ fields in a document is
non-trivial (in fact, evidence suggests that it may not be possible) but the
following code should catch most of them:

---------------------------------------------
Dim objStory As Range
Dim objField As Field

For Each objStory In ActiveDocument.StoryRanges
For Each objField In objStory.Fields
' do what you want to the field here. You can test objField.Type
' to process specific field types
Next
' The header/footer ranges can be linked in a way
' that is not revealed by the outer For Each
' so we have to do the following
While Not (objStory.NextStoryRange Is Nothing)
Set objStory = objStory.NextStoryRange
For Each objField In objStory.Fields
' do what you want to the field here
Next
Wend
Next objStory

Set objStory = Nothing
Set objField = Nothing
---------------------------------------------

Where the above code says "' do what you want to the field here", you
presumably want to insert some data from your data record. To do that, you
would need the name of the field (column) you want to get the data from,
e.g. (this will probably be significantly different in VB.NET)

Dim strMergeFieldCode
Dim strMergeFieldName

If objField.Type = wdFieldMergeField Then
strFieldCode = Split(objField.Code)
strFieldName = strFieldCode(2)
If Right(strFieldName, 1) = Chr(34) Then
strFieldName = Left(strFieldName, Len(strFieldName) - 1)
End If
If Left(strFieldName, 1) = Chr(34) Then
strFieldName = Right(strFieldName, Len(strFieldName) - 1)
End If
End If.

You probably then need to insert the value of your data column. Let's
suppose you have a function myvalue(strFieldName) that returns that value.

One way to do it is

objField.Result.Text = myvalue(strFieldName)

However, be aware that Word "wants" to assign its own results to fields. You
may find that as soon as the user does something like print preview, print
or save, Word updates the field results and destroys your results.

Another approach is to select the field and replace the selection by the
text you want, e.g.

objField.Select
Selection.Range.Text = myvalue(strFieldName)

but that destroys the field, so you would probably need to make a copy of
the Mail Merge main document before using it.

MVP Cindy Meister used to have one or two articles about this kind of thing,
but I do not think they are still available on the web - I'm thinking of the
article

"Beyond Mail Merge: Alternatives to Word's Built-in Feature"

that she references in the "List of my publications" link (see
http://homepage.swissonline.ch/cindymeister/ )
what is this mean:
mMergeField.Code.Tex

It should be clear from the above example what this is.
how to use this to assign value:
ActiveDocument.MailMerge.Fields

I think the above examples show you what you will need.

Peter Jamieson
 
L

Lüko Willms

Am Tue, 24 Oct 2006 04:07:03 UTC, schrieb srid
i have been looking for code/reference/advice for last three days.no
luck(google search and msdn search).any help would be appreciated

I found this article from a dBase expert very helpful for my first
steps:

<http://www.dbase.com/Knowledgebase/adv/activex/WordOlde/word.htm>

Titled "MS Word Mail-Merge.how", authored by Gary White, dBVIPS

BTW, the assignment of values to the mail-merge fields during the
mail-merge process is done by the Mailmerge-Engine of Word itself. No
need to do individual programming for that, especially when, as you
wrote, the template being used already does have the necessary
mail-merge fields defined.


Yours,
 
S

srid

Hi Peter and Luko,

thanks for the valuable info. I will give it a try . when i write the code
take your advice into consideration.

thanks
srid
 
L

Lüko Willms

Am Tue, 24 Oct 2006 13:34:02 UTC, schrieb srid
References: <[email protected]> <[email protected]>
Subject: Re: how to retrieve mailmerge field name using vb.net
Date: Tue, 24 Oct 2006 06:34:02 -0700
Hi Peter and Luko,

thanks for the valuable info. I will give it a try . when i write the code

No thanks -- I just wonder, why can I read your reply to mine, but
not my message which you are replying to?


Yours,
L.W.
 
L

Lüko Willms

I just wonder, why can I read your reply to mine, but
not my message which you are replying to?

It must have been just a wrong command to my newsreader...
I see the article now.

L.W.
 
S

srid

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,Country,SalesAssociate from
contactpipe"
objCommand = New SqlClient.SqlCommand(strSQL, objConn)
myreader =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)

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").ToString())
ElseIf mWord.Selection.Range.Text = "«" & "LastName" &
"»" Then

mWord.Selection.TypeText(myreader("LastName").ToString())
ElseIf mWord.Selection.Range.Text = "«" & "Street" & "»"
Then
mWord.Selection.TypeText(myreader("Addr1").ToString())
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").ToString())
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").ToString())
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,Country,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
 
S

srid

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
 
P

Peter Jamieson

1)

yes, that's why I said at the beginning of this thread that it's not easy to
get this right. In other words, you have to solve /all/ the problems. In my
opinion, a good designer/programmer should either be able to solve each
probem they come across and avoid taking routes where they get stuck.

If I went down that route I would consider something like
a. saving a copy of the mailmerge main document
b. creating an output document
c. for each record in the data source
- retrieve the copy
- make sure my view of the document is set up so I can process its
fields (e.g., if you want to look for MERGFIELD fields by looking for
chevrons <<, you can't be in preview view, where you see result data, or
"field code" view, where you see { MERGEFIELD "mergefield name" }
- evaluate the fields
- copy everything except possibly the final paragraph mark and append
it to the output document
d. close everything
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

When it connects to an ODBC data source, Word OpenDataSource always
references an ODBC DSN of some kind. You can't use a DSN-less connection,
e.g. specifying Driver={whatever}, as you can in some other applications.

However, anything in the existing DSN can be overridden by an item in the
COnnection string, /except/ the driver name. So if you know you have a DSN
called Connect_To_DB that uses the SQl Server driver, and specify

Connection:="DSN=Connect_To_DB;Data Source=servername;initial
catalog=dbname;Integrated security=True;"

it should work. Or you can use a file dsn. But you have to have /a/ DSN.
It's an irritating limitation of Word. The same situation is true if you
want to use an OLEDB connection: for SQL Server you have to have either a
..udl file or a .odc file. You can't just specify an OLEDB connection string
(even though you have to specify the provider anyway).
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)

If there is a header Source, the user should be able to select from the list
of coumns in the Header Source (header Sources are not supported in the User
Interface in Word 2002 and later, and you may find they do not work the way
they were originally supposed to). If you are going to use a header Source,
I'd make it a Word document.

If you don't have a header source, the user will see the list of columns
returned by the SELECT used to create the data source. if there is no data
source, they see no list of columns. If you do not want to connect to the
real data source, you could connect to another data source with identical
column names (it is the field names that Word uses that really count)
temporarily, then open the real data source when you need it.

Peter Jamieson
 
D

Doug Robbins - Word MVP

If you want to save each document created by the mailmerge to a separate
file with the name of the file taken from a field in the data source, see
the "Individual Merge Letters" item on fellow MVP Graham Mayor's website at:

http://www.gmayor.com/individual_merge_letters.htm

If you are using Word XP or later, the "Add-in to Merge Letters to Separate
Files" that I have written and that can be downloaded from that site will
allow you to create each letter as a separate file with a filename taken
from a field in the data source with a minimum of fuss.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
S

srid

Hi peter,
Thanks for the detatiled info. I will give it a try. I am in the process of
rewriting an old application. The old system has so many mailmerge
features.currently we are using word 2003 and vs 2005.

Hi Doug,
Thanks for the info. I am doing more reading these days. The old templates
fields are marked with {{name}}. I have to change them to «name» (word 2003).
Mailmerge is new for me.
 
S

srid

thanks for the info/advice

Peter Jamieson said:
1)

yes, that's why I said at the beginning of this thread that it's not easy to
get this right. In other words, you have to solve /all/ the problems. In my
opinion, a good designer/programmer should either be able to solve each
probem they come across and avoid taking routes where they get stuck.

If I went down that route I would consider something like
a. saving a copy of the mailmerge main document
b. creating an output document
c. for each record in the data source
- retrieve the copy
- make sure my view of the document is set up so I can process its
fields (e.g., if you want to look for MERGFIELD fields by looking for
chevrons <<, you can't be in preview view, where you see result data, or
"field code" view, where you see { MERGEFIELD "mergefield name" }
- evaluate the fields
- copy everything except possibly the final paragraph mark and append
it to the output document
d. close everything


When it connects to an ODBC data source, Word OpenDataSource always
references an ODBC DSN of some kind. You can't use a DSN-less connection,
e.g. specifying Driver={whatever}, as you can in some other applications.

However, anything in the existing DSN can be overridden by an item in the
COnnection string, /except/ the driver name. So if you know you have a DSN
called Connect_To_DB that uses the SQl Server driver, and specify

Connection:="DSN=Connect_To_DB;Data Source=servername;initial
catalog=dbname;Integrated security=True;"

it should work. Or you can use a file dsn. But you have to have /a/ DSN.
It's an irritating limitation of Word. The same situation is true if you
want to use an OLEDB connection: for SQL Server you have to have either a
..udl file or a .odc file. You can't just specify an OLEDB connection string
(even though you have to specify the provider anyway).


If there is a header Source, the user should be able to select from the list
of coumns in the Header Source (header Sources are not supported in the User
Interface in Word 2002 and later, and you may find they do not work the way
they were originally supposed to). If you are going to use a header Source,
I'd make it a Word document.

If you don't have a header source, the user will see the list of columns
returned by the SELECT used to create the data source. if there is no data
source, they see no list of columns. If you do not want to connect to the
real data source, you could connect to another data source with identical
column names (it is the field names that Word uses that really count)
temporarily, then open the real data source when you need it.

Peter Jamieson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top