[sonson411]

I have a list of task (maintainance schedule) to do... and they are required to be done in a by certain different dates regularly (eg. every 14 days or every 28 days)

I have a specific maintance date for each task within the same row. Is there anyway i can make it to automatically send an email to remind me or another person a day before it's due??

thanks

[Zack Barresse]

Hi there, welcome to the board!

Of course there is a way. It would be heavily laden with VBA though. One question: why are you not using Outlook? It has a calendar and a task list which will take care of most of this already. I don't know about automatic emails, but you could invite attendees and email them a copy of the appointment you set on the calendar. Set the reminder on it and they'll get a reminder as well.

HTH

[sonson411]

Thanks for the reply.

I actually considered that using Outlook. However, I use excel becoz beside keeping track of the schedule dates, I also have other information related to each task (maintance schedule date is just one of the information related to the task).

:-)

[Rollin_Again]

Sonson can you post a sample workbook showing what your data will look like in Excel?

Outlook and Excel and can work together to accomplish your task. As previously mentioned by Firefytr this would involve using VBA code that can be stored in your Outlook Session. Now we just need to find someone who knows some VBA. Zack, do you know anyone who tinkers with VBA code?


Regards,
Rollin

[Zack Barresse]

I know a couple people Rollin'.

[sonson411]

Here's the excel file showing how it looks like. I added some comment onto couple cells explaining how i wanna work it out.

Great thanks!!

[Aj_old]

I thik there are 2 ways to do this
1. When U open Ur workbook automaticaly (or not serches) the R column for values that are 1 day biger that Today(), and after that send emails
2 at the moment when U introduce tha dat in Ur spread sheet it creates tasks in outlook and if needed sentd them through email

Wich of this way U choose?

[Zack Barresse]

We need to know when you would like this to 'fire'. What do you want to set off a routine to do this? There is the OnTime function, or Windows Scheduler (which I prefer, as it is less prone to errors and external from Excel, thus independent). What would you like? If you do not want to use an event (i.e. on file open, specific cell change, button click perhaps), what time would you like this run at? Will the computer be on all the time? Would you like to have a message box ask you if you want to run it? We need to know because it may change how this is coded quite dramatically.

[Rollin_Again]

I agree with Zack and recommend using Windows built in scheduler to call a script that will open the workbook and check the dates before taking action. Just out of curiousity why are you not using a database program such as Access to store your data? Your would have greater flexibility such as the ability to query the database with specific search criteria and/or the ability to create custom reports.

Regards,
Rollin

[Aj_old]

I have some ideas how it can be done but I need more info about how U wanna it be donne

[Zack Barresse]

Also, if you want to do this automatically, Outlook will not send without human interaction unless you have a 3rd party application installed which lets you do this. We can do this, if you want to use windows scheduler and a VBS file, with Express ClickYes (look here). If you want to use this we can alter/customize this app via VBA as well, so it will tie in nicely. Plus we don't need much code.

[Zack Barresse]

Okay, just as a preliminary, you could use a VBS file with the following code (I'll upload a sample of the file as well, it would need to be edited via Notepad)...

'/// Declare variables
Dim xlApp
Dim wb
Dim ws
dim blnOpened
dim sSep
dim blnAppOpen
dim blnWbOpen
dim sPath
dim sName

'/// Check for application existence, create
on error resume next
set xlapp = getobject(, "Excel.Application")
blnAppOpen= True
if xlapp is nothing then
    set xlapp = createobject("Excel.Application")
    blnAppOpen= False
'else
'    xlapp.visible = false
end if
xlapp.displayalerts = false
ssep = xlApp.pathseparator

'/// Open Workbook
'//////////////////////////////////////////////////////////////////////////////
'// DECLARE VARIABLE(S) HERE //////////////////////////////////////////////////
sPath = "C:\Documents and Settings\Zachary\Desktop\"
sName = "Mechanic Equipment Schedule Summary1.xls"
'//////////////////////////////////////////////////////////////////////////////
set wb = xlApp.workbooks(sName)
blnWbOpen = True
if wb is nothing then
    set wb = xlApp.workbooks.open(sPath & sName)
    blnWbOpen = False
end if

'/// Run routines: refresh pivot table, save as PDF
xlapp.run "CheckEmailStatus"

'/// Quit
if blnWbOpen = False then wb.close
xlapp.visible = true
xlapp.displayalerts = true
if blnAppOpen= False then xlApp.quit

Then in your workbook, in any standard module...

Option Explicit

Const NL As String = vbNewLine
Const DNL As String = vbNewLine & vbNewLine
Const StrDateFormat As String = "m/d/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("Sheet1")
    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("R4", ws.Cells(ws.Rows.Count, "R").End(xlUp))
        strMsg = "This is just a friendly reminder!" & DNL
        strMsg = strMsg & "Tag #: " & ws.Cells(c.Row, "I").Text & NL
        strMsg = strMsg & "Description: " & ws.Cells(c.Row, "K").Text & NL
        strMsg = strMsg & "Equipment Type: " & ws.Cells(c.Row, "L").Text & NL
        strMsg = strMsg & "Receive Date: " & Format(ws.Cells(c.Row, "N").Value, StrDateFormat) & DNL
        Select Case c.Value - Date
        Case Is > 0    'next service date is after today
            strMsg = strMsg & "Next service date is on " & Format(c.Value, StrDateFormat) & "."
        Case Is = 0    'next service date matches today
            strMsg = strMsg & "Next service date is today!"
        Case Is < 0    'next service date is before today
            'do nothing, date has passed
            strMsg = ""    'clear message
        End Select
        If strMsg <> "" Then
            Set olMail = OL.CreateItem(0)
            olMail.To = "to@domain.com"
            olMail.Subject = "Subject"
            olMail.Body = strMsg
'            olMail.Display 'for testing purposes only
            olMail.Send
        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

Seems to work upon (light) testing.

[Aj_old]

Hi Zack!
Wouldn't it be easyer to do this from the excel file, to create and send e-mails through Outlook. In the references chek the box for MS Outlook Library, and U can create emails and send them from excel!

[Zack Barresse]

Pretty sure that's what I did via late binding. The only difference was to setup a VBS file to give the ability to set it to the windows scheduler. This ensures it running at the same date/time specified, everytime.

[sonson411]

great thanks!!!
it works perfectly.. only one little thing, is there a way to get rid of the warning from outlook express? everytime, it runs, Outlook shows, "a program is trying to automatically send email on your behalf" something like that.. so i'll have to click yes for every message to send