Sending Email Attachments by Data

anyone able to assist?

this has now been added, please oculd someone take a look as im struggling to find the best way to filter the departments and ensure that none are missed.

thanks

UPDATE: I have found the following code with filters the data perfectly but i need to alter it so instead of creating a new worksheet it sends the worksheet as an attachement to the email address in sheet 3. Would appreciate some help

Sub t()
 Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range, objRange As Range
    Dim rowcount As Long
    Dim colcount As Integer, FilterCol As Integer
    Dim SheetName As String
    
    
    Set ws1Master = ActiveSheet

top:

    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If
    FilterCol = objRange.Column
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    On Error GoTo progend
    'add filter sheet
    Set wsFilter = Sheets.Add
    With ws1Master
        .Activate
        .Unprotect Password:=""  'add password if needed
        rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
        colcount = .Cells(1, .Columns.Count).End(xlToLeft).Column
        If FilterCol > colcount Then
            Err.Raise 65000, "", "FilterCol Setting Is Outside Data Range.", "", 0
        End If
        Set Datarng = .Range(.Cells(1, 1), .Cells(rowcount, colcount))
        'extract Unique values from FilterCol
        .Range(.Cells(1, FilterCol), _
               .Cells(rowcount, _
                      FilterCol)).AdvancedFilter _
                      Action:=xlFilterCopy, _
                      CopyToRange:=wsFilter.Range("A1"), _
                      Unique:=True
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
        'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
            'check for blank cell in range
            If Len(FilterRange.Value) > 0 Then
                'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
                SheetName = RTrim(Left(FilterRange.Value, 31))
                'if FilterRange sheet exists
                'update it
                If SheetExists(SheetName) Then
                    Sheets(SheetName).Cells.Clear
                Else
                    'add new sheet
                    Set wsNew = Sheets.Add
                    wsNew.Move After:=Worksheets(Worksheets.Count)
                    wsNew.Name = SheetName
                End If
                Datarng.AdvancedFilter Action:=xlFilterCopy, _
                                       CriteriaRange:=wsFilter.Range("B1:B2"), _
                                       CopyToRange:=Sheets(SheetName).Range("A1"), _
                                       Unique:=False
            End If
        Next
        .Select
    End With
progend:
    wsFilter.Delete
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    If Err > 0 Then
        MsgBox (Error(Err)), vbCritical, "Error"
        Err.Clear
    End If
End Sub


Function SheetExists(wksName As String) As Boolean
    On Error Resume Next
    SheetExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0
End Function
End Function