VBA to mail when cell value update/change

[Jagdev Singh]

Hi Ninja,

Hope you all are well,

I am looking for a vba code to send mail from excel when I add/change specific word SPOE/Assign in column I. If I add/change the cell to Assign in column I then an email should be generate to the person whose name is mentioned in respective cell in column F and same when I add/change the cell value in column I to SPOE then the Emil should be generate in outlook to the person whose name is there in the respective cell in Column G (SPOE).

Please find the same copy attached with the mail.

Regards,

JD

 

[Deepak]

Jagdev Singh

Good to see you jaggu!!

 

[Jagdev Singh]

Hi Deepak,

Hope you are well!

Please help me with the above query. I am stuck and not finding a way around to deal with this.

Regards,
JD

 

[Deepak]

Check this...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("I:I")) Is Nothing And Target.Rows.Count = 1 And Target.Value <> "" Then

    Select Case True
        Case LCase(Target) = "spoe", LCase(Target) = "assign"
            With CreateObject("Outlook.Application").CreateItem(0)
                .To = Target.Offset(, -(2 + CInt(Abs(LCase(Target.Value) = "assign")))).Value
                .Subject = "Test Mail"
                .HTMLBody = "test"
                .Display
                '.Send
            End With
    End Select

End If

End Sub

 

[Jagdev Singh]

Hi Deepak,

Sorry for the delay in getting back to you on this. I have added the code in the Sheet1 section on the VBA side. I tried adding the word assign and spoe in column I, but nothing happens. I even select the reference tool – Microsoft Outlook 14.0 Objective Library and checked my macro tool setting and make it to Enable all macros in both excel and outlook. Please let me know what I am missing here.

Regards,

JD

 

[Jagdev Singh]

Oops my fault it is working let me test it further.

 

[Jagdev Singh]

Hi Deepak,

Sorry for delay in getting back to you on this.

The code works fine and thank you for your input on this.

Many thanks!

Regards,
JD

 

[Deepak]

Thanks for the feedback..