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

jeetusaini85

Board Regular
Joined
Aug 9, 2013
Messages
131
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"
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top