[Laura[_5_]]

Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura

[Ron de Bruin]

Hi Laura

This in a module

Sub testmail()
Recipient = "
Subj = "Test"
Msg = "Hi there the date in cell ..........."
HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%s", True
End Sub


And this in the Thisworkbook module

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("B3") DateSerial(2003, 7, 5) Then
testmail
End If
End Sub

When you open the workbook it will check the date in b3 and if it is
DateSerial(2003, 7, 5) send the mail


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laura" wrote in message om...
Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura

[Chris Nicholas]

"Laura" wrote in message
om...
Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura

You can have the cell date checked in the Workbook_Open event and present a
message box:

In ThisWorkbook Under Workbook_Open place the following code. Replacing the
appropriate cell for A1 and the date that you expect to produce the
Notification.

If DateValue(Range("A1").Value) = DateValue("7/10/2003") Then
MsgBox "The Date has been reached.", vbCritical, "Date Check"
End If

You can also have it send a copy of the Workbook to someone:

ActiveWorkbook.SendMail ActiveWorkbook.SendMail
", Subject:="Date Reached"

But note that if you are using Outlook as your e-mail or Outlook Express the
application will prompt you stating that an application is attempting to
send something on your behalf. Then ask if it is to be allowed.

This is actually documented in Article 263107 XL2000: SendMail Method
Prompts After You Apply Outlook E-mail Security located at
http://support.microsoft.com/?id=263107

Chris

[Tom Ogilvy]

Just to add, this will send the email everytime the workbook is opened and
the criteria is met. That may be what you want, but if you only want it
sent on the first occurance, you would need to add some type of indicator
that the email has already been sent and check that indicator before
executing the code to send the email. Just a thought.


Regards,
Tom Ogilvy

"Ron de Bruin" wrote in message
...
Hi Laura

This in a module

Sub testmail()
Recipient = "
Subj = "Test"
Msg = "Hi there the date in cell ..........."
HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%s", True
End Sub


And this in the Thisworkbook module

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("B3") DateSerial(2003, 7, 5) Then
testmail
End If
End Sub

When you open the workbook it will check the date in b3 and if it is
DateSerial(2003, 7, 5) send the mail


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laura" wrote in message
om...
Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura

[Laura[_5_]]

Thank you so much
The only problem I am having with it is it wont automatically send. So
if you have another way to do that it would be greatly apreciated.
But thank you for all that you've done so far.

Laura

"Ron de Bruin" wrote in message ...
Hi Laura

This in a module

Sub testmail()
Recipient = "
Subj = "Test"
Msg = "Hi there the date in cell ..........."
HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%s", True
End Sub


And this in the Thisworkbook module

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("B3") DateSerial(2003, 7, 5) Then
testmail
End If
End Sub

When you open the workbook it will check the date in b3 and if it is
DateSerial(2003, 7, 5) send the mail


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laura" wrote in message om...
Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura

[Ron de Bruin]

Hi Laura

The only problem I am having with it is it wont automatically send
You can set this in the options of your mail program


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laura" wrote in message om...
Thank you so much
The only problem I am having with it is it wont automatically send. So
if you have another way to do that it would be greatly apreciated.
But thank you for all that you've done so far.

Laura

"Ron de Bruin" wrote in message ...
Hi Laura

This in a module

Sub testmail()
Recipient = "
Subj = "Test"
Msg = "Hi there the date in cell ..........."
HLink = "mailto:" & Recipient & "?"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & Msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
SendKeys "%s", True
End Sub


And this in the Thisworkbook module

Private Sub Workbook_Open()
If Sheets("Sheet1").Range("B3") DateSerial(2003, 7, 5) Then
testmail
End If
End Sub

When you open the workbook it will check the date in b3 and if it is
DateSerial(2003, 7, 5) send the mail


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Laura" wrote in message om...
Hi
I need to either send an email or have a warning pop up when I open
the workbook when a cell gets to a certain date. Any help would be
greatly appreciated. Thank you.

Laura