Help to run a macro when a specific text enter in a cell through another macro

[jeetusaini85]

Hello Kings of macros !!!

I am new in macro and need your help to complete my project.

I have an excel sheet in which some data entered by a form through macro (excel form in sheet 1 and data entered in sheet 2). In the form i have a field which ask users to send email to user with "Yes" or "No" option. Now if user select "Yes" with drop down and hit add content the data has entered but mail not send. It gives me "Type mismatch" error but when if i manually enter "Yes" at data sheet it shoots the mail.

I am stuck here. Please find below the codes:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Intersect(Target, Range("R1:R5001")) Is Nothing Then Exit Sub
If Target.Count < 1 Then Exit Sub
If Target.Value = "Yes" Then ' Error line
Call Mail(Target.Offset(, -11), Target.Offset(, -13), Target.Offset(, -6), Target.Offset(, -2))
End If
Application.ScreenUpdating = True
End Sub

Sub Mail(Email As String, Srn As String, Status As String, Details As String)
Dim OutApp As Object
Dim OutMail As Object
'MsgBox "Attempting to send e-mail"
Set OutApp = CreateObject("Outlook.Application")
If OutApp Is Nothing Then MsgBox "Failed to set OutApp"
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
If OutMail Is Nothing Then MsgBox "Failed to set OutMail"
With OutMail
    .To = Email
    .CC = ""
    .BCC = ""
    .Subject = "IT Support Auto Reply : SRN No. -  " & Srn & " [ " & Status & " ]"
    .Body = "Dear User," & vbCrLf & vbCrLf _
          & "Greetings from IT Help Desk!!!" & vbCrLf & vbCrLf _
          & "Plese find blelow the status of your SRN (Service Request Number) :-  " & Srn & vbCrLf & vbCrLf _
          & "Query Details    : " & "[ " & Details & " ]" & vbCrLf _
          & "Query Status      : " & "[ " & Status & " ]" & vbCrLf & vbCrLf & vbCrLf _
          & "Best Wishes," & vbCrLf _
          & "IT Team" & vbCrLf _
          & vbCrLf & "Intec Capital Ltd" _

    .Display
    
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Please help me on this guys.

 

[jeetusaini85]

Please help me on this guys.

 

[jeetusaini85]

Please help to solve this !!!

 

[jeetusaini85]

I think nobody is here to help me...

 

[jeetusaini85]

Hey VOG r u there to help me......

 

[jeetusaini85]

Bump

 

[jeetusaini85]

Thanks to all !!!