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.

Automatic Email from Excel based on Date in Cell

Discussion in 'Business Applications' started by DMazz, Aug 30, 2009.

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

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Hi All,

    My name is Diego.

    Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

    http://forums.techguy.org/business-a...s-using-2.html


    Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

    BTW - I am using Outlook 2007 and Excel 2007 on Vista.

    Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,834
  3. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Thanks for the reply. I have read through some of these posts and had tried to modify them. As simple as that sounds, there is always some extra logic in there that I do not know how to change properly without giving me problems. The thing is, I have been using excel for years but VBA is GREEK to me. I have only begin to explore the idea of MACROs.

    I am posting my thread because I have no CLUE. So if there is a guy like ZACK who can whip off a working code in minutes, I would really appreciate it.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,834
    If you want the message sent when Excel is not running you will need to use Windows Scheduler to open the workbook that sends emails.
    What do you want the email to say?
     
  5. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Thanks.
    I will put the column data in quotations so that you know which data I want included in my email. It should be as follows:

    Course "B" is scheduled for renewal with the "D" licensing board on the date "J".
    I hope this helps you. Thanks again.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post a sample workbook? Based on what you said your best option is the one that has already been mentioned. You should use the Windows Task Scheduler to fire off a VBS script that will open the workbook in the background and evaluate the date before taking action. Using the Outlook ClickYes program is one way of handling the built in security prompt that is generated by Outlook but there are other methods of sending the email directly through your mail server without using the Outlook object model. Using one of these alternate methods will allow you to send emails without receiving a security prompt at all.

    Regards,
    Rollin
     
  7. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Thanks for your help Rollin. Please have a look at the sample data. I will follow your instructions once you are ready:[​IMG]
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    So I assume that once the email is automatically sent you will place a value in the "K" column? Can you also tell me how often the code should run? You will need to use Windows task scheduler to set the time interval to open and check the workbook. I also need to know what you want the email to say.

    Regards,
    Rollin
     
  9. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Yes you are correct - I would like it to say "Yes".
    The code should run at least once a week.
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I've written and tested some simple VBS code and it appears to work.

    What info would you like to appear in the email?

    Regards,
    Rollin
     
  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You've indicated in your original post that you want to have the code check to see if the date in the specified column matches the current date correct? If you are going to be checking each value against the current date you would need to have the code executed daily. If you only run it once a week you will likely have cells in your workbook that have dates that are prior to the current date. If that is the case you would need to have the code check for dates that are either equal to or less than the current date. Does that make sense? How do you want to handle it? I've included some sample VBS code below. You will need to have the Outlook ClickYes program installed for this to work without user input.

    Code:
    Dim objExcel 
    Dim objOutlook 
    Dim objMail 
    Dim objWB 
    Dim objWS 
    Dim vCell
    
    Set objExcel = CreateObject("Excel.Application")
    Set objOutlook = CreateObject("Outlook.Application")
    
    objExcel.DisplayAlerts = False
    objExcel.Workbooks.Open ("[COLOR="Red"]C:\Test.xls[/COLOR]")
    Set objWB = objExcel.Activeworkbook
    Set objWS = objWB.ActiveSheet
    For Each vCell In objWS.Range("[COLOR="Red"]J1[/COLOR]:J" & objWS.Cells(objWS.Rows.Count, "J").End(-4162).Row).Cells
    
    If FormatDateTime(vCell) <= FormatDateTime(Date) Then
    If vCell.Offset(0, 1).Value <> "YES" Then
    Set objMail = objOutlook.CreateItem(olMailItem)
    
    objMail.To = "[COLOR="Red"][email protected][/COLOR]"
    objMail.Subject = vCell.offset(0, -8).Value & " Email Alert"
    
    objMail.Body = "COURSE TITLE - " & vCell.offset(0, -8).Value & vbCrLf & _
    "APPROVING STATE - " & vCell.offset(0, -7).Value & vbCrLf & _
    "APPROVING BODY - " & vCell.offset(0, -6).Value & vbCrLf & _
    "PROFESSION - " & vCell.offset(0, -5).Value & vbCrLf & _
    "CEUs - " & vCell.offset(0, -4).Value & vbCrLf & _
    "APPROVAL NUMBER - " & vCell.offset(0, -3).Value & vbCrLf & _
    "DATE RECEIVED - " & vCell.offset(0, -2).Value & vbCrLf & _
    "EXPIRATION DATE - " & vCell.offset(0, -1).Value
    
    objMail.Send
    vCell.Offset(0, 1).Value = "YES"
    End If
    End If
    Next
    
    
    objWB.Save
    objWB.Close
    objExcel.Quit
    objOutlook.Quit
    Set objExcel = Nothing
    Set objWB = Nothing
    Set objWS = Nothing
    Set objMail = Nothing
    Set objOutlook = Nothing
    


    Regards,
    Rollin
     
  12. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    Yeah that makes perfect sense. Based on your question, it's clear that you got the nature of the request I have. In that case, the code should run daily. My only worry is that these events happen only several times a year. So having excel open up daily might be bothersome. However, I am willing to live with that.
    On the other hand, if you have a solution that opens it once a week and the date in question happens to fall within the next 7 days, then an email can go out. In other words. The program will scan the dates to see which events will occur each week and send an email out.

    BTW - in case you're wondering, I wouldn't mind if it was one email that had all events for that week or a separate email for each event in the same week.

    Whatever is easiest for you to code is good for me. I appreciate your work and your help!
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I've changed the code above to include logic to check to see if the date is equal to or less than the current date. Just copy the code above into a plain text file and then manually rename the file extension from .txt to .vbs

    You'll want to change the portions that are highlighted in red. The first highlighted portion is the path to your workbook. The second highlighted portion represent the first date cell that will be evaluated. In my example above it would check the first date value in cell J1 so you'll want to change it to reflect the correct row number in your workbook. The last highlighted portion is the email address that you want to receive the email. After the changes have been made and the file saved with the .vbs extension you can run the code by either double clicking the .vbs file or using the Windows Task Scheduler to create a new weekly task and point it to the VBS file. Try the code out by making a copy of your workbook and changing some of the dates to equal todays date or earlier and let me know if it works for you.

    As it stand right now I have a separate email being generated for each event. If I have time I'll tweak the code so that all events are generated in one single email. Also keep in mind that the code assumes that there are no blank lines of data between records.

    Regards,
    Rollin
     
  14. DMazz

    DMazz Thread Starter

    Joined:
    Aug 29, 2009
    Messages:
    23
    I tried the code above by saving it to it's own vbs extensions which I names approvalswarning.vbs

    I put in the appropriate email info and I renamed the xls file to the one I have. There is one concern and one error.

    When I double click the vbs file, it states a VBSscript runtime error code 800A00D which states Type Mismatch: "Format Date Time"

    The concern I have is that when I try to open up MS EXcel it says the file is read only. I will need to update this file with newly approved courses and future expiry dates.

    Am I doing something wrong. The dates are formatted the way they I sent them to you. Please let me know.
     
  15. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I created my own workbook and tested the code on it and it works perfectly. Can you email me your actual workbook so I can debug it? You can send to rollin_again at hotmail dot com


    Regards,
    Rollin
     
  16. 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/856705

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice