Excel to do list with email notification

[Jay3]

Hi Everyone,

I'm trying to build the above. A simple task list which delivers an email notifcation to a user when a new task is added to the worksheet.

I'm rubbish with macros and was wondering if someone could help me out or if anyone has a template they can share which I could tweak that would also be helpful.

It's for use with excel and outlook 2007.

I have a drop down menu in my task list which is a column headed up as "Notify" and the options being Yes or No.

If the user select Yes I want this action to automatically call my next procedure which sends the email to the user.

I'm still struggling with this first bit yet alone the second!

This is the code I have so far, so if anyone can help it would be very much appreciated.

Sub Worksheet_Calculate()
 
  Const CalcCell As String = "G2"
  Const LastValue As String = "G3"
 
  If Range(CalcCell).Value <> Range(LastValue).Value Then
    Range(LastValue) = Range(CalcCell).Value
    Call Mail_small_Text_Outlook
 
    End If
 
End Sub
 
 
Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    On Error Resume Next
    With OutMail
        .To = "[EMAIL="emailaddresshere@somewher.co.uk"]emailaddresshere@somewher.co.uk[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

As I say if anyone has a template they are willing to share which does a similar job / someone can help me finish this off it would be great.

Thanks All,
Jay3