Bugs automating mailmerge

[hals_left]

Hi, I am getting a couple of intermittent bugs using a mailmerge
template that has VBA behind.

I created a Word template that links to a data source which defines a
very small SQL View returning a single integer, over a VPN, just enough
to define the connection parameters username password etc.

1) As the user creates a new doc from the template, it promts to run
the SQL on some machines. Is there a way to prevent this prompt on
Windows 2000/XP ?

The user then presses Ctrl-> T to run the following Macro, which is in
Module1

Sub showDialog()
frmDialog.txtFocus.Text = ""
frmDialog.CheckBox1.Value = False
frmDialog.txtFocus.SetFocus
frmDialog.Show
End Sub

The user enters a record ID and the Button calls the code below to
bring a small set of data onto the client.


Private Sub cmdCreate_Click()
On Error Resume Next

Dim strSQL
If IsNumeric(txtFocus.Value) Then
frmDialog.Hide
strSQL = "SELECT * FROM ""MyView"" WHERE ""RecordID""=" &
txtFocus.Value
ActiveDocument.MailMerge.DataSource.QueryString = strSQL


If CheckBox1.Value = True Then
Dialogs(wdDialogMailMergeRecipients).Show
Else

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
If Err.Number <> 0 Then
MsgBox Err.Number & Err.Description, vbOKOnly,
"Mail Merge Error"
End If



End With
End If

Else
MsgBox ("Please enter a valid Record ID")
txtFocus.Text = ""
txtFocus.SetFocus
End If
End Sub



2) If I omit the line "Dialogs(wdDialogMailMergeRecipients).Show"
(Which was added as a workaround) the mailmerge fails saying "Invalid
Merge Field" for every record. Word is only identifying the fields in
the initial Small View, not the data that has been retrieved by the
user. But after cancelling all the warnings, If I select to view
"MailMerge Recipients" manually, it does show the data has been
retrieved.


3) When I run the "Merge to new document" the first page in the
mailmerge has no data. I can fix this by clicking "Last Record" and
then "First Record" before doing the mail merge.

4) In the template under "This document" I added this code. But I still
get prompted to save both the Template and the Letters produced, is
there a way to prevent prompts to save both docs?

Private Sub Document_New()
Application.DisplayAlerts = wdAlertsNone
ActiveDocument.Saved = True
End Sub



Users have Windows XP Pro 2002 SP2 & Word 2002 (10.42.19) SP2

Any help on these issues appreciated
Thanks!
hals_left

 

[Cindy M -WordMVP-]

Hi Hals_left,

1) As the user creates a new doc from the template, it promts to run
the SQL on some machines. Is there a way to prevent this prompt on
Windows 2000/XP ?

See

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765
http://support.microsoft.com?kbid=825765

2) If I omit the line "Dialogs(wdDialogMailMergeRecipients).Show"
(Which was added as a workaround) the mailmerge fails saying "Invalid
Merge Field" for every record. Word is only identifying the fields in
the initial Small View, not the data that has been retrieved by the
user. But after cancelling all the warnings, If I select to view
"MailMerge Recipients" manually, it does show the data has been
retrieved.

Does the main merge document contain MergeFields that are NOT in the data
source? Are the field names coming across the connection defined with
exactly the same spelling as the names Word shows in the "Insert field"
dialog box? (If Word considers a field name "illegal" it will change it,
but on data connection, what it's seeing may not comply with the field
names already in the document, which will trigger the "invalid" stuff.
Sounds like this may be what's happening.)

this may be having an effect on (3), so let's try to get it straightened
out, first.

4) In the template under "This document" I added this code. But I still
get prompted to save both the Template and the Letters produced, is
there a way to prevent prompts to save both docs?

Private Sub Document_New()
Application.DisplayAlerts = wdAlertsNone
ActiveDocument.Saved = True
End Sub

When you execute a mail merge all the fields in the main merge document
will be updated. That "dirties" the main merge document, so setting the
Saved property on open (or new) won't do any good. Note that setting
ActiveDocument.Saved won't affect the template from which the document is
being generated, either. ThisDocument.Saved however should. Be very
careful to distinguish ThisDocument and ActiveDocument!

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[hals_left]

Hi Cindy,

1) Works great - thanks.

2) I dont think this is the problem, beacuse its intermittent , its OK
on some users machines all the time & OK on mine some of the time! Also
all the fieldnames are without spaces both in SQL Server and over in
Word. When it fails, it tells us that every single merge field in the
document is not present in the data. The only field it recognises i the
data source is TitleID which is in the first / initialiatuion view, it
doesnt see any in the second View at all. But I know they are present
because pressing "Recipients" shows them and clicking merge to new doc
then works OK.

It feels like an initialsation problem - Like I nee a delay command
after the line:
ActiveDocument.MailMerge.DataSource.QueryString = strSQL
To give word time to pull the data before executing the merge, if that
makes sense..


4) Because of the type of data created in the merge (Certificates), our
regulators would like to prevent users from even having the option to
save the document created by the template, ideally it shoudl just go
straight to the printer but while we still have bugs we cant do that.

How could I specify "hisDocument.Saved" on the merge documents created
by the merge template ? & is there any way to completely prevent the
document being saved.

Thanks!

 

[Cindy M -WordMVP-]

Hi Hals_left,

2) I dont think this is the problem, beacuse its intermittent , its OK
on some users machines all the time & OK on mine some of the time!

Lucky you can at least see the problem on your machine So often, the

It feels like an initialsation problem - Like I nee a delay command
after the line:
ActiveDocument.MailMerge.DataSource.QueryString = strSQL
To give word time to pull the data before executing the merge, if that
makes sense..

Yeah, I guess that does make sense. If you comment out the code that
actually executes the merge, does the procedure finish without this
error?

4) Because of the type of data created in the merge (Certificates), our
regulators would like to prevent users from even having the option to
save the document created by the template, ideally it shoudl just go
straight to the printer but while we still have bugs we cant do that.

How could I specify "hisDocument.Saved" on the merge documents created
by the merge template ? & is there any way to completely prevent the
document being saved.

The tricky part with all this is that Word doesn't hand you an object
that's contains the merge result. You can usually PRESUME that
ActiveDocument AFTER .Execute is the merge result. You can even do some
tests (like check the file name (mail merge always follows the same
pattern) as well as the AttachedTemplate, which should be the
AttachedTemplate of the main merge document), if it doesn't match, loop
through the documents collection until you do get a match.

The other road to follow would be to use the MailMerge Events, introduced
in Word 2002. Events are always a bit "iffy", but MailMergeAfterMerge
does give you a parameter with the mail merge result document object.

Of course, just setting the .Saved property won't prevent the user from
being able to save the document. It will only suppress the prompt. And
note that the action of printing can "dirty" the document.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail