title: Count number of rows with specific text
body: <p>I have an assignment to count the number of rows which contain specific text and print the value on email.</p>
  
  <pre><code>Public Function First()
      Dim Source As Workbook
      Dim Var1 As Integer
      Dim Var10 As Integer
      Dim Source2 As Workbook
  
      Set Source = Workbooks.Open("C:\Users\HP\Desktop\IN INPROG.xlsx")
  
      Var1 = Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")
  
      Source.Close SaveChanges:=False
  
      Set Source2 = Workbooks.Open("C:\Users\HP\Desktop\SR INPROG.xlsx")
  
      Var10 = Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")
  
      Source2.Close SaveChanges:=False
  
      eTo = "orange@aod.au"
      esubject = Format(Date, "d/mmmm/yyyy") &amp; " " &amp; "Weekly Open Incident Reminder"
      ebody = "Dear All," &amp; vbCrLf &amp; "" &amp; vbCrLf &amp; "" &amp; vbCrLf &amp; "Orange: " &amp; "SR: " &amp; Var10 &amp; " IN: " &amp; Var1 
  
      Set app = CreateObject("Outlook.Application")
      Set itm = app.createitem(0)
      On Error Resume Next
      With itm
          .Subject = esubject
          .To = eTo
          .body = ebody
          .display
  </code></pre>
  
  <p>My code will return all the values as "0" even though "Orange" does exist in one of the rows.</p>