Question

[Guest]

I have a spreadsheet where I track certain events. I have a cell where a date
is recorded for a certain event. Is there a way to program excel so that when
the date in the cell becomes today's date, excel will send me an email to
alert me that I need to take action. If there is a way could you direct me to
a website where performing this action is explained. I am using Excel 2003.

Thanks in advance for your help.

Dee

 

[Bernie Deitrick]

Dee,

Set a reference to Outlook. Put this into the sheet's codemodule: assumes that cell B6 has the
date, a formula references cell B6, and B7 is empty or is not equal to "Contacted"

Private Sub Worksheet_Calculate()
If Range("B6").Value = Date And Range("B7").Value <> "Contacted" Then
Call EmailMe
Range("B7").Value = "Contacted"
End If
End Sub

And put this into a regular codemodule:

Sub EmailMe()
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 = "Check that workbook..."
myItem.Body = "Hello Dee, " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Could you check that file for values? " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Thanks for doing that." & Chr(13) & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Dee" & Chr(13)
myItem.Send
Set ol = Nothing
Set myItem = Nothing
End Sub


Note that depending on your version of Outlook, you may be prompted to allow the sending of the
email .... a pain, but....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Dear Bernie,

Thank you for the information. I just have a couple of questions. When you
say put the first part in the sheets module and the second part in a regular
module. What does that mean. I don't know a lot about VBA so I'm a little
confused. I know enough to copy code and paste it into my spreadsheets and
make it work, but I'm not sure about the difference between the sheets module
and a regular module. If i right click on the sheet and say insert module, a
module opens. How do I get a regular module.

Thanks in advance for your help.

Best regards,

dee

 

[Bernie Deitrick]

Dee,

Read

Getting Started with Macros and User Defined Functions

at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and then read

Event Macros, Worksheet Events and Workbook Events

at

http://www.mvps.org/dmcritchie/excel/event.htm


HTH,
Bernie
MS Excel MVP

 

[Guest]

Dear Bernie,

Thank you for the links. I followed the instructions. I referenced outlook.
I pasted your first code in the wooksheet and then pasted your second piece
of code in a module. The macro is in my tools, macros and when I click run it
emails me. However, how do i get Excel to email me when the date in the cell
is today's date. If I want to apply this to a number of cells do I just put
in a range of cells. For example if I want to be emailed when the dates in
any cell in the range of B1-B10 equals today Excel will automatically email
me to alert me that I need to do something.

Thanks very much for all your help. I learn so much from these newsgroups.

Best regards,

 

[Bernie Deitrick]

Dee,

How about adding an appointment to your Outlook calendar, for each date? Then Excel doesn't have to
be opened, or the specific file opened, etc. A much neater solution, I think.

Copy the code below into a codemodule, and set a reference to Outlook. Then run the macro. It will
create an appointment at 8:00 on the date of each cell in B1:B10. You could add in other
information to the appointment, and also add a flag next to the cells in Excel so that if you add or
change a date, you can run the same macro by deleting the flag ("Appointment added") in column C of
the same row. (This assumes that column C is blank in those rows - the macro will overwrite whatever
is there.) You can further customize the appointment by adding reminders, setting lengths, etc.

HTH,
Bernie
MS Excel MVP

Sub CreateOutlookAppointments()
Dim OL As Object
Dim myAppt As Outlook.AppointmentItem
Dim myCell As Range

Set OL = CreateObject("outlook.application")

For Each myCell In Activesheet.Range("B1:B10")
If myCell(1,2).Value <> "Appointment added" Then
Set myAppt = OL.CreateItem(olAppointmentItem)
With myAppt
.Body = "An important reminder"
.Start = myCell.Value + 8 / 24
.Subject = "This is an important reminder"
.Save
End With
myCell(1,2).Value = "Appointment added"
Next myCell

End Sub

 

[Guest]

Dear Bernie,

What a fantastic idea. I can run the maco when I enter the date. I copied
the code you gave me into a module. When I ran the macro I got a "Compile
error Next without for" Is there anything else I need to change other than
the range if the range my dates are in is different than B1:B10. Sorry for
all the questions, but I am quite the novice when it comes to VBA.

Thanks again for all your help. It is greatly appreciated.

Best regards,

Dee

 

[Bernie Deitrick]

My bad. I left out an End If - I modified the code without testing....

Sub CreateOutlookAppointments()
Dim OL As Object
Dim myAppt As Outlook.AppointmentItem
Dim myCell As Range

Set OL = CreateObject("outlook.application")

For Each myCell In Activesheet.Range("B1:B10")
If myCell(1,2).Value <> "Appointment added" Then
Set myAppt = OL.CreateItem(olAppointmentItem)
With myAppt
.Body = "An important reminder"
.Start = myCell.Value + 8 / 24
.Subject = "This is an important reminder"
.Save
End With
myCell(1,2).Value = "Appointment added"
End If
Next myCell

End Sub


HTH,
Bernie
MS Excel MVP

 

[Guest]

Dear Bernie,

I was away and just got back and was able to test the macro you gave me.
When I ran the macro it entered in the next cell "appointment added" even if
there was no date in cell B1 to B10. It added an appointment for the dates
that were in cells B1 to B4. Is there a way to apply the maco just to one
cell at a time. I won't be entering all the dates at the same time. Thanks
again for all your help.

Best regards,

Dee