Page 1 of 7 123 ... LastLast
Results 1 to 15 of 94
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically send email based on Excel cell values (Macro)

    Hey all, l’m new here and hope I can get some help with a macro that I have spent hours trying to get correct and have not had any luck. I have a workbook that tracks data from employees and what I need is an automatic email sent to HR when either of two columns (column AE and AF) reach a certain cell value. It will also need to send another email with any increases to the cell value after the starting value is reached but decreases to the cell value will not create an email. I have been trying to get this put together for some time now but I just can’t seem to figure it out so I’m asking any of you experts for some help. Any help would be greatly appreciated!!! Below is what I believe to be the necessary data and if I’ve missed something please let me know.

    Using Excel 2013
    Email is Outlook
    Column B is populated with employee names
    Column AE starting value = 4
    Column AF starting value = 40
    The two columns are populated with formulas, they are not a manual entry
    The worksheet title is “Summary” and this is the only worksheet in this workbook that the macro needs to be applied to.
    I would like, but not required, to have the email sent when the workbook is closed. It can be sent as soon as the cell is changed.
    The data collection starts on Row 7 but the last row fluctuates depending on the number of employees
    All emails will be sent to the same email address, let’s use hrmanager@companyabc.com

    Email Subject = “(Data in Column B) attendance”
    The body of the emails needs to read:
    “HR Manager,
    (Data in Column B) currently has (data in AE) tardies in the past year and needs to have his/her attendance reviewed.
    Thanks”

    Or

    “HR Manager,
    (Data in Column B) currently has (data in AF) unexcused hours in the past year and needs to have his/her attendance reviewed.
    Thanks”

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Welcome to the lounge.

    See my attached workbook.
    I have added named cells to set the limits for triggering the emails.
    In the vba routines, for testing I used
    .Display
    change this to
    .Send
    to actually send the emails
    Don't forget to set the correct email address in the vba

    zeddy
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Something to consider to augment Zeddy's nice piece of code would be to indicate if an email was previously sent along with being time stamped with a date and time. If a comment is inserted in the cells using code in Column AE when an email is sent, it can include a date and time for reference. Also if new tardies occur and you re-run the code, it can skip the tardies whose emails were sent the day before by looking if a comment exists.

    You can see this here:

    http://windowssecrets.com/forums/sho...l=1#post983120

    HTH,
    Maud

  4. #4
    New Lounger
    Join Date
    Jan 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thank You! This is great but it appears that it will only send the first occurrence in the list of employees, unless I'm doing something wrong. What do I need to do to make it send emails for the entire list of employees? Once I get that set up I will try and apply Maudibe's code because I do not want the same email being sent multiple times, unless a new tardy occurs. Thanks again, to both of you!

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    SA,

    I could not find any issues with Zeddy's code except for one very minor flaw. The named range countUnexcused points to AI5 instead of AH5 which gives a garbled message in the status bar.... otherwise sound.

    To add the comment with timestamp and then checks for it when it cycles through the list, change to the following lines in blue:

    Sub sendTardies()
    Code:
    For Each cell In Range(temp)
        zTardy = cell.Value
        If zTardy > zTardyLimit And cell.Comment Is Nothing Then
            cell.AddComment
            cell.Comment.Text Text:="Email sent " & Date
            zRow = cell.Row
            zName = Cells(zRow, "B")
    Sub SendUnexcusedEmails()
    Code:
    For Each cell In Range(temp)
        zHours = cell.Value
        If zHours > zLimit And cell.Comment Is Nothing Then
            cell.AddComment
            cell.Comment.Text Text:="Email sent " & Date
            zRow = cell.Row
            zName = Cells(zRow, "B")
    Tardies.png

    HTH,
    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-01-30)

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    ..thanks for fixing my named cell. I was 'moving' all the named cells to a 'spare' column to the right of the data (to make it easier for SA to incorporate). Missed that. On my system, it processed the emails so fast I never saw the messages in the statusbar.

    I like your use of cell comments in the updated code.
    Especially good as one person may trigger both emails!

    zeddy

  8. #7
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanks to you both

    I was looking for a solution to a similar problem and stumbled on to this. This is 90% of what I need, but I'm having trouble making it fit my application. I'm trying to poll a massive shared excel spreadsheet row by row for missing data in one particular cell (it would be blank), and then send the previous 6 cells in that row along with the row headers to an email address in the cell 7 rows previous. It's reminders for sites that haven't reported back corrective action on incidents, the previous rows are the demographics of the incident and the polled cell is where the corrective action SHOULD be. Basically, we're spending a lot of time clicking, copying, and pasting information into an email template, then pulling the email address based on the site ID and hitting send.

    I'm good with Excel, so I can shoehorn the data to make it fit whatever template I might find, but I just can't figure out exactly how to fix the macro. One challenge is that I think I need more variables to pull the data into the email, I was reading in the Help that you have to declare variables, and I'm not sure where to do it. And instead of testing the cell to see if it matches another, I just want to see if it's blank. zHours = 0 and zHour = "" both seem to not work. I suppose I could just filter out anything that isn't blank before I run the macro and skip that whole bit entirely...

    I attached some dummy data formatted the way mine is currently. In short, for each row, if H is blank, I want to send B through H (and the headers) to A. I imagine it would be pretty easy for anyone who knows this stuff to modify the posted macro to get something to work. Failing that, any guidance on how to proceed would save me a lot of additional research. I used to program in QBASIC and C++ years ago, so I'm not totally hopeless, I just don't know enough about the particulars of VB. Thanks in advance for any help!
    Attached Files Attached Files

  9. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    When you send an .xlsX file you automatically strip out all macros. Try sending your file with the macros.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  10. #9
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Supershoe View Post
    When you send an .xlsX file you automatically strip out all macros. Try sending your file with the macros.
    That might help, right? Sorry...

    I played with this a little during some down time and I think I have it about ready, but there's one gremlin I can't figure out. When I set it to .Display the emails, I can see it cycling through all of the emails. But when I set it to .Send the emails, it only sends the first one. Any idea how to get past this? Do I need to set a delay so Outlook can keep up? If I can get that done, I'm gold.

    Thanks for everything.

    (Important Note: I haven't gone through the trouble of updating all the comments, or even the name of the original Macro. I made my changes to the "sendUnexcusedEmails" macro. The only real change was the test of whether the macro would send the email from a greater-than-a-reference-cell to an equal-to-a-reference-cell (a blank one) statement. The rest is just pulling different cells and the body of the email. I didn't want to delete or change any of the other stuff for fear it would stop working.)
    Attached Files Attached Files

  11. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    It looks like you are talking about the tardies. I see no col G but if the macro and the data coincide maybe this is correct
    .to= c
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  12. #11
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post
    GOT IT!

    The problem is actually with the original macro. Minor fix, instead of putting the line of code "Set OutMail = OutApp.CreateItem(0)" towards the beginning, it needs to be inside the iterative process. I put it right after all the "strbody = strbody &..." definitions. Works like a charm. I think that if it's outside that process, it only creates one email and sends it, or continues to modify it in the case of the ".display" function. Inside the process, it creates a new email for each cell it encounters. Just a warning, at least for my purposes, that may mean it pops out hundred of emails at once, so if you're ".display"ing them, it may crash you. For the example attached, there's only 9 emails generated, not as big a deal. But unlike the original, it doesn't just change around the information on one email when you display it, it generates 9 separate emails.

    I've attached my fixed template. I still didn't clean up the comments, sorry. I've spent enough time on this today. Thanks again to the OP and to all the contributors, this is going to free up a lot of time for my people. Cheers!
    Attached Files Attached Files

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Dalanhurt,

    Also, one of your blank cells in col G has a space in it throwing you blank cell number from 10 to 9 in your sample

    HTH,
    Maud
    Last edited by Maudibe; 2015-04-02 at 19:57.

  14. #13
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    Dalanhurt,

    Also, one of your blank cells in col G has a space in it throwing you blank cell number from 10 to 9 in your sample

    HTH,
    Maud
    Good catch. I'll still take 9 out of 10 over putting together the reminders individually . I used this to send 105 reminders for March incidents in SECONDS today (Now I know I missed at least one...). But still, that would have been a boring day's work for my admin. And with your modification, someone might see that the email didn't get sent and investigate further. I'm not good enough to make the macro robust to the point it would catch a " " or a " ", so I'll probably handle it by just pre-screening the data a little better. Maybe sorting by the field, highlighting all the fields that APPEAR to be blank, and deleting them just to be sure.

    Anyhow, I went ahead and "cleaned up" the macro name, pulled out the unnecessary bits, and added comments for posterity. In case some future unfortunate is looking for a similar solution, they can see how I muddled through to make it work for my application. Can't say enough thanks to you guys for putting the original together.
    Attached Files Attached Files

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I have an updated sample file which uses the email address from column [A] as in your previous data sample.
    This new version uses an Excel Table.
    (To create a Table, start with your initial sample, select a cell in the header, and press Ctrl-L)

    I think a Table would be more useful for you.
    This update will also log when an email is sent (an idea that Maud suggested previously).

    I am away for the weekend.
    Any questions, post back.

    zeddy
    Attached Files Attached Files

  16. The Following User Says Thank You to zeddy For This Useful Post:

    HouseNerd (2015-05-05)

  17. #15
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 1 Time in 1 Post
    Looks like another good solution, probably more efficient than the one I landed on, and one that might be more helpful if someone else ever needs to borrow this solution for their own application. If I was more patient, I guess I could have just waited for you to build the better mousetrap. For my part, though, I don't mind interrogating every cell in the "target" column for blanks, since I'd imagine checking even 20,000 lines would only take a fraction of a second longer than only pulling the blanks in the first place.

    This is off-topic, except that I'm trying to add some bells and whistles to this. Is there a way to look up information from a SEPARATE reference excel spreadsheet at a static address, or failing that, from a separate worksheet within the workbook? I imagine a list that shows the terminal in one column, then a distribution list as text in the next column. Just working to improve the monster, it's just a little copying and pasting and a dummy tracker column in order to set up a "Send2ndreminder" and "Send3rdreminder" macro, so now I'm wondering how to build the escalating management tiers for each reminder. This is getting more academic, since the return on that kind of effort is minimal. I could just VLOOKUP the relevant distribution lists into a hidden cell on each row and pull them into the "cc" field in the macro, but I'd rather maintain everything elsewhere and have the macro pull it automatically. Also, I'd generally like to make this robust enough that I wouldn't have to rely on updating the macro as the months change and the links to current information are different. Similar problem, the only solution I can find is to throw the address into a hidden cell in the worksheet, rather than polling a reference based on the month. Now that I'm thinking about it, there's probably a way to just piece the addresses together using the date from the first incident and solve that problem. I'll stop thinking out loud. This has opened some doors, I'm hoping to start moving some of our processes out of the 80's and get my folks into more meaningful work.

  18. The Following User Says Thank You to dalanhurt For This Useful Post:

    HouseNerd (2015-05-05)

Page 1 of 7 123 ... LastLast

Tags for this Thread

Posting Permissions

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