If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Email validation?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Email validation?

#1
April 8th 12, 11:31 PM posted to microsoft.public.excel.programming
 Charlotte E.[_3_] external usenet poster Posts: 96
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?

CE

#2
April 9th 12, 06:53 AM posted to microsoft.public.excel.programming
 Auric__ external usenet poster Posts: 302
Email validation?

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

--
so many screams in my head
soooo many, many screams
#3
April 9th 12, 10:39 AM posted to microsoft.public.excel.programming
 Charlotte E.[_3_] external usenet poster Posts: 96
Email validation?

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
>

 Thread Tools Display Modes Hybrid Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Using Macro how to create email link for the email addresses in aRange or Selection Satish[_2_] Excel Worksheet Functions 8 December 28th 09 03:30 PM Excel VBA macro to send email attachment from default email client wifigoo Excel Programming 2 April 12th 08 03:54 PM how do you email a portion of a worksheet (range) as an insert into the body of an email? Bruce[_2_] Excel Programming 3 May 31st 07 10:37 PM email validation? GoBobbyGo Excel Discussion (Misc queries) 4 April 18th 06 11:41 PM data validation and using hyperlink to email Anastassia Excel Worksheet Functions 0 May 14th 05 09:55 AM

All times are GMT +1. The time now is 02:47 AM.