There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Tag Cloud
audio avg avg 8 backup bios boot browser bsod computer cpu crash css desktop driver drivers dvd email error excel explorer firefox firefox 3 freeze game graphics hard drive hardware help please hijackthis hjt install internet internet explorer itunes javascript keyboard lan laptop malware missing monitor msn network networking openoffice outlook outlook 2003 outlook express php popups problem router screen seo slow sound sp3 spyware trojan usb video virtumonde virus vista vundo windows windows vista windows xp wireless word
Business Applications
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Business Applications >
Set email reminder base on Excel


HELLO AND WELCOME! Before you can post your question, you'll have to register -- it's completely free! Click here to join today! We highly recommend that you print a copy of our Guide for New Members. Enjoy!

 
Thread Tools
sonson411's Avatar
Junior Member with 8 posts.
 
Join Date: Oct 2007
Experience: Intermediate
11-Oct-2007, 06:07 PM #1
Set email reminder base on Excel
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's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
11-Oct-2007, 07:50 PM #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
__________________
___________
Regards, Zack - MVP - MS Excel 2005-2008 (If you would like comments in any code, please say so.)

OfficeArticles.com :|: Extreme Excel Tutorial :|: Excel Articles by Ken Puls :|: Excel User Group, by Nick Hodge

What is a Microsoft MVP? :|: Live Tech Support? Click here
sonson411's Avatar
Junior Member with 8 posts.
 
Join Date: Oct 2007
Experience: Intermediate
12-Oct-2007, 09:44 AM #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).

:-)
Rollin_Again's Avatar
Distinguished Member with 2,709 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
12-Oct-2007, 10:02 AM #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?


Regards,
Rollin
__________________
Protect your computer and keep it free of pests and other spyware. Download AdAware, Spybot, ZoneAlarm and lots of other great utilities at PC World / SnapFiles / Major Geeks, Tech Guy Stuff

Help Keep this site free by making a small donation

http://www.techguy.org/donate.html
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
12-Oct-2007, 10:59 AM #5
I know a couple people Rollin'.
sonson411's Avatar
Junior Member with 8 posts.
 
Join Date: Oct 2007
Experience: Intermediate
15-Oct-2007, 11:10 AM #6
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!!
Attached Files
File Type: xls Mechanic Equipment Schedule Summary1.xls (20.5 KB, 203 views)
Aj_old's Avatar
Computer Specs
Senior Member with 223 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
15-Oct-2007, 01:00 PM #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?
__________________
The BestS
AJ
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
15-Oct-2007, 01:52 PM #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.
__________________
___________
Regards, Zack - MVP - MS Excel 2005-2008 (If you would like comments in any code, please say so.)

OfficeArticles.com :|: Extreme Excel Tutorial :|: Excel Articles by Ken Puls :|: Excel User Group, by Nick Hodge

What is a Microsoft MVP? :|: Live Tech Support? Click here
Rollin_Again's Avatar
Distinguished Member with 2,709 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
15-Oct-2007, 02:12 PM #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
__________________
Protect your computer and keep it free of pests and other spyware. Download AdAware, Spybot, ZoneAlarm and lots of other great utilities at PC World / SnapFiles / Major Geeks, Tech Guy Stuff

Help Keep this site free by making a small donation

http://www.techguy.org/donate.html
Aj_old's Avatar
Computer Specs
Senior Member with 223 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
15-Oct-2007, 02:32 PM #10
I have some ideas how it can be done but I need more info about how U wanna it be donne
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
15-Oct-2007, 02:56 PM #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.
__________________
___________
Regards, Zack - MVP - MS Excel 2005-2008 (If you would like comments in any code, please say so.)

OfficeArticles.com :|: Extreme Excel Tutorial :|: Excel Articles by Ken Puls :|: Excel User Group, by Nick Hodge

What is a Microsoft MVP? :|: Live Tech Support? Click here
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
15-Oct-2007, 03:36 PM #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.
Attached Files
File Type: zip CheckEmailStatus.zip (653 Bytes, 85 views)
Aj_old's Avatar
Computer Specs
Senior Member with 223 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
16-Oct-2007, 01:50 AM #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!
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,302 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
16-Oct-2007, 02:11 AM #14
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's Avatar
Junior Member with 8 posts.
 
Join Date: Oct 2007
Experience: Intermediate
16-Oct-2007, 11:48 AM #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
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are Off
Refbacks are Off

You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 06:30 PM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.