Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sending emails and referencing specific cells

    Hi guys, first sorry english is not my own language. I have been gone mad and tried so many different things to try and get it working.
    I have a training database, and I need some VBA to send email to every employee that is out of date. Can I attach my workbook? It has instructions from my boss in it. I work in Excel 2010. Thank you.
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    So how do you know it's out of date? Check for a red color or what?

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Try this thread for suggestions.
    http://windowssecrets.com/forums/sho...om-Spreadsheet

    cheers, Paul

  4. #4
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The cells are red if they are late yes

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..after the emails have been sent, I suggest the red cell be changed to a pink cell (to stop another email being sent again), and a cell comment added to the cell to show the date/timestamp when the email was sent.

    zeddy

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    I didn't put in the email but his a basic start for you.
    I'm not an excel person but this does work.
    Sub Button1_Click()
    cols = 19 ' How many columns to head over
    lastrow = 19
    For i = 2 To lastrow
    Cells(i, 1).Select
    Name = ActiveCell
    If Len(Name) < 1 Then ' I should have more than a len of one, else everybody out of the pool
    Exit Sub
    Else
    For x = 1 To cols Step 1
    Cells(i, x).Select
    If x = 1 Then
    fname = ActiveCell.Value
    End If
    If x = 2 Then
    lname = ActiveCell.Value
    End If
    If x = 4 Then
    Email = ActiveCell.Value
    MsgBox "this is " & fname & " " & lname & " " & Email
    End If
    adate = ActiveCell
    If Cells(i, x).Interior.Color = RGB(255, 0, 0) Then
    MsgBox "Got Red"
    ' build your email here
    End If
    Next x
    End If
    Next i
    End Sub ' Okay, everybody out of the pool

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

    The attached revised workbook will cycle through each row and check the training dates for each training event. If the interior color is red then it will generate an email with the requirements you stated as well as insert a comment that the email was sent. If you like, I can add some additional code to prevent you from sending an email a second time if you run the code at a later date. I am not sure as you may want to send a second email.

    HTH,
    Maud

    Tabby1.png

    Tabby2.png
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-16 at 00:04.

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

    tabbytomo (2015-05-18)

  9. #8
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Maudibe, you recently helped me with my spreadsheet and my boss is very happy! thank you! I wondered if you could show me way to send only one email if a person has multiple reds, rather than one for each red? I don't know if possible.
    Last edited by tabbytomo; 2015-05-18 at 04:40. Reason: boss asked one more thing

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Separate email for each training which is out of date (if an employee is out of date on two types of training, receive two emails - one for each)
    Tabby,

    Sorry if I misunderstood your request. The revised workbook will send only one email to the employee if multiple trainings are out of date. A comment in the red cell will indicate when the email notification was sent.

    Please note: in the future, if you choose to use conditional formatting to turn the cells red when the training expires, the coded will need to be adjusted. The base color of cell does not change even if the cell color changes with conditional formatting. This code looks for at the base color (red in this instance) to create the email.

    HTH,
    Maud
    Attached Files Attached Files

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

    tabbytomo (2015-05-18)

  12. #10
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    This is the best! thank you very much Maudibe!

Posting Permissions

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