Filtered Results to email

[Alphacsulb]

I came across this VBA post that allows me to create an email and BCC a list of emails, however I want to gather the visible filtered list, and not everything that is within the range.

I'm unsure where SpecialCells(xlCellTypeVisible) would apply. Any assistance would be appreciated:

Sub CreateEmail()


Dim oMSOutlook As Object
Dim oEmail As Object
Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)


With oEmail


 For i = 2 To Application.WorksheetFunction.CountA(Columns(4)) '2 Skips header, Column 4 applies to Column D
 .Recipients.Add(Cells(i, 4).Value).Type (3)   ' Type 1 (To), Type 2 (CC), Type 3 (BCC) 


 Next i


.Display 'Displays the email before it gets sent
End With


Set oMSOutlook = Nothing
Set oEmail = Nothing


End Sub

 

[Anthony47]

Try with this variant:

For I = 2 To .....
    If Rows(I).Hidden = False Then
        .Recipients.Add(Cells(I, 4).Value).Type (3)
    End If
Next I

Bye