Email validation?

Hi,


Is it possible to validate if an email contains a valid top-level-domain?

http://en.wikipedia.org/wiki/List_of...-level_domains

Perhaps someone has a function?


Thanks in advance,

CE

Charlotte E. wrote:

> Is it possible to validate if an email contains a valid top-level-domain?
>
> http://en.wikipedia.org/wiki/List_of...-level_domains

The actual list is currently he
http://www.iana.org/domains/root/db/

> Perhaps someone has a function?

The easiest and (IMO) best way is to put the valid TLDs in a spreadsheet and
check against that:
Function validTLD(what As String) As Boolean
Dim tld As String, L0 As Long
tld = UCase$(Mid$(what, InStrRev(what, ".")))
If IsNumeric(tld) Then validTLD = True: Exit Function
For L0 = 1 To 326 '->
If tld = TLDlist.Cells(L0, 1).Value Then validTLD = True: Exit Function
Next
End Function

If using a sheet isn't an option, you can build a big nasty array:
Function validTLD(what As String) As Boolean
Dim valid As Variant, tld As String, L0 As Long
tld = UCase$(Mid$(what, InStrRev(what, ".")))
If IsNumeric(tld) Then validTLD = True: Exit Function
valid = Array(".AC", ".AD", ..., ".ZM", ".ZW", ChrW(&H30C6) & _
ChrW(&H30B9) & ChrW(&H30C8), ...)
For L0 = 0 To UBound(valid)
If tld = valid(L0) Then validTLD = True: Exit Function
Next
End Function

....or a big nasty Select Case:
Function validTLD(what As String) As Boolean
Dim tld As String, L0 As Long
tld = UCase$(Mid$(what, InStrRev(what, ".")))
If IsNumeric(tld) Then validTLD = True: Exit Function
Select Case tld
Case ".AC", ".AD", ..., ".ZM", ".ZW", ChrW(&H30C6) & ChrW(&H30B9) & _
ChrW(&H30C8), ...
validTLD = True
End Select
End Function

....but for either of those you'd need to do the "ChrW & ChrW & ChrW" thing
about 50 times.

--
so many screams in my head
soooo many, many screams

Thanks for your suggestion :-)

My solution:

Let the VBA do a WebQuery to import the TLD list at WB startup, and then
test the given email, by a simple VLOOKUP.

Really not that hard once you got to think about it :-)


Thanks anyway...


CE




Den 09.04.2012 07:53, Auric__ skrev:
> Charlotte E. wrote:
>
>> Is it possible to validate if an email contains a valid top-level-domain?
>>
>> http://en.wikipedia.org/wiki/List_of...-level_domains
>
> The actual list is currently he
> http://www.iana.org/domains/root/db/
>
>> Perhaps someone has a function?
>
> The easiest and (IMO) best way is to put the valid TLDs in a spreadsheet and
> check against that:
> Function validTLD(what As String) As Boolean
> Dim tld As String, L0 As Long
> tld = UCase$(Mid$(what, InStrRev(what, ".")))
> If IsNumeric(tld) Then validTLD = True: Exit Function
> For L0 = 1 To 326 '->
> If tld = TLDlist.Cells(L0, 1).Value Then validTLD = True: Exit Function
> Next
> End Function
>
> If using a sheet isn't an option, you can build a big nasty array:
> Function validTLD(what As String) As Boolean
> Dim valid As Variant, tld As String, L0 As Long
> tld = UCase$(Mid$(what, InStrRev(what, ".")))
> If IsNumeric(tld) Then validTLD = True: Exit Function
> valid = Array(".AC", ".AD", ..., ".ZM", ".ZW", ChrW(&H30C6)& _
> ChrW(&H30B9)& ChrW(&H30C8), ...)
> For L0 = 0 To UBound(valid)
> If tld = valid(L0) Then validTLD = True: Exit Function
> Next
> End Function
>
> ....or a big nasty Select Case:
> Function validTLD(what As String) As Boolean
> Dim tld As String, L0 As Long
> tld = UCase$(Mid$(what, InStrRev(what, ".")))
> If IsNumeric(tld) Then validTLD = True: Exit Function
> Select Case tld
> Case ".AC", ".AD", ..., ".ZM", ".ZW", ChrW(&H30C6)& ChrW(&H30B9)& _
> ChrW(&H30C8), ...
> validTLD = True
> End Select
> End Function
>
> ....but for either of those you'd need to do the "ChrW& ChrW& ChrW" thing
> about 50 times.
>