Word Mailmerge

I'm creating a Word Data Document from VB for a mailmerge.  My problem is that I have over 30 fields and I get an automation error on the following line that it is longer than 255 chars.

wrdDoc.MailMerge.CreateDataSource Name:="C:\Datadoc.doc", HeaderRecord:="Firstname, LastName, and the rest ...."

I can't use short field names - I must keep the original fieldnames.  I tried using temporary short names and then trying to modify the FieldNames and/or DataFields properties but I get an error.  It appears that you cannot modify these properties in runtime.
emezeiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
inthedarkConnect With a Mentor Commented:
You could try creating a document without using the mail merge. Print your database fields into the document. You can then have any number of fields you like.

In the following example a pipe delimited file contains your address fields. But you can open your data using other methods too.  You may need some help with this.

You will end up with a very felixble solutuion.

Best of luck, inthedark.


Untested Example:

Dim WordApp As Word.Application
Dim Doc As Word.Document
Dim DSel As Word.Selection
Dim Range As Word.Range
Dim datafile As Long
Dim fc As Integer ' field count
Dim wrd
ReDim flds(0)


' create an instance to word
Set WordApp = New Word.Application

' give word some time
DoEvents
DoEvents
DoEvents
DoEvents

' show word (optional)
WordApp.Visible = True
DoEvents

' create a new document
Set Doc = WordApp.Documents.Add ' can set a template ("C:\templates\yourtemplate.dot", False)

' but its best to setup a template and open like:
'Set Doc = WordApp.Documents.Add("C:\templates\yourtemplate.dot", False)

' create a link to the current typing area (known as the selection)
Set DSel = WordApp.Selection

' open your data file
' theformatof your data is another subject but here is an example using a pipe delimter:

delimiter="|"


datafile = FreeFile
Open "C:\MyData.txt" For Input As #datafile

Dim DataLine$

Do While Not EOF(datafile)
   Line Input #datafile, DataLine$
   
   ' turn the single line into an array of fields
   flds = Split(DataLine, Delimiter)
       
   ' The name and address are in fields
   ' fld(0), fld(1), etc.

   GoSub CreatePage
Loop

Close datafile

' save the document
Doc.SaveAs "C:\windows\Temp\test.doc"
   
WordApp.Quit
Set DSel = Nothing
Set Range = Nothing

MsgBox "Done"

Exit Sub

CreatePage:


' setup a style
DSel.Style = Doc.Styles("Normal")

' put the addresses into the page

For fc = 0 To UBound(flds)
   DSel.TypeText flds(fc) + vbCrLf
Next fc

' movedown

DSel.TypeText vbCrLf
DSel.TypeText vbCrLf
DSel.TypeText vbCrLf

DSel.TypeText "Dear Chummy," ' or even "Dear " + fdl(25) +","

DSel.TypeText vbCrLf

' type some words
DSel.TypeText "Mary had a little lamb" + vbCrLf

' now close of the data this record
' now move to the next page

' Now go to end of document
DSel.Collapse Direction:=wdCollapseEnd '0

' insert a pagebreak
DSel.InsertBreak wdPageBreak

Return
0
 
devthaCommented:
Just declare
ReDim flds(0) as string and Delimiter as string and this works....I tested this.

0
 
emezeiAuthor Commented:
Thanks for your response.  This works but I can not use it.  We have to use mailmerge because this need is based on hundreds of existing mailmerge templates utilizing these 30+ fields.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
inthedarkCommented:
Acutally now I think of it I have seen a similar problem. What version of Word and service packs are you using?

0
 
inthedarkCommented:
You can use a templaple.

You can open your template scan the document and replace your tags with the required fields. Then print it page by page if you wish.




0
 
emezeiAuthor Commented:
inthedark,

I'm not at work today,  but I believe we are using 97 - definately not 2000.  Word has a similar restriction of 255 chars on the '.OpenDataSource SQLStatement:= ...' but there they have a 'SQLStatement1:= ...' as well, so you can have 2 * 255 chars.  
Thanks again.
0
 
emezeiAuthor Commented:
See MS Site - Q298418 - PRB: The HeaderRecord String for Word's CreateDataSource Method is Limited in Size to 255 Characters
0
 
emezeiAuthor Commented:
inthedark,

I'm going to give you the points because your code worked and you put in the extra effort of writing a complete example.

For those that payed points for "Questions already asked" -I played around with the original idea of originally setting up with short names to meet the 255 limit than I did the following in runtime which worked.


Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document  '-- FOR TEMPLATE DOC
Dim wrdDataDoc  As Word.Document  '-- FOR DATA DOC

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
   
'--- NEW DOC FOR GENERATING TEMPLATE
Set wrdDoc = wrdApp.Documents.Add
   

'--- CREATE DATA SOURCE WITH SHORT NAMES
wrdDoc.MailMerge.CreateDataSource Name:="C:\Datadoc.doc", HeaderRecord:="FN, LN, and the rest ...."
 
'--- THIS HERE IS WHAT DID IT  
wrdDoc.MailMerge.EditDataSource

'--- OPEN FILE TO INSERT DATA
Set wrdDataDoc = wrdApp.Documents.Open("C:\DataDoc.doc")

'--- MODIFY THE FIELD NAMES
wrdDataDoc.Tables(1).Cell(1, 1).Range.Text = "FirstName"
wrdDataDoc.Tables(1).Cell(1, 2).Range.Text = "LastName"
wrdDataDoc.Tables(1).Cell(1, 3).Range.Text = "Address"
....
....
   
' Fill in the data
0
All Courses

From novice to tech pro — start learning today.