Option Explicit
Sub Test()
MsgBox Replace(listeMails, ";", vbLf)
End Sub
Function listeMails() As String
Dim Id0 As Integer
Dim Id1 As Integer
Dim M As Variant
Dim T0 As ListObject: Set T0 = [Résultatdexportduneliste3].ListObject ' Table feuille Taches
Dim T1 As ListObject: Set T1 = [Résultatdexportduneliste].ListObject ' Table feuille Destinataire
On Error Resume Next
Id0 = T0.ListColumns("Affectée à").Index ' Index de la colonne "Affectée à"
Id1 = T1.ListColumns("Code").Index ' Index de la colonne "Code"
M = T1.ListColumns("Adresse de messagerie").Index ' Index de la colonne des E-Mails
If T0.AutoFilter.Filters(Id0).On Then ' Filtre en amont
Select Case T0.AutoFilter.Filters(Id0).Count ' On filtre
Case 1
T1.Range.AutoFilter Field:=Id1, _
Criteria1:=T0.AutoFilter.Filters(Id0).Criteria1
Case 2
T1.Range.AutoFilter Field:=Id1, _
Criteria1:=T0.AutoFilter.Filters(Id0).Criteria1, _
Criteria2:=T0.AutoFilter.Filters(Id0).Criteria2, _
Operator:=T0.AutoFilter.Filters(Id0).Operator
Case Else
T1.Range.AutoFilter Field:=Id1, _
Criteria1:=T0.AutoFilter.Filters(Id0).Criteria1, _
Operator:=T0.AutoFilter.Filters(Id0).Operator
End Select
End If
M = WorksheetFunction.Transpose(T1.DataBodyRange.Columns(M).SpecialCells(xlCellTypeVisible).Value)
Select Case True
Case Err <> 0: ' Pas de lignes filtrées
Case VarType(M) = vbString: listeMails = M ' une seule ligne
Case Else: listeMails = Join(M, ";") ' plusieurs lignes
End Select
T1.Range.AutoFilter Field:=Id1 ' on enleve le filtre
End Function