Checkboxes on mailmerge recipients list

[Steffen Grellmann]

Hi newsgroup,

I'm quite familiar how Word is handling SELECT-statements.

What I want to do is to programmatically access/set the state of the
checkboxes in the "mailmerge recipients list" in order to set that
state on my own Word form (which includes a similar recipients list).

How is Word reaching that unchecked items are not printed when
executing the mail merge?

Could someone bring more light on this, please?
Your time and help is higly appreciated.

Kind regards,

Steffen

 

[Peter Jamieson]

Roughly speaking the checked/unchecked states of these boxes correspond to
the value of ActiveDocument.MailMerge.DataSource.Included for the
ActiveRecord.

In other words, if you want to set or examine the checkbox value, you have
to navigate to the record you want then examine or modify the value of
..Included.

However,
a. it isn't particularly easy to manipulate the .ActiveRecord reliably, in
the general case (the facilities for doing this are a bit weird, and if you
do it one way, you'll find that when you iterate through the records, you
will not even see the ones where Included is set to False)
b. my recent tests in this area have tended to hang Word.

 

[Steffen Grellmann]

Hi Peter,

thank you for replying.

Roughly speaking the checked/unchecked states of these boxes correspond to
the value of ActiveDocument.MailMerge.DataSource.Included for the
ActiveRecord.

This was exactly what I wanted to know for getting started on this
matter. Thank you!

I just want to give you a short response before I'm getting deeper
into it.

In other words, if you want to set or examine the checkbox value, you have
to navigate to the record you want then examine or modify the value of
.Included.

OK, I have checked this but it's not working that simple as it is
described in the F1 help.

However,
a. it isn't particularly easy to manipulate the .ActiveRecord reliably, in
the general case (the facilities for doing this are a bit weird, and if you
do it one way, you'll find that when you iterate through the records, you
will not even see the ones where Included is set to False)

It seems tricky and buggy. I will do some more testing and give you an
response.

b. my recent tests in this area have tended to hang Word.

ACK!

Kind regards,

Steffen

 

[Peter Jamieson]

You probably need something like the following:

---------------------------------------------
Sub x()
Dim lLastRecord As Long
With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdLastDataSourceRecord
lLastRecord = .ActiveRecord
.ActiveRecord = wdFirstDataSourceRecord
Debug.Print CStr(.ActiveRecord) & ": " & CStr(.Included)
Do Until .ActiveRecord >= lLastRecord
.ActiveRecord = wdNextDataSourceRecord
Debug.Print CStr(.ActiveRecord) & ": " & CStr(.Included)
Loop
End With
End Sub
---------------------------------------------

But I can't remember which version of Word first had wdLastDataSourceRecord
etc. You also need to consider the possibility that with some data sources,
Word may not actually be able to determine the number of the last record
without reading the entire data source, e.g. if the data source is an OLE DB
source with a "forward only cursor". Ther may also be problems in a
multi-user environment if someone adds or deletes records while you iterate
through them (I really do not know what Word does in that case)/

Although you can /set/ .ActiveRecord to the special values like
wdLastDataSourceRecord , you can't compare .ActiveRecord to them because in
that case VBA thinks you are trying to compare .ActiveRecord with a negative
integer. Personally, I think this confusion of actual numeric values and
symbolic values is a very poor way to do things.

 

[Steffen Grellmann]

Hi Peter,

thank you for replying.

My main problem at that point is that I can't read the value of
..Included without an error message in that case, that a new connection
to the data source has been freshly established /and/ Word's
recipients list has not been opened by clicking the appropriate button
on the command bar at minimum once! Any suggestions? I don't want to
solve this by opening the recipients list per code
(Dialogs(wdDialogMailMergeRecipients).Show). Setting the
SetAllErrorFlags and SetAllIncludedFlags even do not solve this
behaviour.

Sub x()
Dim lLastRecord As Long
With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdLastDataSourceRecord
lLastRecord = .ActiveRecord
.ActiveRecord = wdFirstDataSourceRecord
Debug.Print CStr(.ActiveRecord) & ": " & CStr(.Included)
Do Until .ActiveRecord >= lLastRecord
.ActiveRecord = wdNextDataSourceRecord
Debug.Print CStr(.ActiveRecord) & ": " & CStr(.Included)
Loop
End With
End Sub

Thanks for that snippet. This is quite helpful because .RecordCount
always returns -1 when "SELECT DISTINCT..." is used (which I have to
use for some special reasons...). And .LastRecord is returning a
negative value in some cases!

But I can't remember which version of Word first had wdLastDataSourceRecord
etc.

Word 97 definetly don't have the wdLastDataSourceRecord, relying on
some articles, Word 2000 and newer has (not tested).

Ther may also be problems in a
multi-user environment if someone adds or deletes records while you iterate
through them (I really do not know what Word does in that case)/

I will do some tests on this as soon as it is clear that my plan to
use the Checkstate on my own form can be realized.

Although you can /set/ .ActiveRecord to the special values like
wdLastDataSourceRecord , you can't compare .ActiveRecord to them because in
that case VBA thinks you are trying to compare .ActiveRecord with a negative
integer.

But this doesn't matter if I would use your snippet above which is
using the value of .LastRecord instead of .RecordCount, which I would
normally use in For...Next constructs, or I'm wrong?

Personally, I think this confusion of actual numeric values and
symbolic values is a very poor way to do things.

ACK!

Kind regards,

Steffen

 

[Peter Jamieson]

Hi Steffen,

I knew there was a reason why I never manipulated .Included but I had
forgotten what it was. I get exactly the same thing here. I can't think of a
way around it - even performing a merge to a new document does not magically
create the .included objects.

You can trap the error when you test .Included. I suppose that is enough to
tell you that all the records must be included (because the user has not
looked at the Recipients dialog box) but I can't see how you can get Word to
create these objects without showing the dialog.

I will see if I can find out anything more about this.

 

[Steffen Grellmann]

Hi Peter,

thank you for replying.

Do you know a way to /close/ the Recipients dialog programmatically?
This could be an - unpleasant - workaround, to open/close the dialog
before executing my own code.

I knew there was a reason why I never manipulated .Included but I had
forgotten what it was.

It calms me down a little bit that I'm not the only one...

I get exactly the same thing here. I can't think of a
way around it - even performing a merge to a new document does not magically
create the .included objects.

I've tried this also like some other things, with no success.

You can trap the error when you test .Included. I suppose that is enough to
tell you that all the records must be included (because the user has not
looked at the Recipients dialog box) but I can't see how you can get Word to
create these objects without showing the dialog.

I will see if I can find out anything more about this.

And I will post it here if there are any news from my side.

Kind regards,

Steffen

 

[Peter Jamieson]

Do you know a way to /close/ the Recipients dialog programmatically?

No, unless you can either get sendkeys to work (which I can't, here), or do
the equivalent of sendkeys using the Win32 API.

Many of the older Word dialog boxes have properties and methods that allow
you to manipulate them without displaying them at all. However, the newer
and more complicated boxes either do not have such properties, or if they
do, they are not documented anywhere.

 

[Steffen Grellmann]

No, unless you can either get sendkeys to work (which I can't, here), or do
the equivalent of sendkeys using the Win32 API.

I also can't get SendKeys working. Because the recipients dialog is a
popup you can't execute any code until the dialog has been closed. It
doesn't wonder that I had no success when I tried to close the dialog
using the Win API. It might be worth trying to /open/ the dialog using
Win API and to close it immediately again, but I have no idea for the
moment how the dialog could be opened using API.

Kind regards,

Steffen

 

[Peter Jamieson]

It might be worth trying to /open/ the dialog using

Win API and to close it immediately again, but I have no idea for the
moment how the dialog could be opened using API.

I don't know how to do it either. I suspect that in this case, Word needs to
drive the dialog box anyway.

 

[Peter Jamieson]

The only other suggestion I can make is that if you are using a suitable
dialect of SQL (i.e. one that can do JOINs with something that you are able
to create) then you could build a list of included record numbers and store
it as a file, then do an Inner Join with whatever table/query you are
already using as a data source. Jet/ACE would probably support such a Join
even if the data was stored as (say) a delimited text file, although I
haven't actually tried that in Word. Or alternatively if the list of
included records is small enough that you don't exceed the 255/511 character
limit on qeury length, I suppose you could add

WHERE included_record_in IN (ID1,,ID)

 

[Steffen Grellmann]

The only other suggestion I can make is that if you are using a suitable
dialect of SQL (i.e. one that can do JOINs with something that you are able
to create) then you could build a list of included record numbers and store
it as a file, then do an Inner Join with whatever table/query you are
already using as a data source. Jet/ACE would probably support such a Join
even if the data was stored as (say) a delimited text file, although I
haven't actually tried that in Word.

Thank you Peter, this is another nice idea. I will think about it.
It's a bit pity that with this solution the checkstate would not be
consistent beetween Word's recipients list and my own list. It was my
goal to make this consistency sure at any time. But if you know this
approach is not that easy as to say (e.g.the filters...).

Or alternatively if the list of
included records is small enough that you don't exceed the 255/511 character
limit on qeury length, I suppose you could add

WHERE included_record_in IN (ID1,,ID)

Before I started this thread, I first thought Word is doing it
internally this way. But I cannot use WHERE because I'm at the 255/511
character limit right now and the amount of possible records is too
much.

Kind regards,

Steffen

 

[Peter Jamieson]

It's a bit pity that with this solution the checkstate would not be

consistent beetween Word's recipients list and my own list.
Yes...

It was my
goal to make this consistency sure at any time. But if you know this
approach is not that easy as to say (e.g.the filters...).

....at a certain point, I do not try to push Word, or the other components of
Office, or any other piece of complex software, too hard. If it <<does not
want>> to do a certain thing, the effort requied to make it do that thing
reliably is usually expensive. If you are coding in Word all the time, and
you have to support your code every day, then you probably learn what
actually works and what does not. I am not really in that position.

In this case, one thing you could try is to intercept the Edit Recipients
command (I'm not sure you can easily). If it is feasible, then at least you
should be able to tell that the user has used the Edit Recipients dialog
box, and at that point you may be able to check the current value of
..QueryString (if they have not changed it, of course!) inspect the values of
..Included, and bring "your" data into line with that. Perhaps that is what
you are already doing. Personally, I do not like intercepting commands at
all: Word provides a standard user interface, and I do not like altering an
experienced user's experience of that interface. Where it is feasible, I
would prefer to add stuff and tell them to use that instead. But I know that
is not always what is required in an organisation.

The other problem with adjusting your list to be in line with what they
specified is that it will always allow them to /exclude/ records, but it's
not so easy to see how they could re-include them usin gthe standard dialog.
Using your dialog, it should be feasible.

 

[Steffen Grellmann]

Hi Peter,

thank you for replying and sharing your ideas.

...at a certain point, I do not try to push Word, or the other components of
Office, or any other piece of complex software, too hard. If it <<does not
want>> to do a certain thing, the effort requied to make it do that thing
reliably is usually expensive. If you are coding in Word all the time, and
you have to support your code every day, then you probably learn what
actually works and what does not. I am not really in that position.

I agree with you. Even if I'm an experienced programmer, I'm not in
this position, too. For the moment I will concentrate on other topics
of my Word application. Maybe I'm going to do a second try in a couple
of weeks. It would just take too much time and would make it to
expensive as I believe. As a compromise, I will tell the users, they
have to use Words dialog if they want to include/exclude recipients.

Thank you again for your time an help.

Kind regards,

Steffen