1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Automatic email from excel based on date

Discussion in 'Business Applications' started by chad_hunt, Jun 7, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    I have a personnel tracker that tracks required documents that need to be renewed every year. There are 4 of these and each has a seperate column for each date. They are J,L,T and U.

    I would like excel to scan each of these columns every time the document is opened for any of those dates that are 60-65 days from expiring, I use Now()-300 to check this.

    But if the cell is blank I want it ignored, which right now it does not. If it finds 1 or even 80 that are in that range I want 1 email generated to a list of management personnel.

    Currently it generates how ever many are blank or expiring. I use outlook.

    My data starts on row 3 and goes down to 331, the required and maximum number of people allowed on the contract.

    If I can get just one email with a generic message I would be happy. But if I could I would like the info that is actually doing the triggering to be copied and pasted in the email to include the info in A, C and D.

    Here is my current code, I would change .display to .send once I have it working. please help.

    Private Sub Workbook_Open()
    Dim Cell As Range
    Dim DateRng As Range
    Dim Msg As String
    Dim olApp As Object
    Dim olEmail As Object
    Dim RngEnd As Range
    Dim Wks As Worksheet

    Set Wks = Worksheets("Work Site Info")

    Set DateRng = Wks.Range("J3")
    Set RngEnd = Wks.Range("J331")
    Set DateRng = IIf(RngEnd.Row < DateRng.Row, DateRng, Wks.Range(DateRng, RngEnd))

    'Change this to what you want.
    Msg = "This is message is the body of the email."

    For Each Cell In DateRng.Columns(4).Cells
    If Cell < Int(Now() - 300) Then
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    Set olEmail = olApp.CreateItem(0)
    With olEmail
    .To = "my email"
    .Subject = "Expiration notice"
    .Body = Msg
    .Display
    End With

    End If
    Next Cell

    Set olApp = Nothing

    End Sub
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Code:
    For Each Cell In DateRng.Columns(4).Cells
    [B][COLOR="Red"]if len(trim(cell.value)) <> 0 then[/COLOR][/B] 
    If Cell < Int(Now() - 300) Then
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    Set olEmail = olApp.CreateItem(0)
    With olEmail
    .To = "my email"
    .Subject = "Expiration notice"
    .Body = Msg
    .Display
    End With
    End If
    [B][COLOR="red"]End If[/COLOR][/B]
    Next Cell
    

    I added the two bold red lines, if the cell is not filled (length of the trimmed value <> 0) then it does the following test else skips to the next cell.
     
  3. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    Wow, thanks for the fast reply!! I will test it out now!
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Just holler if you need moer help.
    Happy to help
     
  5. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    well it was a no go, just did nothing. I have J4 and J5 with nothing in them and J6 with a date that is set to expire and no email generated.
     
  6. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    could I shoot you my workbook by chance?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    No problem, just make sure it doesn't contain meaningful company or private data
     
  8. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    Here is the work book, I will actually store the to: email addresses on the 2nd tab in column A so as management changes it is easier and faster to update. Also it currently is only checking column J but need it to check J, L, T and U. Thank you so much for the help and fast reply!
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Try changing the code line:

    Code:
    If Cell.Value - Date <= 65 Then
    
    
    instead of

    If Cell.Value < Int(Now() - 300) Then

    It triggers Outlook here
    All you have to do now is se the difference to the desired value.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    You see, what Int (Now() - 300) does is not return a number of days

    The INT (Now() - 300) is another date
     
  11. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    Cool, that part worked, now how do I get it to check all 4 of the columns and is there a way to put that into one email instead of numerous ones opening up per each case found?
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I only see 3 columns, J, K and L ?
    You want only one mail message per row which is to be triggered by either J, K or L (or the 4th one) ?
    I'm shutting down here but will be online later this evening to follow-up.
     
  13. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    Only one email total, the trigger columns are J, L, T or U. So lets say after the code runs and checks these 4 columns. it sends 1 email to the contacts. It might be easier to have the code stop at the first find and send the email but ultimatly I would like it to scan all 4 columns and paste the persons name with what is expiring in the email but for now I will take what I can get. Again thank you so much for the help.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I saw that I didn';t read the post correctlywhen you first attached your sheet sorry.
    Will try it this evening
     
  15. chad_hunt

    chad_hunt Thread Starter

    Joined:
    Jun 7, 2010
    Messages:
    13
    No worries, thanks again! I am almost done for the evening so I will check back tomorrow.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/927675

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice