Tech Support Guy banner
Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
3 Posts
Discussion Starter · #1 ·
I've created an Excel file for the traders at work that list all the securites we hold and the dates the the hold on trading them come off. What I'd like to do is have an email sent out say 3 days before the hold comes off and the day the hold comes off.

I've read around a bit and it seems that there has to be some event in Excel that would trigger the emails. The problem here is that the Excel file will probably be closed most of the time. It'll just be opened every so often to add a new security or if someone wants to check something. There may be several days where it goes without being used.

Is there any other way I could create some sort of database that can automatically send out emails via Outlook without requiring any action on my end (maybe Access?).

The files will all be saved on a networked server and the emails will be send through an Exchange server. Most people are using Office 2003, a few people are using Office 2007. Everyone is on Windows XP.
 

·
Registered
Joined
·
19,932 Posts
JohnEnglish, welcome to the Forum.
You can do this with Excel or Access, if you use Access you can Inmport the Excel data for use.
I have posted a couple of Alert emailing databases before, but the key to doing what you want is Windows Scheduler. Which you use to open the Workbook or database to send the emails if they are required.
 

·
Registered
Joined
·
684 Posts
I agree with OBP. I would create an AutoExec macro to open a blank for form in an access database. The form would have hidden code to search through the database and send your emails/alerts via outlook. I would definitely use the Task Scheduler to trigger the macro to open the form. Hope you get on ok, if not get back in touch and we can assist you with this.
 

·
Registered
Joined
·
3 Posts
Discussion Starter · #4 ·
Sorry for the delayed response, I've been quite busy with work.

I understand what you're saying regarding the task scheduler to trigger the Excel emails. However, I'm not sure how to do that.

I guess what I'd like clarification on is:

(1) How to set up Windows Scheduler

(2) How to set up Excel to send the emails
 

·
Registered
Joined
·
19,932 Posts
The windows scheduler can be set from the Windows XP "Help & Support" to open the Excel workbook when the computer is switched on.
The excel workbook would use the "WorkbookOpen Event" to trigger some VBA code, the vba code will have to loop through the range of data that holds the Trade off date.
But you have to consider some operating issues as well.
Every time the workbook is opened the vba will send the emails, so you need some way to turn them off once sent.
What do you do about "Trade off dates" when the computer with the Schedule is not started 3 days before or on the day?
 

·
Registered
Joined
·
3 Posts
Discussion Starter · #6 ·
That's a good point; I don't want it to send emails everytime the workbook is opened. Ideally it would send the emails at a preset time if certain conditions were met (i.e. send email at 6 am if there are broker warrants set to come off of trading hold within the next 3 days OR send an email if any broker warrants are coming off hold that day).

As for the second point, the computers are always on, I'm just not logged into my account. I assume that would still prevent the email from being sent? If that's the case I guess the only solution is to send the emails the next time the account is logged into.
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top