Hi,
Here is macro that
split value in column F
populate column A
Populate column E
populate B with VLOOKUP formula that retrieves data from the other sheet

Sub SplitString()
    Dim cl As Range
    Dim vSplitString As Variant
    Dim intIndex As Integer
    Dim strName As String
    Dim rngEMails As Range
    
    With Worksheets("Email ID Data")
        Set rngEMails = .Range("A1").Resize(.Range("A" & _
                                                   Rows.Count).End(xlUp).Row, 2)
    End With

    For Each cl In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        vSplitString = VBA.Split(cl.Value, "\")
        intIndex = UBound(vSplitString)
        strName = vSplitString(intIndex)
        Range("A" & cl.Row).Value = Left(strName, InStrRev(strName, ".") - 1)    'Name
        Range("E" & cl.Row).Value = vSplitString(intIndex - 1)  'Subject
        Range("B" & cl.Row).FormulaR1C1 = "=VLOOKUP(RC1," & _
            rngEMails.Address(True, True, xlR1C1, True) & ",2,False)"
    Next cl

End Sub