Tech Support Guy banner
Status
Not open for further replies.

Set email reminder base on Excel

47K views 36 replies 7 participants last post by  slurpee55 
#1 ·
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
 
#2 ·
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
 
#3 ·
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).

:)
 
#4 ·
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? :D


Regards,
Rollin
 
#7 ·
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?
 
#8 ·
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.
 
#9 ·
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
 
#11 ·
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. :)
 
#12 ·
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)...

Code:
'/// 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...

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

Attachments

#13 ·
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!
 
#15 ·
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
 
#16 ·
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
 
#17 ·
Zack what would U say about such an aprouch:

Code:
Sub EmailfromExcel()
nr = 0
SendToList = "send@list.com"
    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!
 
#18 ·
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.
 
#19 ·
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!
 
#20 ·
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. :)
 
#23 ·
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
 
#25 ·
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
 
#26 ·
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)?
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top