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.

Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

Discussion in 'Business Applications' started by PhateX1337, Jul 28, 2008.

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

    PhateX1337 Thread Starter

    Joined:
    Jul 28, 2008
    Messages:
    5
    Hey guys,
    I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

    Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

    I have attached the excel file I want to use.

    I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

    Any help would be greatly appreciated, thanks!
     

    Attached Files:

  2. Sponsor

  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    Automating the email is not difficult but you must have an Event that will fire the code (Workbook_Open , Workbook_BeforeClose, Worksheet_Change, etc.) If you want the entire process to be automated it is probably a better choice to use Windows Task Scheduler to fire a stand alone VBS script that will automatically open your workbook each day and evaluate the contents before taking the appropriate action. Does that sound like something that would work for you or do you want the code to be fired directly from within Excel?

    Regards,
    Rollin
     
  6. PhateX1337

    PhateX1337 Thread Starter

    Joined:
    Jul 28, 2008
    Messages:
    5
    Thanks for the replies. I really do not know how to manipulate that code too well that you posted, slurpee. I tried, but I am not sure how to set Outlook (or Thunderbird, in my case) as a reference. I'm also not sure how to change it to look for dates in column C for my spreadsheet. Also, can I change that to just send all the emails to one address instead of it looking for addresses in column B? Finally, will that work throughout all sheets?


    Rollin,
    I would prefer to keep it simple and just fire it on Workbook_Open instead of scheduling it. Any chance you could help me out with getting this code to work?

    Thanks!
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    there is code here
    http://www.ozgrid.com/forum/showthread.php?t=49302&page=2
    I think that, if your email is called, then whatever you have as your default will be what opens - Rollin probably knows better than I, however.
    As for the changes, I think we need a coder (like Rollin!!!) to write it for you....
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    What mail client do you have installed?

    Regards,
    Rollin
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  11. PhateX1337

    PhateX1337 Thread Starter

    Joined:
    Jul 28, 2008
    Messages:
    5
    Yep, I'm using Thunderbird. But if at all possible I'd like it to be cross-compatible with Outlook. Is this possible? If not, let's just assume that I am using Outlook because that it what it will be used on.
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Outlook is much better integrated with Excel and the code exists already - hopefully, Rollin can adapt some to meet your needs. Thunderbird has a few issues with Excel (see the link in #9), and, although it could (probably) be used to meet your needs, using Outlook is much more straight-forward.
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,436
    "Cross-compatible"; I've found that it'll "run with" your default mail client, as slurpee said.

    The attached is as the file you posted, but including a macro "Test" that autoruns thanks to a Workbook_Open event. So you just need to modify Test to do what you need (instead of displaying the "Workbook opened OK." prompt).

    Have a look at this from John W. I've used it many times, it's more "newbie-friendly" (no offence intended) IMO.

    HTH
     

    Attached Files:

  14. PhateX1337

    PhateX1337 Thread Starter

    Joined:
    Jul 28, 2008
    Messages:
    5
    Yeah just assume I'm using Outlook with this - that is what the systems will be using and that's what it needs to be compatible with.
     
  15. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Replace the sub test from the bomb#21 attachment with this:
    Code:
    Sub Test()
    MsgBox "Workbook opened OK."
    Application.ScreenUpdating = False
        For Each sh In ThisWorkbook.Worksheets
            i = 0
            Do While Not (IsEmpty(sh.Cells(i + 2, "C")))
                If sh.Cells(i + 2, "C").Value = Date Then
                    msg = "Work Requested By: " & sh.Cells(i + 2, "B").Value & Chr(10) & _
                          "Recived on " & sh.Cells(i + 2, "A").Value & Chr(10) & _
                          "Is needed on " & sh.Cells(i + 2, "C").Value & Chr(10) & _
                          ""
                    Call SendMail(msg, address)' Type in one cell you mail address, and set the Address = to that cell, or just change it with your mail address
                End If
                i = i + 1
            Loop
        Next
    Application.ScreenUpdating = True
    End Sub
    and create a new sub like this:
    Code:
    Sub SendMail(Message, MailAddress)
        Dim OutApp As Object
        Dim OutMail As Object
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
    
    On Error GoTo cleanup
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = MailAddress
            .Subject = "Reminder"
            .Body = Message
            .Send  'Or use Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    cleanup:
        Set OutApp = Nothing
    End Sub
    
    the second code is from http://www.rondebruin.nl/mail/folder3/message.htm
     
  16. PhateX1337

    PhateX1337 Thread Starter

    Joined:
    Jul 28, 2008
    Messages:
    5
    AJ that works perfectly, thank you so much! It is just what I needed.
     
  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/734664