1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Set email reminder base on Excel

Discussion in 'Business Applications' started by sonson411, Oct 11, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. sonson411

    sonson411 Thread Starter

    Joined:
    Oct 11, 2007
    Messages:
    8
    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. Sponsor

  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  4. sonson411

    sonson411 Thread Starter

    Joined:
    Oct 11, 2007
    Messages:
    8
    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).

    :)
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    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
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I know a couple people Rollin'. ;)
     
  7. sonson411

    sonson411 Thread Starter

    Joined:
    Oct 11, 2007
    Messages:
    8
    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:

  8. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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?
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    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. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    I have some ideas how it can be done but I need more info about how U wanna it be donne
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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 = "[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.
     

    Attached Files:

  14. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    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. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  16. sonson411

    sonson411 Thread Starter

    Joined:
    Oct 11, 2007
    Messages:
    8
    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
     
  17. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/636786