Need help getting excel to send automatic emails based on cell value

Ticket Spreadsheet.xlsx

I have looked at various threads and whilst i can find similar solutions none are working

I want a ticketing system that automatically sends an email containing references to the address in column I when the corresponding cell B is changed to 'IN PROGRESS' then another (different email) when it is changed to resolved.

I want a MsgBox to appear asking: 'Would you like to email the customer?' Y/N so i can choose not to send the email. i want excel to just directly send the email via the exchange network IF POSSIBLE and not open outlook email; however i would be happy to run it through Outlook if it makes this work.



So when column B changes to 'IN PROGRESS' an message box appears.

If Yes is clicked an email is sent:

Email Subject: Ticket Number [COLUMN A] for [COLUMN J]

Email Msg: Thanks for returning your [COLUMN J] your ticket number is [COLUMN A] please quote this on all future emails. We aim to resolve this by [COLUMN C + 7 DAYS]. Thank You.


When column B is changed to 'RESOLVED' then it should send another email out:

Email Subject: Ticket Number [COLUMN A] for [COLUMN J] - RESOLVED

Email Msg: Your issue with your [COLUMN J], ticket number [COLUMN A] has now been resolved. Thank You.


My code is probably miles away from being right:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lngResponse As Long
    Dim URL As String, strEmail As String, strSubject As String, strBody As String
    If Left(Target.Address, 2) = "$B" Then
        If Target.Value =”IN PROGRESS”. Value Then
            lngResponse = MsgBox("Would you like to email the customer?", vbYesNo)
            If lngResponse = vbYes Then
                strEmail = Range("$I" & Right(Target.Address, 2)).Value
                strSubject = "Ticket Number " & Range("$A" & Right(Target.Address, 2)).Value & "for " & Range("$J" & Right(Target.Address, 2)).Value
                strSubject = Application.WorksheetFunction.Substitute(strSubject, " ", "%20")
               strBody = “Thanks for returning your “  & Range("$J" & Right(Target.Address, 2)).Value & “your ticket number is“ & Range("$A" & Right(Target.Address, 2)).Value & “please quote this on all future emails. We aim to resolve by “& Range("$D" & Right(Target.Address, 2)).Value & “. Thank You.”
strURL = "mailto:" & strEmail & "?subject=" & strSubject
                ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
debugs:
If Err.Description <> "" Then MsgBox Err.Description
            End If
        End If
    End If
Exit Sub
        If Target.Value =”RESOLVED”. Value Then
            lngResponse = MsgBox("Would you like to email the customer?", vbYesNo)
            If lngResponse = vbYes Then
                strEmail = Range("$I" & Right(Target.Address, 2)).Value
                strSubject = "Ticket Number " & Range("$A" & Right(Target.Address, 2)).Value & "for " & Range("$J" & Right(Target.Address, 2)).Value & “- RESOLVED”
                strSubject = Application.WorksheetFunction.Substitute(strSubject, " ", "%20")
               strBody = “Your issue with your “  & Range("$J" & Right(Target.Address, 2)).Value & “, ticket number “ & Range("$A" & Right(Target.Address, 2)).Value & “has now been resolved. Thank You.”
strURL = "mailto:" & strEmail & "?subject=" & strSubject
                ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus
debugs:
If Err.Description <> "" Then MsgBox Err.Description
            End If
        End If
    End If
End Sub

Thanks in advance.