Select the first filtered cell then move onto the next filtered cell down
Try this code:
Sub SendEmail()
Dim objOutlook As Object
Dim objMail As Object
'Dim RowsCount As Integer
'Dim Index As Integer
Dim Recipients As String
Dim Category As String
Dim CellReference As Integer
Dim RowLimit As String
'New variables.
Dim firstRow As Long
Dim lastRow As Long
Dim cell As Excel.Range
Dim row As Long
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
Category = Range("D1")
If Category = "Email Address1" Then
CellReference = 4
ElseIf Category = "Email Address2" Then
CellReference = 5
End If
With ActiveSheet
'Find the first and last index of the visible range.
firstRow = .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).row
lastRow = .Cells(.Rows.Count, 1).End(xlUp).row
'Iterate through all the rows between [firstRow] and [lastRow] established before.
'Some of those rows are hidden, but we will check it inside this loop.
For row = firstRow To lastRow
Set cell = .Cells(row, CellReference)
'We are checking here if this row is hidden or visible.
'Note that we cannot check the value of property Hidden of a single cell,
'since it will generate Run-time error '1004' because a single cell cannot be
'hidden/visible - only a whole row/column can be hidden/visible.
'That is why we need to refer to its .EntireRow property first and after that we
'can check its .Hidden property.
If Not cell.EntireRow.Hidden Then
'If the row where [cell] is placed is not hidden, we append the value of [cell]
'to variable Recipients.
Recipients = Recipients & cell.Value & ";"
End If
Next row
End With
With objMail
.To = Recipients
.Subject = "This is the subject"
.Display
End With
Set objOutlook = Nothing
Set objMail = Nothing
End Sub
I believe the Hidden
property of a range is what you want. The following code worked for me:
Dim row As Range
For Each row In Range("MyTable").Rows
If not row.EntireRow.Hidden Then
''' DO STUFF '''
End If
Next
I have always found that using a For Each
loop is a much cleaner way to iterate through data in an excel sheet. "MyTable" was the name I gave to the range of interest but if you prefer you can just enter a the limits of the range like Range("A1:D4")
. Though I think it is a better practice to use named ranges as it makes your code more readable.
EDIT: To address your comment...
If you insert a row into the middle of a named range the limits of the range automatically expand. Though if your table is going to be the only data in the worksheet you can also use the UsedRange
property of a worksheet object. For instance:
Dim row As Range
For Each row In Worksheets("MySheet").UsedRange.Rows
If not row.EntireRow.Hidden Then
''' DO STUFF '''
End If
Next
If all you have is the first row of the table you can expand this range to the full table using:
dim FirstRow as Range
dim LastRow as Range
dim myTable as Range
set FirstRow = Range("A1:B1")
set LastRow = FirstRow.End(xlDown)
set myTable = Range(FirstRow, LastRow)
And then use the same For Each
loop as before. Hope this helps!