Solved email alerts on Excel

September 24, 2013 at 06:27:17
Specs: Windows
Hi

I have a developed a spread sheet that shows the expiry date of our contractors insurance documents and need an email alert to be set up to request the new documentation.

Table Example:
Column A= Company name/Column B=type of insurance/Column C=expiry date.

The Spreadsheet shall only be opened once a week so I would need that to act as a trigger and for the email to be sent 2 weeks before the actual expiry date.


Many thanks for the help!

message edited by gillkate


See More: email alerts on Excel

Report •


#1
September 24, 2013 at 07:02:17
Look here, should be everything you need:

http://www.rondebruin.nl/win/sectio...

MIKE

http://www.skeptic.com/


Report •

#2
September 24, 2013 at 07:10:12
I was after more of a macro, with on an ongoing basis sends an email alert 14 days prior to the expiry.

thanks

message edited by gillkate


Report •

#3
September 24, 2013 at 07:44:51
✔ Best Answer
Did you look at the web site,
it has Macros to do almost anything you can think of.

Look on this page:

http://www.rondebruin.nl/win/s1/out...

Under the heading: Outlook object model (body)

there is the macro for:

Send a mail when a cell reaches a certain value

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 24, 2013 at 07:49:11
Okay that's great, thanks

Report •

#5
September 24, 2013 at 08:14:02
Well, I wouldn't say that rondebruin website has everything that gillkate needs.

It doesn't address the "The Spreadsheet shall only be opened once a week so I would need that to act as a trigger" or the "for the email to be sent 2 weeks before the actual expiry date" issues.

To have the code run when the workbook is opened, you could use the Workbook_Open event, stored in the ThisWorkbook module. You would want to use a key to prevent duplicate emails from being sent.

For example, one of the last instructions in the code could be to place a date in a cell, e.g. A1. One of the first instructions could be to compare that date to the system date and decide if the email portion of the code should be run or if the macro should exit.

As far as checking the expiry date, you could loop through the "expiry date' column to determine which emails should get sent. By including a column of email addresses in the worksheet, the code could pick up the email address when it finds an "email required" date and use that email address in the .To line in the code.

The rodebruin site certainly has the core of what you need, but any code found there is going to need some serious tweaking.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question