Trigger Email based on due date for inspection

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Spunkybum

Thread Starter
Joined
Nov 23, 2011
Messages
3
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
 

Spunkybum

Thread Starter
Joined
Nov 23, 2011
Messages
3
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.
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
Hi there, welcome to the board!

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

http://forums.techguy.org/business-applications/636786-set-email-reminder-base-excel.html

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.

Code:
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
                olMail.Send
            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.

HTH
 

Spunkybum

Thread Starter
Joined
Nov 23, 2011
Messages
3
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Top