Query to send e-mail

[Glenn]

Is there anyway to set up a Query so that if the field contains a specific
word it sends an e-mail to address in another field in the same record? I
just want to send a standard message to all those who meet the Query.

If so, how can I do it?

Thanks.

 

[Michel Walsh]

Hi,



Depends of your Access version, with Access 2, 97 and 2000 I "think" you
can. It is a matter to SHELL within the table validation rule. The exact
syntax is a little bit tricky and I don't have any of the mentioned versions
installed, but may be Google can speak to you. You can refer to field of the
SAME record in the TABLE validation rule, but you cannot use VBA functions.
iif and shell are defined inside Jet, so it is ok to use them. From memory,
I would start with something like:

iif( compareField = "toto", 0<>shell( "Notepad.exe"), true)

to conditionally open Notepad if toto is to be stored in compareField. Note
that with Jet, only the relevant portion of the iif is evaluated, so shell()
should not be evaluated if something else than toto is stored. I used
Notepad instead of "sending mail", it is easier for debugging purposes.

Note that I don't claim the syntax is right, it is by memory and I didn't,
can't, test it.


Hoping it may help,
Vanderghast, Access MVP

 

[Glenn]

I am using Access 2003. Is there a different way to do it with that?

 

[Michel Walsh]

Hi,


Not that I know, in Jet... and I would not really be at ease to supply
more details, sorry.

If you really need that kind of facility, can you migrate to MS SQL
Server which has triggers? or, without moving everything, to move that
"table" in MS SQL Server? you can test the updated values inside a trigger,
and call appropriate stored procedure, such as sending email.


Hoping it may help,
Vanderghast, Access MVP

 

[Chaim]

The SQL Server with trigger idea won't work either. He's asking for this
action to occur as result of a query; there is no trigger fired when you do
a select.

 

[Michel Walsh]

Hi,


The table validation wouldn't have work, since it is fired only on
append or on update.

You may present the query through a form, that untimely opens the query,
but in the form, in VBA code, do whatever checks are needed before
presenting the query. That assumes the user-security is in place so that the
"standard user" has to use your form to access the data in the query, not
having the right to open the query, directly.



Hoping it may help,
Vanderghast, Access MVP