Email visible entries only (filtered) with VBA

[jenksdev]

Hi all.

I am unable to attach files for some reason (it says I do not have permissions to) so, please bear with me.

I have created a sheet that looks like this: https://imgur.com/a/7pnr4M1

I have the following VBA doing the job:

Sub EmaiList()


    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Dim EmailList As Integer
    Dim strbody As String


    
    
With Application
    .ScreenUpdating = False
End With


' Define location of email list


EmailList = Application.Range("A1").CurrentRegion.Rows.Count


' Set definition of row


For r = 1 To EmailList


' Create body of email - note HTML


emailBody = "Dear " & Cells(r, 2) & ",<br><br>" & _
            "How are you?<br><br>" & _
            "Kind regards."
            
' Create the email


    Set OutMail = OutApp.CreateItem(0)


' Create email content


    With OutMail
        .To = Cells(r, 1).Value
        .Subject = "Tax reminder"
        .HTMLBody = emailBody & "<br>" & .HTMLBody
        .Save
    End With
    
' Action next row after opening previous email


Next r


' When no more rows, end query


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

There are two problems:

1. An error occurs on line one, as it tries to send an email to the row titled 'Email'. How can I skip this row?

2. It also includes the filtered address (note the filter is on, hiding row three). How can I have it only include the visible rows?

Any help is much appreciated.

Kind regards,

Ben

 

[Fluff]

How about

For R = 2 To EmailList
    If Rows(R).Hidden = False Then
        
        ' Create body of email - note HTML
        
        
        emailBody = "Dear " & Cells(R, 2) & "," & _
                    "How are you?" & _
                    "Kind regards."
                    
        ' Create the email
        
        
            Set OutMail = OutApp.CreateItem(0)
        
        
        ' Create email content
        
        
            With OutMail
                .To = Cells(R, 1).Value
                .Subject = "Tax reminder"
                .HTMLBody = emailBody & "" & .HTMLBody
                .Save
            End With
            
        ' Action next row after opening previous email
    End If

Next R

 

[jenksdev]

Brilliant, thank you.

I just couldn't get my head around those two points.

Much appreciated.

Ben

 

[Fluff]

Glad to help & thanks for the feedback