VBA adding multiple attachments based on record count

[Damian37]

I have posted some previous questions concerning this matter, but I don't think I clearly explained what I'm
dealing with. So I am creating a new post in the hopes I can clear things up.
I have an issue with some code that I have to create a generic email and add an attachment to the email.
I have it worked out where I'm only creating one e-mail regardless of how many records the recipient has in the file.
However, the code is also adding an attachment for each record the recipient has in the file. So if there are 3
records for the recipient than 3 attachments are added to the email. Can anyone see in my coding where it would cause
multiple attachments in the email. I thank you for any assistance you can provide.

Sub OutlookEmail()
  
  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim iCounter As Integer
  Dim MailDest As String
  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)
  Set fso = New Scripting.FileSystemObject
  
  'Worksheets("Data").Activate
   
  For iCounter = 2 To WorksheetFunction.CountA(Columns(32))
'  For Each cell In Worksheets("Data").Columns(32).Cells.SpecialCells(xlCellTypeConstants)
     MailDest = ""
     If Len(Cells(iCounter, 32).Offset(0, -31)) > 0 Then
     If MailDest = "" And Cells(iCounter, 32).Offset(0, -12) < 0 Then
     If Cells(iCounter, 32).Value Like "*@*.*" And _
        Application.WorksheetFunction.CountA(Columns(32)) > 0 Then
     With OutLookMailItem
     MailDest = Cells(iCounter, 32).Value

        .To = MailDest
        .CC = "Davon_Johnston@cable.comcast.com; Casey_Montgomery@cable.comcast; Damian_Velez@cable.comcast.com"
        .Subject = "Negative Replenishment"
        .HTMLBody = "Hello " & Cells(iCounter, 31).Value & ",
" _
            & "Your store(s) is/are reporting negative inventory on one or more SKUs. " _
            & "The SKUs that have negative counts will impact replenishment of that particular SKU(s). " _
            & "Please cycle count the below SKU(s) and enter the corrected on hand quantity into the system to prevent further impact to replenishment. " _
            & "Please remember a negative inventory count on 1 SKU will stop replenishment on that 1 SKU, " _
            & "more than 5 negative inventory counts on devices will impact all device replenishment, " _
            & "and more than 20 negatives on accessories will impact replenishment on all accessories until counts are corrected. " _
            & "If you are having an issue correcting your negative inventory please open a Service Now ticket for xStore issues." _
            & "For inventory related issues, please open a ticket in Spice Works for the Supply Chain Support Desk (SCSD).
" _
            & "Thank You," & "Davon Johnston
" _
            & "Manager, Supply Chain Support, Strategic Development
" _
            & "Cell #: 720-357-0303
" _
            & "Desk #: 303-658-7803"
            
            'strLocation = "\\cable.comcast.com\corp-DFS\CHQ-Shared\SC Support Desk\Negative Replenishment Reports\Negative Replenishment file_" _
    '& Format(Date, "mm.dd.yyyy") & ".xlsx"
    
            
            .Attachments.Add "\\cable.comcast.com\corp-DFS\CHQ-Shared\SC Support Desk\Negative Replenishment Reports\Negative Replenishment file_" _
    & Format(Date, "mm.dd.yyyy") & ".xlsx"
    
            
    .Display
    '.Send
    End With
            End If
        End If
    End If
'   End If
    Next iCounter
  Set OutLookMailItem = Nothing
  Set OutLookApp = Nothing
End Sub

D.

 

[DanteAmor]

What do you need?
Create an email by record? Each record with one file attachment


Or just an email. But if you have 3 records and you just want an email, why do you have a FOR cycle?

If you have 3 records on the sheet, what do you expect?