Send automatic message when logging in and out

[CW]

I realise some people may see this as more of an Outlook issue, rather than
Access, but I have tried posting it in that group without any response, and I
think this group is probably a rather more responsive and creative bunch of
people so here we go:

Our users are somewhat scattered around the site and do not always know who
is in or out.
We would like to have Outlook send automatic messages when a user logs into
our Access database (the main tool) and again when they log out. (Equally, it
could be when he/she starts and Outlook, as this is used concurrently with
Access by everybody).
This would inform other staff whether that user was now in the office, or
had gone out.
In other words, upon opening, a msge would be sent saying "John Smith is now
in the office" and when closing, another message would go out saying "John
Smith has left the office".
Can anybody come up witn a way of doing this, please?
Many thanks!
CW

 

[Tom Wickerath]

Hi CW,

Do you really need to send e-mail notifications? Seems like a *LOT* of
e-mail that one or more people would be receiving. I suspect that they'd
start to get annoyed pretty soon at the volume of e-mail, especially if a
person opened your application and closed it several times each day.

Instead, may I suggest that you simply record the last login and last log
out times in a table? This way, you could easily set up a query that
management could run to answer various questions, such as who did not log in
today, who logged in today and for how long, who is currently logged in (but
not logged out), etc. You could base a form or report on your query. That
way, your management could "pull" the information when they wanted it,
instead of having it constantly "pushed" to them in the form of a blizzard of
e-mail messages.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[CW]

Tom -
Firstly, thanks for the very promt response - I wasn't expecting that on a
Saturday!
You make a good point about the potential volume of messages, but it won't
really be too much of an issue with us as there aren't that many people (less
than 10) and they tend to only arrive once and leave once during each day
(i.e. they're not constantly coming and going).
Also, the messages wouldn't go to all users. Only to one mailbox, "Reception".
One of the main concerns is that as the phone "reception" service is
distanced from most of the users, at a remote office, they have no idea who
is in or out at any given time. This is the main driver for having an
automatic notification msge such as I am trying to implement.
I can see how your suggested table could work and I might do some work on
this, but if at all possible I would really like to come up with individual
notifications, if this is programatically (? strange word...) feasible.
Thanks again
CW

 

[Tom Wickerath]

Well, yes, it is possible to use e-mail notification. However, Outlook and
Outlook Express would require user intervention, assuming you have the latest
service packs installed. Thus, the user would need to click OK on a prompt
that indicates that an application is attempting to send a message. If you
want the message sent silently, then Outlook and Outlook Express would be
pretty much ruled out.

While it may take longer to open a query over a LAN, as long as the
recordset is not huge, you shouldn't have too much problem. For example, I
can open a table with 30 records from my office in Renton, Washington (USA)
in a .mdb that is on a file server in Houston, Texas (USA). The table,
incidently, shows employee names and last login date & time, although I'm not
tracking log out date & time. It usually takes about 10 seconds or so for the
table to open. That's not terrible, considering the distance involved
(approx. 2450 miles [or 3940 km] according to MapQuest).

Considering this, do you still wish to pursue an e-mail notification, and,
if so, would it need to be done silently (without the employee knowing about
it)?

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[CW]

Tom -
The "Reception" staff don't actually have access to the network where the
users are (it's a service provided to us by an associate company and for
privacy reasons we wouldn't want them connected).
For this reason, email seems to be the most suitable (only?) medium that we
can use.
But there's no concern about the silent aspect of it - no problem if users
had to click to enable the message to be sent.
Thanks again for your time spent helping with this!
CW

Well, yes, it is possible to use e-mail notification. However, Outlook and
Outlook Express would require user intervention, assuming you have the latest
service packs installed. Thus, the user would need to click OK on a prompt
that indicates that an application is attempting to send a message. If you
want the message sent silently, then Outlook and Outlook Express would be
pretty much ruled out.

While it may take longer to open a query over a LAN, as long as the
recordset is not huge, you shouldn't have too much problem. For example, I
can open a table with 30 records from my office in Renton, Washington (USA)
in a .mdb that is on a file server in Houston, Texas (USA). The table,
incidently, shows employee names and last login date & time, although I'm not
tracking log out date & time. It usually takes about 10 seconds or so for the
table to open. That's not terrible, considering the distance involved
(approx. 2450 miles [or 3940 km] according to MapQuest).

Considering this, do you still wish to pursue an e-mail notification, and,
if so, would it need to be done silently (without the employee knowing about
it)?

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom -
Firstly, thanks for the very promt response - I wasn't expecting that on a
Saturday!
You make a good point about the potential volume of messages, but it won't
really be too much of an issue with us as there aren't that many people (less
than 10) and they tend to only arrive once and leave once during each day
(i.e. they're not constantly coming and going).
Also, the messages wouldn't go to all users. Only to one mailbox, "Reception".
One of the main concerns is that as the phone "reception" service is
distanced from most of the users, at a remote office, they have no idea who
is in or out at any given time. This is the main driver for having an
automatic notification msge such as I am trying to implement.
I can see how your suggested table could work and I might do some work on
this, but if at all possible I would really like to come up with individual
notifications, if this is programatically (? strange word...) feasible.
Thanks again
CW

 

[Tom Wickerath]

Oops. That should have read WAN (wide area network) as opposed to LAN (local
area network):

Change this:

While it may take longer to open a query over a LAN, as long as...

to this:

While it may take longer to open a query over a WAN, as long as...

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Well, yes, it is possible to use e-mail notification. However, Outlook and
Outlook Express would require user intervention, assuming you have the latest
service packs installed. Thus, the user would need to click OK on a prompt
that indicates that an application is attempting to send a message. If you
want the message sent silently, then Outlook and Outlook Express would be
pretty much ruled out.

While it may take longer to open a query over a LAN, as long as the
recordset is not huge, you shouldn't have too much problem. For example, I
can open a table with 30 records from my office in Renton, Washington (USA)
in a .mdb that is on a file server in Houston, Texas (USA). The table,
incidently, shows employee names and last login date & time, although I'm not
tracking log out date & time. It usually takes about 10 seconds or so for the
table to open. That's not terrible, considering the distance involved
(approx. 2450 miles [or 3940 km] according to MapQuest).

Considering this, do you still wish to pursue an e-mail notification, and,
if so, would it need to be done silently (without the employee knowing about
it)?

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom -
Firstly, thanks for the very promt response - I wasn't expecting that on a
Saturday!
You make a good point about the potential volume of messages, but it won't
really be too much of an issue with us as there aren't that many people (less
than 10) and they tend to only arrive once and leave once during each day
(i.e. they're not constantly coming and going).
Also, the messages wouldn't go to all users. Only to one mailbox, "Reception".
One of the main concerns is that as the phone "reception" service is
distanced from most of the users, at a remote office, they have no idea who
is in or out at any given time. This is the main driver for having an
automatic notification msge such as I am trying to implement.
I can see how your suggested table could work and I might do some work on
this, but if at all possible I would really like to come up with individual
notifications, if this is programatically (? strange word...) feasible.
Thanks again
CW

 

[Tom Wickerath]

Okay, perhaps the easiest way to send the notification is to use an Autoexec
macro. You can add this as an action to an existing Autoexec macro, if you
are using one.

Action: SendObject

In lower pane:
To: (e-mail address removed)
Subject: Arrived at work
Message Text: I'm here now.
Edit Message: Yes or No, your choice.

Note: Yes will cause the message to display for the user, but it avoids the
"An application is attempting to automate blah blah blah" warning message. A
setting of No will result in the warning message. Personally, I prefer using
a setting of Yes.

For leaving work, you can send a similar message via a saved macro (or VBA
code, if you are comfortable working with VBA). Just call the macro or code
in the Close Event procedure of your switchboard form. You might also want to
include a message box to remind the person to make sure their e-mail software
is opened, so that the message doesn't end up sitting in their Outbox.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Baz]

I have used Outlook Redemption in a live environment without any problems,
but I haven't used it extensively. The biggest problem I have encountered
is that it costs $200!

I generally ignore the email client and use the vbSendMail DLL, unless the
users specifically want the outgoing email in their Sent Items folder.

For sending emails via the default email client, with user intervention, I
find that a mailto request is by far the simplest (apart from SendObject,
which isn't reliable). The only drawback with a mailto request is that you
can't do attachments.

 

[Tom Wickerath]

Does the vbSendMail DLL work in a VBA environment? It sounds like it might,
otherwise I'm guessing you wouldn't have mentioned it in the context of an
Access question. I only see VB listed on the web site.

One can probably get approval (if that is even required) in such a small
company to install this .dll file. I can tell you that I'd have to pull teeth
to get it made available for installation at the Fortune 500 company that I
work at. Very difficult process to get external software approved, especially
if created by an unknown person.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[CW]

Tom and Baz -
Thank you SO much for your great advice. Your explanations are good and
clear and hopefully I'll get this working over the weekend and give everybody
a nice surprise with this new feature on Monday morning!
Best wishes
CW

 

[Rick Brandt]

Tom and Baz -
Thank you SO much for your great advice. Your explanations are good and
clear and hopefully I'll get this working over the weekend and give everybody
a nice surprise with this new feature on Monday morning!
Best wishes
CW

When users don't need to see (or even be aware of) the message and all of them
will have Windows 2000 or higher then sending the message using the CDOSys
library is the simplest and most reliable way to go. It requires minimal code,
no additional software to be installed, and avoids all of the security issues
that are present with Outlook or Outlook Express.

 

[Baz]

You simply reference it, like you would any other library. The compiled DLL
is included in the download (at least, it was when I last downloaded it).

It's nice to be able to tinker with it in VB, but not essential, it works
well straight out of the box.

 

[Baz]

Example here:

http://support.microsoft.com/kb/313775

Looks useful, especially for apps that need distributing: no additional
components to worry about.

 

[Baz]

Re getting approval, as a freelancer if I come up against such anal
retentives I tell them to get someone else to do the job. After all, if
they are prepared to let me install my software there is absolutely no
reason (other than boneheaded bureaucracy) not to let me install someone
else's if I've got the source code for it and compiled it myself.

 

[Grover Park George]

Tom,

That would be me, I think, and yes, we had some initial issues with
Redemption. Specifically, email goes into the "Draft" folder before being
sent, so if the user closes Outlook, or if Outlook is not running, the email
just sits there waiting until Outlook is started again.

SInce I'm the one sending out reports with this technique, I always manually
ensure that Outlook is open until all of the outgoing reports (as many as 30
or 40 at a time) go out.

If you can count on your users to make sure Outlook (or Outlook Express) is
running, it works well.

I have crafted two or three different modules to handle variations on report
packages we send out regularly. Here's a URL to a DEMO version.
http://www.gpcdata.com/downloads/multipleaddresseedemo.zip

It's a large file.

 

[Tom Wickerath]

Hi George,

Actually, I was thinking that Armen had made the statement I referenced. I
wasn't aware that you had brought up any issues with Outlook Redemption.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Well, actually, there is good reason to not be so trusting of software from
any and all sources. If the source code is available (I wasn't aware that
this was true with the .dll file you referenced--perhaps I just didn't see
that part of the web site), then the process of getting approval is likely
easier.

In my case, the company I work for is involved with a lot of very sensitive
and classified military contract work. I work for the commercial side of the
business, but there's plenty of very valuable data that needs to be
protected. You really wouldn't want to be loading software unless you could
be assured that it was not spyware that is designed to collect data and send
it to a PC off site. Or collect sensitive data and write it to some obscure
out-of-the-way file, where a disreputable person who might have a business
relationship that involved maintaining an application could periodically come
in and harvest the collected data file.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jan Kowalski]

You simply reference it, like you would any other library. The compiled
DLL
is included in the download (at least, it was when I last downloaded it).

It's nice to be able to tinker with it in VB, but not essential, it works
well straight out of the box.