VBA: vérifier validité email (saisie dans TextBox)

Private Sub CommandButton1_Click()
'macro originale: chip pearson
'source:microsoft.public.excel.misc
'mot-clé: email validation
If Len(TextBox1) - Len(Replace(TextBox1, "@", "")) = 1 And _
Len(TextBox1) - Len(Replace(TextBox1, ".", "")) = 1 Then
   If TextBox1 Like "?*@?*.?*" Then
        Debug.Print "OK"
   Else
       MsgBox "L'adresse mail saisie est invalide"
    End If
Else
    MsgBox "L'adresse mail saisie est invalide"
    TextBox1 = vbNullString
End If
End Sub

Sub verifmail()
Dim s As String
Dim chaine As String
s = "[EMAIL="prenomnom@domaine.fr"]prenomnom@domaine.fr[/EMAIL]"
chaine = "*[/\*-+&|<>(){}:!£$¤%ùéèàµ,?;]*"
If Len(s) - Len(Replace(s, "@", "")) = 1 And _
Len(s) - Len(Replace(s, ".", "")) = 1 And Not s Like chaine Then
If s Like "?*@?*.?*" Then
MsgBox "Adresse email valide"
Else
MsgBox "L'adresse mail saisie est invalide"
End If
Else
MsgBox "L'adresse mail saisie est invalide"
 
End If
End Sub

[SIZE=2][COLOR=NAVY]Sub[/COLOR] Test()
    MsgBox IIf(VerifMail("mon_email@serveur.com"), "Adresse OK", "Adresse refusée")
[COLOR=NAVY]End Sub[/COLOR]


[COLOR=NAVY]Function[/COLOR] VerifMail([COLOR=NAVY]ByVal[/COLOR] adrEmail [COLOR=NAVY]As String[/COLOR]) [COLOR=NAVY]As Boolean[/COLOR]
[COLOR=GREEN]'myDearFriend! - www.mdf-xlpages.com[/COLOR]
[COLOR=NAVY]Dim[/COLOR] T [COLOR=NAVY]As String
Dim[/COLOR] C [COLOR=NAVY]As Byte[/COLOR]
    T = UCase(adrEmail)
    [COLOR=NAVY]If[/COLOR] Len(Replace(T, Chr(64), "")) <> Len(T) - 1 [COLOR=NAVY]Then Exit Function
    For[/COLOR] C = 45 [COLOR=NAVY]To[/COLOR] 95
        [COLOR=NAVY]Select Case[/COLOR] C
        [COLOR=NAVY]Case[/COLOR] 45 [COLOR=NAVY]To[/COLOR] 46, 48 [COLOR=NAVY]To[/COLOR] 57, 64 [COLOR=NAVY]To[/COLOR] 90, 95
            T = Replace(T, Chr(C), "")
            [COLOR=NAVY]If[/COLOR] Len(T) = 0 [COLOR=NAVY]Then Exit For
        End Select
    Next[/COLOR] C
    [COLOR=NAVY]If[/COLOR] Len(T) = 0 [COLOR=NAVY]Then
        If[/COLOR] adrEmail [COLOR=NAVY]Like[/COLOR] "?*@??*.??*" [COLOR=NAVY]Then[/COLOR] VerifMail = [COLOR=NAVY]True
    End If
End Function[/COLOR][/SIZE]

Private Sub WorkBook_Open()
[COLOR=Green] '***Ajoute la référence Microsoft VBScript Regular Expression 5.5[/COLOR]
Dim X As String
X = "C:\WINDOWS\System32\vbscript.dll\3"
ThisWorkbook.VBProject.References.AddFromFile X
End Sub

Private Sub WorkBook_Open()
[COLOR=Green] '***Ajoute la référence Microsoft VBScript Regular Expression 5.5[/COLOR]
Dim X As String
'Pour PC sous Windows 98 SE
X = "C:\WINDOWS\SYSTEM\vbscript.dll\3"
ThisWorkbook.VBProject.References.AddFromFile X
End Sub

Private Function verifier_syntaxe_mel(mel As String) As Boolean
     Dim reg as object
     Dim verif As Object
     
     Set Reg = CreateObject("vbscript.regexp")
     Reg.Global = False
     'admet les majuscules
     Reg.IgnoreCase = True
     
     Reg.Pattern = "^[a-z0-9_.-]+@[a-z0-9.-]{2,}\.[a-z]{2,3}$"
     Set verif = Reg.Execute(mel)
     verifier_syntaxe_mel = (verif.Count = 1)
     
     Set verif = Nothing
     Set Reg = Nothing
   End Function