Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 = " 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 "Dee" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question
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 "Bernie Deitrick" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 "Dee" wrote in message ... 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" wrote: 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 = " 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 "Dee" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|