title: Excel VBA macro to send multiple attachments based on cell values
body: <p>I'm a beginner in VBA and am working on a macro to automate reports.  Basically, the macro goes through a spreadsheet where each row represents a different client.  The third column has the client name, the fourth column has the amount spent, fifth column is the recipient's e-mail address, and the sixth and seventh columns contain parts of the file path for a complete report.</p>
  
  <p>It works fine if you want to generate an e-mail for each row, but now I want it to generate an e-mail including information from multiple rows.  My thought was that I would assign each e-mail a unique ID in column 2 and that it would skip generating the e-mail if the row had the same unique ID as the row before it, but it would include the numbers from column 4 and the attachments based on the file path in columns six and seven for all subsequent rows that were equal to that unique ID.  If column 2 went 1, 1, 1, 2, 2, 3, for example, there would be 3 e-mails instead of 6.  The list is always going to be ordered correctly, so that's not a concern.</p>
  
  <p>I'm not quite sure how to create the nested loop that would loop through all of the subsequent rows with equal values to the first cell in column 2 and then ignore them for the first loop.  I'd appreciate any suggestions.</p>
  
  <pre><code>Sub Test1()
  Dim rng as Range
  Dim OutApp as Object
  Dim OutMail as Object
  Dim r as Long
  
  Set rng = Nothing
  Set rng = ActiveSheet.UsedRange
  
  For r = 2 to 8
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  With OutMail 
       .To = rng.Cells(r,5).value
       .Subject = rng.Cells(r,3).value &amp; " Report"
        If rng.Cells(r,2).value &lt;&gt; rng.cells.value(r+1,2) Then
              .HTMLBody = "This is how much was spent on the " &amp; rng.Cells(r,3).Value &amp; " account:" &amp; rng.Cells(r,4),value
              .Attachments.Add (rng.Cells(r,6).value &amp; rng.Cells(r,7).value)
        ElseIf rng.Cells(r,2).value = rng.Cells(r+1, 2) Then
              .HTMLBody = "This is how much was spent on the " &amp; rng.Cells(r,3).Value &amp; " account:" &amp; rng.Cells(r,4).value &amp; "&lt;/br&gt;" &amp; _
              "This is how much was spent on the " &amp; rng.Cells(r+1,3).Value &amp; " account:" &amp; rng.Cells(r+1,4).value
              .Attachments.Add (rng.Cells(r,6).value &amp; rng.Cells(r,7).value)
              .Attachments.Add (rng.Cells(r+1,6).value &amp; rng.Cells(r+1,7).value)
        Else '
        End If
       .Display
  
   End With
   Set OutMail = Nothing
   Set OutApp = Nothing
  
   Next r
   End Sub
  </code></pre>