Add image to attached code

Option Explicit
 
 
Sub ProcessAbsentees()
    Dim dt As Date
    Dim rw As Long
    Dim col As Integer
    Dim rowcount As Long
    Dim rng As Range, pRange As Range, C As Range, p As Range
    Dim firstAddress As String
 
    With ThisWorkbook.ActiveSheet
        dt = Date
        rw = .Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row
        Set rng = .Range("a3:z3")
        Set rng = rng.Cells.Find(dt, LookIn:=xlValues).Offset(0, 1)
        Set rng = Range(Cells(7, rng.Column), Cells(ActiveSheet.UsedRange.Rows.Count, rng.Column))
 
        With rng
            Set C = .Find("N", , xlValues, xlWhole)
            If Not C Is Nothing Then
                firstAddress = C.Address
                Set pRange = C
                Do
                Set C = .FindNext(C)
                    Set pRange = Union(C, pRange)
                Loop While Not C Is Nothing And C.Address <> firstAddress
            End If
            
                      
            For Each p In pRange
                Cells(6, rng.Column - 1).FormulaR1C1 = "=SUMPRODUCT((R[1]C:R[200]C=R[" & p.Row - 6 & "]C)*(R[1]C[1]:R[200]C[1]=""Y""))"
                Call MailEm(p.Offset(0, -1).Value, Cells(p.Row, "A").Value, Cells(6, rng.Column - 1).Value)
            Next
 
        End With
        Cells(6, rng.Column - 1).Clear
    End With
End Sub
 
Sub MailEm(siteName As String, empName As String, empNum As String)
    Dim olApp    'As Outlook.Application
    Dim olNS    'As Outlook.Namespace
    Dim mai    'As mailitem
    Set olApp = CreateObject("outlook.application")
    Dim siteList As Range, C As Range
    Dim oContact As String, OContactTel
 
    Set siteList = ThisWorkbook.Sheets("Site List").Columns("B:B")
    Set C = siteList.Find(siteName, , xlValues, xlWhole)
 
    If Not C Is Nothing Then
        oContact = C.Offset(0, 2)
        OContactTel = C.Offset(0, 3)
    Else
        oContact = "absences@advent.local"
    End If
 
    If OContactTel = "" Then OContactTel = "XXXXX"
 
    Set mai = olApp.CreateItem(0)
    mai.To = oContact
    mai.Subject = C.Offset(0, -1) & " Abscenses " & Date
    mai.Body = "Hi " & C.Offset(0, 1) & "," & vbCrLf & vbCrLf
    mai.Body = mai.Body & "Unfortunately " & empName & " has called in sick this morning however there will still be " & empNum & " other operatives on site.  Our apologies for any inconviniencies this may cause,   a representative from Advanced Ventilation will contact you after 9:00am on " & OContactTel
    mai.Body = mai.Body & vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & "Advanced Ventilation - In Partnership with Nuaire"
    mai.Body = mai.Body & "Tel: (01204) 523384"
    mai.Body = mai.Body & "Fax: (01204) 389849"
    mai.Body = mai.Body & vbCrLf & vbCrLf & vbCrLf & vbCrLf & "This is an automated email to use an alternative email for this information please contact Adanced Ventilation"
       
    mai.Send
 
    Set mai = Nothing
    Set olApp = Nothing
 
End Sub