This idea is likely to cause a lot of problems. Mainly because the workbook will have to be kept open all the time.
There are two possible approaches. One is to have a macro which, each minute, scans the spreadsheet to find times between Now and Now + 1 Minute. Then it would send the email if it was not complete. If there were a lot of emails it might take more than a minute to send them all and this could cause problems. Also if the workbook was inadvertently closed some reminders might be missed. You could overcome some of the problems by including an extra column next to each date/time column to indicate if the email had been sent. Then you would perhaps be scanning for unticked dates <Now(). Another approach would be to runa macro on each change in the worksheet which extracted the list of emails and sorted them into date order on a separate sheet. In this case the timer macro, instead of looking every minute would be set to go to sleep until the next email was due. The workbook would still have to be kept open and there might be more problems with regenerating the list and knowing what emails had been sent. I think you would still need the "Sent" columns in the original worksheet. I would only have a workbook running all the time on a computer that did nothing else. Otherwise it is certain you will open another workbook and then close Excel. Also you can run across problems with certain methods of opening trying to open a new instance of Excel and you Personal.xlsb being locked. There may also (I don't know) be problems with running a shared workbook with a a timer event. A third approach might be to use the delayed delivery function in Outlook. Prepare the email as soon as you know it might be required but delay delivery until the due time. It will sit in your outbox. If the task is completed you then delete all undelivered emails relating to that task. I have never developed such a solution so I do not know if there are any issues with it. I am also not an expert at Outlook VBA. However someone else may be able to help you. On 6 June 2012 06:08, Puttu * <puttu...@gmail.com> wrote: > Hi Pascal, > > Thanks for looking into this. I appreciate that. > > my concept is for need a macro where it can send the reminder mail on the > specified time & date which updated in the tracker. Again specified *Date > & Time* and status of *completed* updated manually from user. Only thing > I need macro should trigger automatically during the date and time. > > For Example attached is the xls sheet for your reference > > 1. *"Task (Subject name)"* This refer column B:B (B2) What ever the text > inserted in cell B2 should be my subject line for that mail (user defined) > > 2. *"First Reminder Date & Time"* This refer C:C column - The First > reminder mail should go based on the input time & date given in the cell > (C2) excel sheet, again this date is user defined (Either current of Future > date) -- If the status "F" column still showing as "Work in Progress" then > second reminder should trigger off based on the input time and date > > > 3. *"Second Reminder Date & Time"* This refer D:D column - The Second > reminder mail should go based on the input time & date given in the cell > (D2) excel sheet, again this date is user defined (Either current of Future > date) -- If the status "F" column still showing as "Work in Progress" then > Final reminder should trigger off based on the input time and date > > 4. *"Final Reminder Date & Time*" This refer E:E column - The Final > reminder mail should go based on the input time & date given in the cell > (E2) excel sheet, again this date is user defined (Either current of Future > date) -- If after 3 reminders also the status "F" column still showing as > "Work in Progress" then it should say 3 reminders are completed and end the > reminders. > > 5. *"Status"* Column F:F - If the column F is in progress, then 1, > reminder, second reminder and Final reminder should kickoff. If the Column > F show as completed then reminder should stop. > > 6. *"Email Group"* - List of users mail should rolled out. > > I was tried my best to explain the details. let me know if you still need > any more details. > > Thanks Again and attaching the mail one more time for the reference. > > > On Wed, Jun 6, 2012 at 1:06 AM, bpascal123 <bpascal...@gmail.com> wrote: > >> Hi Puttu, >> >> Please provide more details about your task such as the email client you >> want to use. I would suppose Outlook but you don't tell. How column F is >> completed? Do you manually changed the dates? What are the dates related to. >> >> You should provide more details, it's too vague to me and maybe to >> others, I don't know >> >> Pascal >> >> -- >> FORUM RULES (986+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> >> >> ------------------------------------------------------------------------------------------------------ >> To post to this group, send email to excel-macros@googlegroups.com >> >> To unsubscribe, send a blank email to >> excel-macros+unsubscr...@googlegroups.com > > > > > -- > Putta > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@googlegroups.com > -- David Grugeon -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
