Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Outlook alerts and email functionality to Excel spreadsheeet

    Good Morning all and thanks for any help in advance! I manage a data center with a staff of 7 computer operators. The main aspect of their job is to follow a run book and perform a series of tasks throughout their shift. This process is currently paper driven with really no controls in place. I'm currently in the process of creating an excel document that will track their daily tasks and at the end of their shift submit to me via email a completed shift document. I have some general conditioning and formatting built in and I am by no means an excel pro. A few controls I would like incorporate would be the operator not be able to submit the document without all tasks being completed and possibly them even be emailed reminders during their shift on tasks that have not been completed at stated time. I've attached the current state of the spreadsheet and I again, I appreciate any and all assistance!
    Kevin
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Your question is pretty general and the answers will depend on many things.

    Could you walk us through what you envision what a user would do, and what you would want the program to do and when? Do you want code running all the time excel is open or only check things based on particular actions (buttons, cells changing, selection, etc).

    Also, are you emailing from outlook or some other program, and what do you want emailed and when?

    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Your question is pretty general and the answers will depend on many things.

    Could you walk us through what you envision what a user would do, and what you would want the program to do and when? Do you want code running all the time excel is open or only check things based on particular actions (buttons, cells changing, selection, etc).

    Also, are you emailing from outlook or some other program, and what do you want emailed and when?

    Steve
    Mornin Steve,

    The spreadsheet would be open for the duration of each shift and updated by multiple users (2-3) simultaneously. The users will perform a physical task, then click yes in the "status" cell which check marks the "completed" cell, then select their name from the drop down "sign off" cell and then finally comment as necessary in the "comment" cells. Ideally, I would like a program to monitor the spreadsheet (possibly every hour) to check that tasks are being completed (completed, status and sign off cells all populated for past tasks) at scheduled times and if during that hourly check a previous task is not complete, an email reminder of these missed tasks would be sent to a DL in Outlook. The contents of that email should list the name(s) of the task(s) that were not completed at scheduled time(s). Finally, at the end of each shift, one of the staff would click a submit button that would email a copy of the completed spreadsheet to an Outlook user. This submit button should check to see that all shift tasks have been completed and if not generate a message box indicating such and not allow submission until all are complete. I hope this makes sense.

    Thanks,
    Kevin

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    kwelti,

    I have revised your worksheet to do checks on completed tasks at intervals that you can specify. If, at the specified interval time, prior tasks are not completed (status= Yes, signoff= selected employee) then an email is generated to the DL using an email address that you can select. The workbook will begin checks automatically when opened. I have currently coded for 40 task following your formatted rows but that is easily adjusted.

    Note: MS Outlook must be open prior to opening the workbook for the code to function properly.

    Screenshot of hidden "Incomplete Task" worksheet. Set time interval and DL email address. During testing I had it set for every 30 seconds (see image). You will need to unhide the worksheet and change cell H3 to 01:00:00 for every hour
    tasks1.png

    Screenshot of email sent to the DL every specified time interval
    task2.png

    I have revised your foumulas in column A only to show a check mark when both the status is Yes and signed off. It seems that was what you wanted for an alert to be sent. =IF(AND(D2="Yes",E2<>""),1,0) See row 5 in image
    task3.png

    If you indicate what times the shifts end, I can code the button to do a check and notify the user if there are still outstanding tasks. Are there tasks and a shift beyond midnight?

    HTH,
    Maud

    Update: I have added some newly acquired code to test if Outlook is currently running (Thanks to all that contributed). The code initiated from the workbook_Open subroutine was running too prematurely causing issues so a splash screen was added to slow things down.
    Attached Files Attached Files
    Last edited by Maudibe; 2014-06-29 at 09:55.

  5. #5
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow thank you so much Maud!!! The spreadsheet seems like it should operate exactly how I envisioned it... except that now I'm unable to test or implement because all of my machines are 64 bit and I'm getting a compile error stating the code has to be converted?

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Strange, the machine I built it on is 64bit Win7 Pro office 2010. Wonder if I convert it to an .xlb will you still have the same issue. Do you have an email address that I can send it to you. The forum will not let an .xlb upload. If so, you can give it to me by Private message.

    Maud

  7. #7
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok I PM'd you my email address. thx

  8. #8
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning Ted,

    I did receive the revised version and I think I will be able to get it to work on my machine. The error message I kept getting on compile said to add the "Ptrsafe" attribute to the declaration statements to "update" the code. The lines I changed were the first 2... Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
    Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName As Any) As Long
    I don't know what that means but when I did, the program stepped through that portion of the code successfully, which it hadn't done previous. Now, when I unhide the cells to adjust the email DL and time interval and I don't see any values in any cells, particularly H2 and H3. I'm unable to determine if that is where the code is looking and how to change it? Your instructions were pretty straightforward and I don't know why I'm not able to modify those values? I'm determined to get this to work!!!

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Kwelti,

    You are running the 64 bit version of Office. The steps that you had taken are correct by adding the PtrSafe keyword to the declarations of the Windows API calls. You should be unhiding a worksheet not cells. Right click any sheet tab at the bottom and select unhide from the context menu. A popup box will appear. Make sure that the "Incompleted Task" sheet is highlighted (there should be only one sheet listed). click OK. On this sheet you can see the parameters you can change as well as temporary holding area for the updated task list for the emails. I had hid his sheet so the employees would not know that there a changes that could be made.

    HTH,
    Maud

  10. #10
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok yes understood. I was able to get in and change those values and test the email function. It works fine when the workbook is maximized but when minimized the email doesn't generate. I'm thinking probably because the time value doesn't change in the workbook unless it's in use?

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Interesting! Does the Last time updated value change on the hidden page after minimized beyond the interval set? That will help narrow it down to an Excel vs Outlook issue. Glad it works well for you when maximized.

    Maud

  12. #12
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No it does not. The last time update on the hidden page shows 10:44AM which is the last time I was emailed. It has been open and maximized\minimized intermittently since but no more emails after 10:44 this morning.

  13. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Kwelti,

    Seeing if I can reproduce the same results. Will get back to you soon.

    Maud

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Kwelti,

    I have let the program function overnight (minimized) and all day so far (minimized and Maximized). I have received the hourly updates as expected. I also ran simultaneous spreadsheets that automate functions in Outlook. I was unable to duplicate the behavior. Could it have been a key combination from another program that halted the code or maybe some other function in Outlook running that created a conflict? Wondering if data entry in a cell at the same time that the code gets initiated to run by the timer would stop the code. The timer is reset for the next execution as it runs through its cycle. If the code execution is halted, the timer is not reset and you will not get the emails at the intervals set. I'm thinking that a button should exist to send an email upon request which would also serve as a mechanism to reset the timer. Also, some indicator to show that the next email has been scheduled.

    Try it again and if it stops, make sure that there is not an error state that halted code execution (error message box)


    Maud
    Last edited by Maudibe; 2014-07-03 at 13:33.

  15. #15
    New Lounger
    Join Date
    Jun 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was able to test somewhat successfully over the weekend. The function ran for several hours and then stopped in the afternoon. I'm trying to figure out what could have happened, possibly in outlook, to cause it to stop.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •