# Set email reminder base on Excel



## sonson411 (Oct 11, 2007)

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 (Jul 25, 2004)

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 (Oct 11, 2007)

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 (Sep 4, 2003)

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 (Jul 25, 2004)

I know a couple people Rollin'.


----------



## sonson411 (Oct 11, 2007)

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 (Sep 24, 2007)

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 (Jul 25, 2004)

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 (Sep 4, 2003)

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 (Sep 24, 2007)

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


----------



## Zack Barresse (Jul 25, 2004)

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 (Jul 25, 2004)

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 = "[email protected]"
            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 (Sep 24, 2007)

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 (Jul 25, 2004)

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 (Oct 11, 2007)

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


----------



## Zack Barresse (Jul 25, 2004)

Oh! The solution I posted, using ClickYes, will get rid of that, although it will not work with Outlook Express. If you used Outlook, and I'm not sure how it is not, as that is the application created via code, it would suppress it. Are you _sure_ you have Outlook Express and not just Outlook? If you click Help.. About, what does it say? Also, if you do have Outlook, download/install Express ClickYes (free via the link I posted in post #11).

HTH


----------



## Aj_old (Sep 24, 2007)

Zack what would U say about such an aprouch:


```
Sub EmailfromExcel()
nr = 0
SendToList = "[email protected]"
    For Each c In Range("R4", Cells(Rows.Count, "R").End(xlUp))
        If c.Value - Date = 1 Then
        nr = nr + 1
            strMsg = "This is just a friendly reminder!" & Chr(10)
            strMsg = strMsg & "Tag #: " & Cells(c.Row, "I").Text & Chr(10)
            strMsg = strMsg & "Description: " & Cells(c.Row, "K").Text & Chr(10)
            strMsg = strMsg & "Equipment Type: " & Cells(c.Row, "L").Text & Chr(10)
            strMsg = strMsg & "Receive Date: " & Format(Cells(c.Row, "N").Value, StrDateFormat) & Chr(10)
            strMsg = strMsg & "The due date is tommorow:  " & c.Value
            Set myOlApp = CreateObject("Outlook.Application")
            Set myitem = myOlApp.CreateItem(olMailItem)
                myitem.Display
                myitem.To = SendToList
                myitem.Subject = "Next control is on " & c.Value
                myitem.Body = strMsg
                'myitem.Display
                myitem.Send
        End If
    Next c
    'display a mesage box in wich is sayd how many email were sent and to whom
MsgBox ("You have " & nr & " tags that have the due date tommorow, for all of them were sent email" & _
        " notifications to :" & SendToList)
End Sub
```
I used a part of U code, and modified it
I tested it on my sistem and it creates and send email automaticaly!


----------



## Zack Barresse (Jul 25, 2004)

Aj_old, a couple of things I don't like, 1) creating the application object in each iteration (as mentioned in the other thread as well), 2) message boxes, if automation is desired, message boxes mean human interaction and not fully automated, 3) not using clickyes and displaying the message, again, human interaction, 4) not using windows scheduler or ontime call of any kind, thus keeping this routine stagnant without human interaction (unless coupled with the VBS file), 5) will create a new instance of Outlook everytime, even if one is created, which seems a waste of resources IMHO, 6) only checks for one instance of date differential, which seems slightly lacking IMHO, 7) if the VBS file is _not_ used, quite a bit of functionality is taken away, with it, Excel does not even need to be open and it will still run.


----------



## Aj_old (Sep 24, 2007)

Hi Zack!

The message box is displayed when all the work is allready done, just to inform U abut it!
I used something like this for about half an year to send letters every evening and it work fine for me, thats why I posted it! If this is not the best way to do this, than it's still an option!


----------



## Zack Barresse (Jul 25, 2004)

Aj_old said:


> The message box is displayed when all the work is allready done, just to inform U abut it!


I saw that.


Aj_old said:


> I used something like this for about half an year to send letters every evening and it work fine for me, thats why I posted it! If this is not the best way to do this, than it's still an option!


My only preferences are those listed. Oh, the only other one I'd add is your variables aren't declared. Again, personal preference. But I'll recommend everything I've mentioned still.


----------



## Aj_old (Sep 24, 2007)

OK I'll try to keep this in mind!
Thanks for U "critics" it helps me to became beter, cause it shows my imperfection, an give me reasons to work at!


----------



## Zack Barresse (Jul 25, 2004)

Aj_old said:


> OK I'll try to keep this in mind!
> Thanks for U "critics" it helps me to became beter, cause it shows my imperfection, an give me reasons to work at!


Likewise! :up:


----------



## JSOMER2000 (Oct 26, 2007)

This is something I am looking for - I am a complete novice when it comes to code in excel.

I have a basic spreadsheet where it tells me when a job is overdue on a certain day this is done by some formulas in the spreadsheet.

I see from your thread that your program can automatically email you when a job is overdue. I see that you have a script using windows installer - Do you know can how I can do this?

Nick


----------



## Zack Barresse (Jul 25, 2004)

Hi there Nick, welcome to the board!

I suggest you start your own thread. If you're directly looking at the information in this thread, put a URL link to this thread in your post.


----------



## sonson411 (Oct 11, 2007)

The code that i was using is awesome...
however, one day it came back to bite my back...
coz i just realize that suddenly there's around 80 item is going to be over due...

so instead of sending 80+ individual emails, how would i make them all into one single email??

thank you so much


----------



## Zack Barresse (Jul 25, 2004)

Sure. These are all going to be to the same email address and all? If you want the information in the email combined, how do you want each record item combined? Just on a new line or something? Can you zip/upload your latest file version with data (mock if need be)?


----------



## sonson411 (Oct 11, 2007)

firefytr said:


> Sure. These are all going to be to the same email address and all? If you want the information in the email combined, how do you want each record item combined? Just on a new line or something? Can you zip/upload your latest file version with data (mock if need be)?


here's my VBA code within my excel:


> Option Explicit
> 
> Const NL As String = vbNewLine
> Const DNL As String = vbNewLine & vbNewLine
> ...


Having the information in new lines would do the job:
for example:


> Tag #: 142-PU-416
> Description: Pump 416
> Equipment Type: Pump
> Activity Code: 1, 4, 5
> ...


----------



## Zack Barresse (Jul 25, 2004)

Changing the email to outside the loop and slightly manipulating the *strMsg* variable should work...

(_untested_)

```
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 ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -activate")
    For Each c In ws.Range("AS10", ws.Cells(ws.Rows.Count, "AS").End(xlUp))
        strMsg = strMsg & "Tag #: " & ws.Cells(c.Row, "Q").Text & NL
        strMsg = strMsg & "Description: " & ws.Cells(c.Row, "R").Text & NL
        strMsg = strMsg & "Equipment Type: " & ws.Cells(c.Row, "T").Text & NL
        strMsg = strMsg & "Activity Code: " & ws.Cells(c.Row, "AP").Text & NL
        Select Case c.Value - Date
        Case Is = 3    'next service date matches a day after today
            strMsg = strMsg & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 2    'next service date matches a day after today
            strMsg = strMsg & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 1    'next service date matches a day after today
            strMsg = strMsg & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 0    'next service date matches today
            strMsg = strMsg & "Next service date is today!" & DNL
        Case Is < 0    'next service date is before today
            strMsg = ""    'clear message
        End Select
    Next c
    If strMsg <> "" Then
        Set olMail = OL.CreateItem(0)
        olMail.To = "[email protected]"
        olMail.Subject = "Preservation Date"
        olMail.Body = strMsg
        ' olMail.Display 'for testing purposes only
        olMail.Send
    End If
    objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -suspend")
    ' objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -stop") 'optional to stop the service altogether
    If blnCreated = True Then OL.Quit
End Sub
```


----------



## Zack Barresse (Jul 25, 2004)

Holy crap! Man, I tried to edit my post, and look at this.. Okay, I had an adjustment (I thought it was going to be easy to adjust my post), here is the code, a new variable is used to clear each loop iteration...


```
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, strTemp 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 ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -activate")
    For Each c In ws.Range("AS10", ws.Cells(ws.Rows.Count, "AS").End(xlUp))
        strTemp = strTemp & "Tag #: " & ws.Cells(c.Row, "Q").Text & NL
        strTemp = strTemp & "Description: " & ws.Cells(c.Row, "R").Text & NL
        strTemp = strTemp & "Equipment Type: " & ws.Cells(c.Row, "T").Text & NL
        strTemp = strTemp & "Activity Code: " & ws.Cells(c.Row, "AP").Text & NL
        Select Case c.Value - Date
        Case Is = 3    'next service date matches a day after today
            strMsg = strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 2    'next service date matches a day after today
            strMsg = strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 1    'next service date matches a day after today
            strMsg = strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 0    'next service date matches today
            strMsg = strTemp & "Next service date is today!" & DNL
        End Select
        strTemp = ""
    Next c
    If strMsg <> "" Then
        Set olMail = OL.CreateItem(0)
        olMail.To = "[email protected]"
        olMail.Subject = "Preservation Date"
        olMail.Body = strMsg
        ' olMail.Display 'for testing purposes only
        olMail.Send
    End If
    objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -suspend")
    ' objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -stop") 'optional to stop the service altogether
    If blnCreated = True Then OL.Quit
End Sub
```


----------



## sonson411 (Oct 11, 2007)

I just tested out... It screens through everything but it only sends out one item (which is the bottom item in the list)

This is what shows up in the email:


> Tag #: 142-PU-401
> Description: Basal Aquaifer Pump
> Equipment Type: Submersible Pump
> Activity Code: I 2, 8
> Next service date is on 2/2/2008.


Anyways, i've attached a shorter version of my excel code...
the RED highlighted part is which i want to send out reminder base on the date (3 days or less)

Actually, there's two columns of dates... because some of them has to do small preversation every 2 weeks and a big preservation every 4 weeks. I'm planning just to create a separate marco base on the second column..


----------



## Zack Barresse (Jul 25, 2004)

Ah, missed a critical part - appending the previous entries to the string variable when setting. Here is the code (look at the Select Case statements and see how it sets the strMsg variable differently)...

```
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, strTemp 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 ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -activate")
    For Each c In ws.Range("AS10", ws.Cells(ws.Rows.Count, "AS").End(xlUp))
        strTemp = strTemp & "Tag #: " & ws.Cells(c.Row, "Q").Text & NL
        strTemp = strTemp & "Description: " & ws.Cells(c.Row, "R").Text & NL
        strTemp = strTemp & "Equipment Type: " & ws.Cells(c.Row, "T").Text & NL
        strTemp = strTemp & "Activity Code: " & ws.Cells(c.Row, "AP").Text & NL
        Select Case c.Value - DateValue("January 30, 2008") ' Date
        Case Is = 3    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 2    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 1    'next service date matches a day after today
            strMsg = strMsg & strTemp & "Next service date is on " & Format(c.Value, StrDateFormat) & "." & DNL
        Case Is = 0    'next service date matches today
            strMsg = strMsg & strTemp & "Next service date is today!" & DNL
        End Select
        strTemp = ""
    Next c
    If strMsg <> "" Then
        Set olMail = OL.CreateItem(0)
        olMail.To = "[email protected]"
        olMail.Subject = "Preservation Date"
        olMail.Body = strMsg
        ' olMail.Display 'for testing purposes only
        olMail.Send
    End If
'    objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -suspend")
'    ' objwShell.Run ("""\\701-0131\resources$\home\etsang\My Documents\ClickYes\Express ClickYes\ClickYes.exe"" -stop") 'optional to stop the service altogether
    If blnCreated = True Then OL.Quit
End Sub
```
I think that should get all of your values. Let us know how it works. Sorry it's taken me a bit to get back to you, been super busy here.


----------



## sonson411 (Oct 11, 2007)

Just got back from my vacation...
once again, great thanks, firefytr!!!

one little question about this line:
Select Case c.Value - DateValue("January 30, 2008") ' Date

is there a particular command to set "Today" date, instead of inputting the date everytime by myself??


----------



## Zack Barresse (Jul 25, 2004)

```
Select Case c.Value - Date
```
*Date* is the VBA command for the current System date. If you want the time, use the *Time* command. If you want the current date *and* time, use *Date + Time*.


----------



## BigDog12345 (Jul 10, 2008)

I tried the sample .xls file and the code and when I click on the script I only get a screen flash. Nothing else happens. I did change the script for my username on where to find the .xls file on my desktop. Any ideas. The code I used was in #12.


Thanks


----------



## slurpee55 (Oct 20, 2004)

Try the code in #31


----------



## BigDog12345 (Jul 10, 2008)

I tried it and it did the same thing. Here are the files I'm using.


----------



## slurpee55 (Oct 20, 2004)

BigDog, since this is a rather old thread (and I haven't seen Zack around lately, but there are some other good coders out there - and I am not one), I would suggest you start a new thread - put a link to this one in it, but post the actual code you are using.


----------

