Covert e-mail function required!!! (Resolved)

Jak · May 7th, 2002, 05:00 AM

In an excel worksheet I have created a time-sheet that measures actual hours worked and flexi-time recrued. As part of the flexi-time policy an individual is not allowed to go below –2:00hours into deficit during a monthly period. What I would like to do is have the on_close function check a named range and e-mail me if a sheet is saved and the users time is = to or greater than –2:01 hours.

Any suggestions on how to go about this would be great.

[ This Message was edited by: Jak on 2002-05-07 07:53 ]

[ This Message was edited by: Jak on 2002-05-15 06:30 ]

Tom Schreiner · May 7th, 2002, 06:09 AM

Hi Jak
Which rows or columns contain the username and corresponding flag value?
User name Cell A2
Related value Cell B2
Something like that.
What is your sheet name?
What is your default mail manager?
Outlook? Outlook Express? Other?
Which version of Office are you using?
Thanks,
Tom

Jak · May 7th, 2002, 06:28 AM

Hi TsTom

User.Name cell = B6
Range to be monitored against –2:00 hour deficit = “J18:J45”
Sheet.Name = TIMESHEET
Mail manager = Outlook
Office version = Microsoft Office 2000 Professional

I hope this info is of use and you will be able to give me some insight into the problem. Thanks.

s-o-s · May 7th, 2002, 06:28 AM

Covert may be a problem, more recent versions of office (2000 onwards at least) have built in ability to notify users when other applications eg Excel are trying to use Outlook).

Another alternative would be if you are in a network environment why don't you add some autoclose code which updates a networked file with the info you need, this can then be made covert.

ps Downside of this is that any user that can write to this file could also amend their details if they know its location therefore you will need to hide and protect the code within your VBA project.

Tom Schreiner · May 7th, 2002, 06:47 AM

I agree with SOS on the disk file option.
I do not understand the first statement about user notification. I have several workbooks in 2000 which auto send using Outlook and have never been prompted.
Not even notified in the statusbar.
If possible, I'd like to see your book.
or if you are a bit fluent with the VBA language, I can just send you a couple examples of workbooks using Outlook.
There are also multiple examples of email automation on this board. See search.
My email address is below.
Thanks,
Tom

P.S.
SOS,
If you don't mind, would you explain your first paragraph a bit more?
Thanks

Jak · May 8th, 2002, 10:26 AM

Thanks S O S and TsTom for your contributions. I have looked through the previous forums relating to e-mail, hyperlinks and networked files. I know have enough info and code to make an informed choice on which methodology to adopt.

This subject is now closed

May 7th, 2002, 06:28 AM	#4
s-o-s Board Regular Join Date: Apr 2002 Location: Kissimmee, Florida Posts: 384	Covert may be a problem, more recent versions of office (2000 onwards at least) have built in ability to notify users when other applications eg Excel are trying to use Outlook). Another alternative would be if you are in a network environment why don't you add some autoclose code which updates a networked file with the info you need, this can then be made covert. ps Downside of this is that any user that can write to this file could also amend their details if they know its location therefore you will need to hide and protect the code within your VBA project. __________________ Hope This Helps. Sean. Digest of Homes WinXP, XL XP

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 7th, 2002, 05:00 AM	#1
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	In an excel worksheet I have created a time-sheet that measures actual hours worked and flexi-time recrued. As part of the flexi-time policy an individual is not allowed to go below –2:00hours into deficit during a monthly period. What I would like to do is have the on_close function check a named range and e-mail me if a sheet is saved and the users time is = to or greater than –2:01 hours. Any suggestions on how to go about this would be great. [ This Message was edited by: Jak on 2002-05-07 07:53 ] [ This Message was edited by: Jak on 2002-05-15 06:30 ]

May 7th, 2002, 06:09 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Jak Which rows or columns contain the username and corresponding flag value? User name Cell A2 Related value Cell B2 Something like that. What is your sheet name? What is your default mail manager? Outlook? Outlook Express? Other? Which version of Office are you using? Thanks, Tom

May 7th, 2002, 06:47 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	I agree with SOS on the disk file option. I do not understand the first statement about user notification. I have several workbooks in 2000 which auto send using Outlook and have never been prompted. Not even notified in the statusbar. If possible, I'd like to see your book. or if you are a bit fluent with the VBA language, I can just send you a couple examples of workbooks using Outlook. There are also multiple examples of email automation on this board. See search. My email address is below. Thanks, Tom P.S. SOS, If you don't mind, would you explain your first paragraph a bit more? Thanks

May 8th, 2002, 10:26 AM	#6
Jak Board Regular Join Date: Apr 2002 Location: Newcastle upon Tyne Posts: 762	Thanks S O S and TsTom for your contributions. I have looked through the previous forums relating to e-mail, hyperlinks and networked files. I know have enough info and code to make an informed choice on which methodology to adopt. This subject is now closed