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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

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:
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

No members online now.
Top