Mail Merge: MS Word Cutting off Text

[Ray Stantz]

Good day all:

I have a form letter generator setup in my database and when filled in and i click an email button, it transfers the text of the letter to a MS word template i have setup with various fields encoded. The text appears in its appropriate field after the merge but i'm noticing MS Word is cutting off at least half of my text. Is there a limit on the characters that can be merged? I have the field within the database set to memo and the field in the template is set to Unlimited so my thought is whatever i type in that memo field would appear in the word document because it should be reading and merging whatever i type, is this not the case?

Please let me know if you need to see any code to get a better picture,

Thanks,

Ray....

 

[Moniker]

As a quick guess, is the textbox on the form letter in Word set to "CanGrow" and "CanShrink"? I'm not positive those are even options in Word, but I think they are.

Beyond that, at what point does the template start cutting off? Does it handle just a sentence or two fine, and then "cut off" things when it gets to a few paragraphs, or does it cut off everything, regardless of the length?

 

[GaryPanic]

can you count the cut off point -is it around 255 chars- then theres an issue with simple qrys seem to cut off at this point .


find the problem and the guys will probably give the answer

g

 

[Ray Stantz]

Thank you all for your replies.

Strangest thing, i did an experiment using three different letters i have loaded inthe database and they all cutoff at different places.

Letter 1 Cutoff: Words 38; Characters 212; Paragraphs 3
Official Numbers: Words 185; Characters 952; Paragraphs 6

Letter 2 Cutoff: Words 35; Characters 218; Paragraph 1
Official Numbers: Words 246; Characters 1442; Paragraphs 5

Letter 3 Cutoff: Words 37; Characters 215; Paragraph 1
Official Numbers: Word 107; Characters 605; Paragraphs 2

There are no options for Can Grow and Can Shrink in Word, there is only

Type (set to Regular Text)
Default Text (left blank)
Maximum Length (set to Unlimited)
Run macro- Entry (left blank) Exit (left blank)
Bookmark (name of the field from the database)
Fill-in Enabled (box checked)
Calculate on Exit (left blank)

Also, i looked inthe table/query and the memo field has the entire letter, no cutoffs. I orignally copied/pasted the text into the field in the databse but went back and manually typed the letter just in case there were some special characters the database or Word did not recognize so i'm at a lost.


Ray...

 

[Moniker]

All of those are cutting off at the 255 mark (253 for the first sample, 254 for the second, and 253 for the third). It's dropping because Word won't put out half a word, so that's why it's stopping just short of 255.

Anyway, how are you exporting this stuff? Are you using the built-in functions or are you attaching to Word directly? Something tells me that if you're using the built-in MailMerge functions, that's going to be the issue. You'll instead need to connect to the Word object and then write out your fields explicitly.

 

[Ray Stantz]

This is the code i have for the mail merge. I got the idea from an example on this forum. The Letter_Text field is what is used for the body of the email and where i having the issue.

Public Function MergetoWord()

On Error Resume Next

Dim WordObj As Word.Application
Dim TemplateChoice As String
Dim MyTo As String
Dim MySubj As String

Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If


WordObj.Visible = True

Dim strMyTemplatePath As String
strMyTemplatePath = "G:\CMD\StandardTemplate.dot"

MyTo = Me.Email_Address
MySubj = "Message from the Department of Community Affairs"

WordObj.Documents.Add Template:=strMyTemplatePath, NewTemplate:=False
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="DateSent"
WordObj.Selection.TypeText [DateSent]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="To"
WordObj.Selection.TypeText [To]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Contractor"
WordObj.Selection.TypeText [Contractor]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Address"
WordObj.Selection.TypeText [Address]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="towho"
WordObj.Selection.TypeText [towho] & ":"


[B]WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Letter_Text"
WordObj.Selection.TypeText [letter].Column(2)[/B]


WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Signed"
WordObj.Selection.TypeText [Signed] & ","


WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="From"
WordObj.Selection.TypeText [Emp Name]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Title"
WordObj.Selection.TypeText [Title]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="carboncopy"
WordObj.Selection.TypeText "Cc:"

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="cc"
WordObj.Selection.TypeText [copy]

DoEvents
WordObj.Activate
WordObj.Selection.MoveDown wdLine, 20 ' Number = Number of Lines to move
Set WordObj = Nothing

DoCmd.Hourglass False

Exit Function

TemplateError:
Set WordObj = Nothing
Exit Function

End Function

 

[Moniker]

Whoa. That's an issue. What's this doing?

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Letter_Text"
WordObj.Selection.TypeText [letter].Column(2)

Is the letter part of a listbox or something? That sort of reference will only return 255 (or less) characters. You'll need to get the text in some other manner.

What is the structure where this letter is stored? Is it just a table with a memo field? How is it stored?

 

[Ray Stantz]

I attached a screenshot so you can see what i am seeing.

The form has a dropdown menu where the user will choose the letter from.

The dropdown is pulling from the 2nd cloumn of a table called Letters and that cloumn is the memo field with the text so that is why you see it setup that way. Is this causing the problem?

 

[Moniker]

Yes, that's the issue. Are the letter title (Compliance, non-compliance, etc.) in the same table with the letter text (what is currently being cut off)? If so, the solution will be easy. If not, how is one related to the other?

 

[Ray Stantz]

Yes,

The dropdown on the form has these properties

Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW id, [Letter Type], [letter text] FROM letters ORDER BY letters.[Letter Type];
Column Count: 3
Column Widths: 0";1.5";1"
Bound Column: 1

The Letter table looks like

Column 1: ID (Pk auto generated)
Column 2: Letter_Type (i.e. Non Compliance; Compliance etc.)
Column 3: Letter_Text (body text that is cutting off)

 

[Moniker]

Change that "Column(2)" line to be a DLookup and you should be okay.

Change this:

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Letter_Text"
WordObj.Selection.TypeText [letter].Column(2)

To this:

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Letter_Text"
WordObj.Selection.TypeText DLookUp("[Letter Text]","letters","id = " & YourComboBoxName)

 

[Ray Stantz]

Perfect!! Thank You!

 

[Moniker]

Not a problem.