Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to use a date field within my spreadsheet as notification

    Hello,

    I would like to know if anyone can help me solve a problem I have at the moment.

    I have created a spread sheet, which automatically calculates 90 days in advance.

    I would like to know how do I get the spread sheet to notify me, when the computer date reach's the 90 days date.

    I have no idea about macro's or anything like that....

    I would like an email to be sent to a couple of people if that is possible, i have attached the basic spread sheet incase its required to be viewed.

    Any assistance would be greatly appreciated.

    thank you in advance
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    77
    Thanks
    9
    Thanked 1 Time in 1 Post
    You can highlight the cell (in many ways) using conditional formatting. Look at the example of your spreadsheet, I have made the 3 MTHS DATE cell change colour when today or later is reached.
    To customise it to your liking, click on the Start tab then under Styles click on Conditional formatting.
    Click on Manage Rules then click on the rule and then on Edit Rule.
    Here you can see the formula enterd (for future reference) and next to the Preview is a button - Format. Click on that to change the Style of how you want to cell to look when today or later is reached.
    I also hidden the dates for unused cells to tidy the sheet up a little so if you enter a date in the TOMS RXC column then the future date will appear.
    Let me know if I am not making any sense
    Attached Files Attached Files

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,178
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Muddy,

    Welcome to the Lounge as a new poster!

    You can use conditional formatting to turn the dates in Col G red when they are >= the date calculated.
    Conditional Formatting Formula for Col G: =TODAY()-$G4>=0
    CondFmt.JPG
    Note: I changed your date for illustration (highlighted in yellow). You can use the format painter to copy the format down the column or use the Rule Manager to change the range it applies to. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #4
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alan/retired

    Thank you for the input I have adopted your suggestions.

    Muchly appreciated.

    The spread sheet wont be used by me, its going to be used by an employee.

    The original request.
    (I would like an email to be sent to a couple of people if that is possible),

    One person would be me and my accountant, to make sure the staff member is dealing with the property when the date in column G is reached.

    I have also heard about Excel being able to make an entry in outlook calender. which I can sent a rule in to email.

    either of the above is acceptable.

    As you can appreciate, We have had to instigate major changes with the company to automate different things after a staff member didnt perform the work requested by the due date.

    hence the need to be notified of the date in column G

    thank you

  6. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    231
    Thanks
    0
    Thanked 11 Times in 11 Posts
    Muddy:

    Can you be a bit more specific. For Excel to assist you would need to open the workbook every day so that the workbook would compare the due date and the current date. This could be done by using Excel's Auto Open routines. However this still requires someone to open Excel. What I mean is if someone is away for several days and does not Open Excel you have no protection.

    However, if you are using Outlook putting a reminder in Outlook Calendar with a one click "Send out E-Mail" could all be done and skip the Use of Excel. Outlook supports Macro's.

    Regards,
    Tom Duthie

  7. #6
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The workbook or spread sheet would be opened every day. The employment position is filled everyday.

    I like the idea of having the Excel spreadsheet opening automatically each time the computer starts.

    The workbook or spread sheet is updated everyday.

    I really need a automated way of sending an email or making a outlook calender entry.

    If you want to play around with my Workbook/Spreadsheet please use Alan's updated version as I have adopted the alterations he has made.

    I have heard about excell being able to put an entry in the outlook calender.

    I can then setup outlook to look at calender and send email.

    Or if Workbook /spreadsheet can send an automated email to a couple of people

    I am looking at whatever is the easiest to setup.

    thank you

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You can trigger it with a routine that looks at the dates when the file is opened.
    Code:
    Option Explicit
    Private Sub Workbook_Open()
      Dim lLastRow As Long
      Dim lRow As Long
      Dim iColDate As Integer
      Dim iCol3Mon As Integer
    
      iColDate = 3 'Col c
      iCol3Mon = 7 'Col G
      
      With Worksheets("Sheet1")
        lLastRow = .Cells(.Rows.Count, iColDate).End(xlUp).Row
        For lRow = 4 To lLastRow
          If .Cells(lRow, iColDate) <> "" And _
            .Cells(lRow, iCol3Mon).Value <= Date Then
              MsgBox "Date hit - your macro runs from row " & lRow
          End If
        Next
      End With
    End Sub
    You would put the code you create to email the message where the messagebox is. To send an email look at the code here: http://www.rondebruin.nl/win/section1.htm. You don't ahve enough information for providing more details about the email message.

    Steve

  9. #8
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SDCKAPR

    Thank you for the reply.

    I have looked at the code and let me tell you I am totally confused.

    The message in the email would be ( 90 days has elapsed property is needs to be Disposed of).

    I have looked at http://www.rondebruin.nl/win/section1.htm.
    Talk about confusing. I have no idea what it all means.

    Where would I put my email address to send email to. Multiuser@westnet.com.au - Clayh@westnet.com.au

    Would it be easier to put a calender entry into outlook. I know how to send an automatic email from a calender entry.

    I think this is all beyond me, I might need to find someone to set it up for me.

    Anyway thank you for trying to explain or show me what needs to be done.

    Smiling

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The question for us when when help write the code is not where you put the email addresses, but where will the code find them. Is it just 1 email address, will there be a different email address (or address) for each date? What else will be in the email, etc, etc. What I provided was sample code to email and a method for the workbook to get the appropriate "trigger". If you need further help from us, walk us through what you want to happen when that trigger is pulled (and we need details)....

    Steve

  11. #10
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve

    OK. I have already given you the 2 email address in previous postings.

    The message in the email would be ( 90 days has elapsed, property needs to be Disposed of).

    The idea is when the date is entered in column c, it automatically calculates 90 days and post the date in column G

    When column G reaches the present date, I would like the spread sheet to automatically send off an email to both me and my accountant.
    Multiuser@westnet.com.au - Clayh@westnet.com.au

    I only want 1 email sent for each date line in column G, I have re posted the spread sheet again Version 13.... made a few mistakes trying to use the code you sent me

    If you look in Column G you will see there is 4 dates, one is highlighted in yellow, which means the date has been reach. (email required to be sent on that date)
    You will also notice 3 other dates below, which are in the future, when the dates match the computer date (present date) the date will change yellow and should send off an email.

    As you can appreciate there will be a lot of email for the amount of lines in column G, I already know this. I only want 1 email sent to both parties for each line in column G

    I hope I have explained it better.

    Thank you steve for taking the time and having the patience with me.
    Attached Files Attached Files

  12. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Shelter Island, NY, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Greetings!

    Maybe use the Task Scheduler, that invokes a batch file, that invokes the Excel spreadsheet with an Auto Open macro, which creates a file depending on the date criteria, which the batch file will check for its existence. Haven't figured out how to get an email generated and sent.

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You can try this, it uses sendmail. The subject of the email will be "90 days has elapsed, property needs to be Disposed of" and the file will be attached. That is about the limit with sendmail. (to and subject, with attached workbook, sendmail has no "body" to use)

    Code:
    Option Explicit
    Private Sub Workbook_Open()
      Dim lLastRow As Long
      Dim lRow As Long
      Dim iColDate As Integer
      Dim iCol3Mon As Integer
    
      'change these as needed
      iColDate = 3 'Col c
      iCol3Mon = 7 'Col G
      With Worksheets("Sheet1")
        lLastRow = .Cells(.Rows.Count, iColDate).End(xlUp).Row
        For lRow = 4 To lLastRow
          If .Cells(lRow, iColDate) <> "" And _
            .Cells(lRow, iCol3Mon).Value <= Date Then
              Me.SendMail Recipients:=Array("Multiuser@westnet.com.au", "Clayh@westnet.com.au"), _
                Subject:=" 90 days has elapsed, property needs to be Disposed of"
          End If
        Next
      End With
    End Sub
    More options could be done if you had EXCEL vba send an email using outlook. This code sends it to the 2 emails, with the subject line: "90 days has elapsed, property needs to be Disposed of" and the Body lists the name, file num, and description (other options are available) of the property.
    Code:
    Option Explicit
    Private Sub Workbook_Open()
      Dim OutApp As Object
      Dim OutMail As Object
      Dim sBody As String
      Dim lLastRow As Long
      Dim lRow As Long
      Dim iColDate As Integer
      Dim iCol3Mon As Integer
      
    'change these as needed
      iColDate = 3 'Col c
      iCol3Mon = 7 'Col G
      With Worksheets("Sheet1")
    
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
        lLastRow = .Cells(.Rows.Count, iColDate).End(xlUp).Row
        For lRow = 4 To lLastRow
          If .Cells(lRow, iColDate) <> "" And _
            .Cells(lRow, iCol3Mon).Value <= Date Then
            sBody = "Name: " & .Cells(lRow, 1) & vbNewLine & _
                "File No: " & .Cells(lRow, 2) & vbNewLine & _
                "Description: " & .Cells(lRow, 4)
            On Error Resume Next
            With OutMail
              .to = "Multiuser@westnet.com.au; Clayh@westnet.com.au"
              .Subject = "90 days has elapsed, property needs to be Disposed of"
              .Body = sBody
              .Send
            End With
            On Error GoTo 0
          End If
        Next
      End With
      Set OutMail = Nothing
      Set OutApp = Nothing
    End Sub
    Steve

  14. #13
    New Lounger
    Join Date
    Mar 2014
    Location
    Western Australia
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Steve,



    Firstly I never considered both options which you clearly thought of.

    Thank you for taking the time and having the patience to help me out, due to myself having limited or no knowledge.

    I will return to read how to insert or place the code into the worksheet, surely I cant stuff that up, but then again. look at the version titles for the amount of time I have had to change it. (laughing).... all good , they say that how you learn by making mistakes and being able to laugh at yourself.

    Either way to all those that have spent time helping me.. I extend my appreciation to you All.

    THANK YOU

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The code goes into the ThisWorkbook object (not a regular module) and will run whenever the workbook is opened.

    Steve

  16. #15
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,197
    Thanks
    45
    Thanked 227 Times in 210 Posts
    Muddy,

    Just drop the excel spreadsheet in your startup folder and it will open automatically when you boot your computer

Posting Permissions

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