Best way to automatically email alerts via Outlook?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JohnEnglish

Thread Starter
Joined
Jan 14, 2011
Messages
3
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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

karlhaywood

Account Closed
Joined
Jan 17, 2010
Messages
680
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.
 

JohnEnglish

Thread Starter
Joined
Jan 14, 2011
Messages
3
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
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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?
 

JohnEnglish

Thread Starter
Joined
Jan 14, 2011
Messages
3
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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
See if you can get the Scheduler to open your Excel Workbook for you when you log in or at a specific time.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top