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


Board Regular
Aug 9, 2013
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:

Rich (BB code):
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
Rich (BB code):
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"
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" _

End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Please help me on this guys.

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.


Board Regular
Aug 9, 2013
Hey VOG r u there to help me......

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...