title: Change loop from every cell to particular range in Excel VBA
body: <p>I have the following piece of code which sends emails in bulk.</p>
  
  <pre><code>Sub Sengrd_Files()
  
      Dim OutApp As Object
      Dim OutMail As Object
      Dim sh As Worksheet
      Dim cell As Range
      Dim FileCell As Range
      Dim rng As Range
  para2 = ""
  para3 = ""
  
  para232 = Range("AA2").Value
  
      With Application
          .EnableEvents = False
          .ScreenUpdating = True
      End With
  
      Set sh = Sheets("Sheet1")
  
      Set OutApp = CreateObject("Outlook.Application")
  
      For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
  
          'Enter the path/file names in the C:Z column in each row
          Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
  
          If cell.Value Like "?*@?*.?*" And _
             Application.WorksheetFunction.CountA(rng) &gt; 0 Then
              Set OutMail = OutApp.CreateItem(0)
  
              With OutMail
                  .to = cell.Value
                  .Subject = "Circle Profitability Report for the period ended 30-NOV-2017"
                  .Body = "Dear Sir/Madam," _
  &amp; vbNewLine _
      &amp; para232 &amp; vbNewLine _
      &amp; vbNewLine &amp; para2 &amp; vbNewLine _
      &amp; Remark &amp; vbNewLine &amp; vbNewLine _
      &amp; para3 &amp; vbNewLine &amp; vbNewLine
  
  
                  For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                      If Trim(FileCell) &lt;&gt; "" Then
                          If Dir(FileCell.Value) &lt;&gt; "" Then
                              .Attachments.Add FileCell.Value
                          End If
                      End If
                  Next FileCell
  
                  .Send  'Or use .Display
              End With
  
              Set OutMail = Nothing
          End If
      Next cell
  
      Set OutApp = Nothing
      With Application
          .EnableEvents = True
          .ScreenUpdating = True
      End With
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/jqq9n.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jqq9n.png" alt="enter image description here"></a></p>
  
  <p>7 different mails will be sent to different people mentioned in Column B with Attachment defined in Col C.
  The Macro by default sends mails for ALL line items probably because of this line in code</p>
  
  <pre><code>**For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)**
  </code></pre>
  
  <p>I cannot define a variable i and change the above line to</p>
  
  <pre><code>**For Each i =1 to 5 sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)** 
  </code></pre>
  
  <p>due to syntax error. Can anyone help me in syntax in replacing "For each cell in" to a finite range.</p>