Struggling with macro that has pivot filtering and creates an email

[ReventonKing]

Hello guys!

I'm new to this forum and new to VBA in general.

The circumstances: I have a workbook with 26000 rows of data. It changes everyday as well. In this workbook I have a worksheet with a pivot table that contains names and data belonging to them.

The project: I'm working on a macro that
1. Filters for a name,
2. Creates a new file based on the visible data,
3. Sends an email with the containing file to the email address belonging to the filtered name,
4. Deletes the file after the email has been sent,
5. And goes automatically to the next name and starts the cycle again.

The problems:
First, for some reason it doesn't work at all. Probably due to my lack of knowledge it has many mistakes.
Second, I don't have just the names in the column that it filters, I have many items starting with a "#" or something else, which is not a name, how do I exclude them from the filtering process?
Third, the email addresses are in another workbook, how do I filter for that based on the filtered name and add it to this code?
Forth, I don't think my "paste" line is good, I'm not sure if it would paste the copied data into the new file or not. I'm not able to test it, since my code stops well before.

Here is the code I've written so far:

Sub EMAILPIVOTMACRO()


Dim Pttable As PivotTable
Dim Ptitem As PivotItem
Dim Emailsheet As Worksheet
Dim Fname As String
Dim OutApp As Object
Dim OutMail As Object


'Selecting Worksheet on which the Pivot table takes place'
Worksheets("Closing Dates").Select


'Make sure no filter is applied as of yet to the pivot table (THIS PART DOESNT WORK)'
With ActiveSheet.AutoFilterMode = False
End With


'Selecting the Pivot table and starting the loop'
Set Pttable = ActivateSheet.PivotTables("Closing Dates")
For Each Ptitem In Pttable.PageFields("Catcher_NEW").PivotItems
        Pttable.PageFields("Catcher_NEW").CurrentPage = Ptitem.Name
        
    'Select and copy Visible Range after the applied filter'
    Range("A4:E100").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    'Create a new workbook'
    Set Emailsheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    'Paste the originally copied data from pivot'
    Sheets("Sheet1").Select
    Range("A1:E100").Select
    ActiveSheet.Paste
    
    'Save file'
    Fname = "C:\Users\...\EmailTemp\closingdatesblank.xlsx"
    ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    
    'Create email with the just saved file inserted'
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    
    
    
        'Find email address (NOT YET IN USE)'
        
    
        'Send email'
        With OutMail
            .to = "asd"
            .CC = ""
            .BCC = ""
            .Subject = "asd"
            .Body = "asd"
            .Attachments.Add ("C:\Users\...\EmailTemp\closingdatesblank.xlsx")
            .Send
        End With
    
    'Delete the created file'
    Kill "C:\Users\...\EmailTemp\closingdatesblank.xlsx"
    
    'Goes to the next pivot item and starts from to the begining of the cycle'
    Next Ptitem
End Sub

Thank you for any help! (I apologize if I didn't meet the posting guidelines.)