email on the planned date

[shanmughant]

I have designed a role-based planner in excel. I would like to get a
email reminder on the start date and end date of the activity. I a
not able to do it. Can someone help?

Regards

T Shanmugha

 

[Bernie Deitrick]

T,

You can use the worksheet's calculate event (or other event as appropriate -
you really didn't give enough details). This requires a reference set to
Outlook - the code will generate an email when the current date is later
than the date in cell A1. Cell B1 is used to prevent multiple emails being
sent:

Private Sub Worksheet_Calculate()
If Now() > Range("A1").Value And _
Range("B1").Value <> "Contacted" Then
Call EmailBernie
Range("B1").Value = "Contacted"
End If
End Sub

In a regular codemodule:

Sub EmailBernie()
Dim ol As Object, myItem As Object
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "(e-mail address removed)"
myItem.Subject = "Hey, Get Ready..."
myItem.Body = "Hello Bernie, " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "That event is coming up! " & Chr(13) &
Chr(13)
myItem.Body = myItem.Body & "Thanks." & Chr(13) & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Me" & Chr(13)
myItem.Send
Set ol = Nothing
Set myItem = Nothing
End Sub