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.

Trigger Email based on due date for inspection

Discussion in 'Business Applications' started by Spunkybum, Nov 23, 2011.

Thread Status:
Not open for further replies.
  1. Spunkybum

    Spunkybum Thread Starter

    Nov 23, 2011
    I have created a spreadsheet to register equipment and record last inspection date. This date is a constant as all inspections are carried out at the same time. I then applied a simple formula to calculate the next due date, this is either 12 months; 6 months or 3 months.
    14 days prior to the next due inspection the cell adjacent to the Next Due Date becomes Red and reads "TEST"
    I need this event to trigger an email to various recipients but the spreadsheet will rarely be opened. From what I have read so far, I should create an event in Scheduler that will open the spreadsheet then trigger an email to the required recipients.
    The problem is that I have no idea where to start.
    Any help will be greatly appreciated.
    Serial NumberREFERENCE STANDARDLast Inspection DateDescriptionLocationNext Inspection DateStatus16433-1Visual Inspection19/4/11Wire rope sling 16mm x 3 metre H/S IWRCContainer18/4/12OK
  2. Spunkybum

    Spunkybum Thread Starter

    Nov 23, 2011
    I seem to have had a lot of views but no assistance yet so I thought it may be helpful to attach an excerpt from the Workbook I am trying to automate.
    Thanks in Advance.

    Attached Files:

  3. Zack Barresse

    Zack Barresse

    Jul 25, 2004
    Hi there, welcome to the board!

    This question seems to come up a lot. A board search could have yielded you these results:


    This is pretty much what you're asking to do.

    Do you have any code so far? Will you be sending one email out per record which states TEST? Or would you like to send out one email if there is a TEST anywhere, regardless of how many there are? When you send out an email, do you want to change the date of the Last Inspection Record to the current date?

    Assuming you want to utilize one separate email per TEST record, you might be able to use this as your VBA code. As far as the VBS code, you should be able to use the code in the other link and you just need to change the path and file name. It also assumes you want to put the information from that record into the email. You'll need to specify the recipient email address and subject. And reading from the other thread, it assumes you'll be utilizing the free program ClickYes.

    Option Explicit
    Const NL                            As String = vbNewLine
    Const DNL                           As String = vbNewLine & vbNewLine
    Const strDateFormat                 As String = "d/m/yyyy"
    Sub CheckEmailStatus()
        Dim OL As Object, olMail As Object, objwShell As Object
        Dim ws As Worksheet, c As Range, strMsg As String, blnCreated As Boolean
        Set ws = ThisWorkbook.Sheets("Master")
        Set objwShell = CreateObject("wscript.shell")
        On Error Resume Next
        Set OL = GetObject(, "Outlook.Application")
        blnCreated = False
        If OL Is Nothing Then
            Set OL = CreateObject("Outlook.Application")
            blnCreated = True
        End If
        objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
        For Each c In ws.Range("H5", ws.Cells(ws.Rows.Count, "H").End(xlUp))
            If c.Value = "TEST" Then
                strMsg = "This is just a friendly reminder!" & DNL
                strMsg = strMsg & "S/N: " & ws.Cells(c.Row, "B").Text & NL
                strMsg = strMsg & "Reference Standard: " & ws.Cells(c.Row, "C").Text & NL
                strMsg = strMsg & "Last Inspection Date: " & Format(ws.Cells(c.Row, "D").Value, strDateFormat) & NL
                strMsg = strMsg & "Location: " & ws.Cells(c.Row, "F").Value & NL
                strMsg = strMsg & "Description: " & ws.Cells(c.Row, "E").Value & DNL
                If strMsg <> "" Then
                    Set olMail = OL.CreateItem(0)
                    olMail.To = "[email protected]"
                    olMail.Subject = "Subject"
                    olMail.Body = strMsg
        '            olMail.Display 'for testing purposes only
                End If
                ws.Cells(c.Row, "D").Value = VBA.Date()
            End If
        Next c
        objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -suspend")
        '    objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop") 'optional to stop the service altogether
        If blnCreated = True Then OL.Quit
    End Sub
    Post back the answers to the questions and any others you may have.

  4. Spunkybum

    Spunkybum Thread Starter

    Nov 23, 2011
    Thanks Zack,

    I'll give this a try, as far as the records go, I have set up the Management Strategy so that I only have one "Test" date for each equipment type. This way I only have 4 test dates to worry about. (4 Equipment types)
    I don't want the next test date automated as I need to have some management interaction here.
    Unfortunately, working on a Corporate Network, I am unable to install any software on any workstations but the warning message is not a big issue for me in this instance.
    I'll let you know how I go.
    Regards and Thanks.
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!

Thread Status:
Not open for further replies.

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

  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