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.

Please help me fix this "Send email when due date" script :(

Discussion in 'Business Applications' started by lordazura, Jan 25, 2016.

Thread Status:
Not open for further replies.
  1. lordazura

    lordazura Thread Starter

    Joined:
    Jan 25, 2016
    Messages:
    2
    Hello and yes, another one of this. Although I know absolutely nothing of VBA, I've read about a dozen topics on the subject on techguy and other communities in an attempt to figure out something, a way to do it.

    I found the script that was the most likely to work in my case, analyzed it, switched the references, cells and so on. I think I am getting close to something functional, alas, I fear it is as far as my knowledge and Trials & Errors goes.

    I'll be forever grateful if someone can fix this for me. I'll even donate 5$ to Tech Support Guy with a mention of your glorious name :p

    I am using Excel 2010 in English, Windows 7 and Outlook.

    Attached is the fruit of all my efforts. I am looking to make a task tracker that will email me should a task approach its due date (yes I know that sheet will have to remain opened, it isn't an issue :p)


    Many thanks and best regards,
    LordAzuRa
     

    Attached Files:

  2. lordazura

    lordazura Thread Starter

    Joined:
    Jan 25, 2016
    Messages:
    2
    Latest version of the script:

    Code:
    Option Explicit
    
    Private Sub Worksheet_Calculate()
      Dim FormulaCell  As Range
      Dim FormulaRange  As Range
      Dim NotSentMsg  As String
      Dim MyMsg  As String
      Dim SentMsg  As String
      Dim MyLimit  As Double
    
      NotSentMsg = "Not Sent"
      SentMsg = "Sent"
    
      'Above the MyLimit value it will triger the email
      MyLimit = Date
     
      Set FormulaRange = Me.Range("E5:E35")
      On Error GoTo EndMacro:
      For Each FormulaCell In FormulaRange.Cells
      With FormulaCell
      If .Value > MyLimit Then
      MyMsg = NotSentMsg
      If .Offset(0, 1).Value = NotSentMsg Then
      strTO = "[email protected]"
      strCC = "[email protected]"
      strBCC = ""
      strSub = "Greetings " & Cells(FormulaCell.Row, "B").Value
      strBody = "Hi Sir, " & vbNewLine & vbNewLine & _
      "This email is to notify that you need to do your task : " & Cells(FormulaCell.Row, "B").Value & _
      vbNewLine & vbNewLine & "Regards, Yourself"
      If sendMail(strTO, strSub, strBody, strCC) = True Then MyMsg = SentMsg
    '  Call Mail_with_outlook2
      End If
      Else
      MyMsg = NotSentMsg
      End If
      Application.EnableEvents = False
      .Offset(0, 1).Value = MyMsg
      Application.EnableEvents = True
     
      End With
     
      Next FormulaCell
    
     
    ExitMacro:
      Exit Sub
    
    EndMacro:
      Application.EnableEvents = True
    
      MsgBox "Some Error occurred." _
      & vbLf & Err.Number _
      & vbLf & Err.Description
    
    End Sub
    
    It works properly! I did it :D
    Although the "My Limit = Date" function isn't exactly what I want. It currently send me an email for tasks that have a date later than the current day. What I need the script to do is send me an email when the due date is the Exact same day as when I opened the document.
    Any insight?​
     
    Last edited: Jan 26, 2016
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/1164897

  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