Bulk Email address verification through macro?

[vishwakarma]

Hi Team,

Is it possible to verify email address in bulk through macro?

I have a list of email addresses in excel sheet and want to verify them from a web site if they are valid deliverable e-mail address or not.

Here is the website link :- http://www.ipaddresslocation.org/email_lookup/check-email.php


Appreciate your help on this…

[vishwakarma]

Hi Guys,

I did a research on this and got a sample code from a site (from ‘JP SOFTWARE TECHNOLOGIES’). However it is not working properly. The function returns FALSE for every email addresses.

Can anyone help me with this?

Below is the code link:

http://www.jpsoftwaretech.com/email-validation-in-vba/

[vishwakarma]

I would really appreciate if anyone can help me on this...:help

[vishwakarma]

Can anyone help me with this? Sorry for bugging... But this is something I am really looking forward to.

Many Thanks,

[anandbohra]

Try this

Validating an email


http://www.vbaexpress.com/kb/getarticle.php?kb_id=281#instr

[Kenneth Hobs]

Your first example website does not work properly either. JP's example always returns False from that website as well. It uses old Soap methods so it is not surprising.

If it were me, I would validate email syntax and then validate the domain. The last part to validate a domain would be something like JP's example with a tweak.

For the syntax method, use the referenced kb article or try this regex method based on a kb post married with a pattern string.

' http://www.regular-expressions.info/email.html


Sub Test_ValidEmail()
Debug.Print ValidEmail("kenneth.ray.hobson@gmail.comm")
End Sub

' http://www.vbaexpress.com/kb/getarticle.php?kb_id=68
Function ValidEmail(sEmail As String) As Boolean
'Reference must be set to Microsoft VbScript Regular Expression 5.5
'Dimension the RegExp objects
Dim RegEx As VBScript_RegExp_55.RegExp, RegMatchCollection As VBScript_RegExp_55.MatchCollection
Dim RegMatch As VBScript_RegExp_55.Match, SubMatch As VBScript_RegExp_55.SubMatches

Set RegEx = New RegExp
' set the RegExp parameters
With RegEx
.MultiLine = False
.Global = True
.IgnoreCase = True
.Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b"
End With

' create the collection of Matches
Set RegMatchCollection = RegEx.Execute(sEmail)
ValidEmail = (RegMatchCollection.Count > 0)
End Function

[vishwakarma]

Hi Kenneth/Anand,

Many Thanks for sharing the link and the code. I really appreciate it. However, my requirement is a little different here. Sorry, if I was not clear on this before. What I’m looking here is to identify whether a specific email address is still working or not (If we can send email to it).

I can verify the working email ids with the help of this link (http://www.ipaddresslocation.org/email_lookup/check-email.php). But it only works for one email id at one time. What if I need to verify multiple email at one go.


Hope I was able to explain my requirement clearly this time…Many Thanks for the help.

[vishwakarma]

Hi Guys,

Can anyone help me or guide me on this?

Thanks,

[vishwakarma]

Hey guys,

Can anybody please guide me on this?

I have a basic knowledge of VBA but if anyone can guide me on this I might be able to get the solution.

Thanks,

[anandbohra]

Check if this site helps you


http://tools.email-checker.com/Mining/EmailExtractor.aspx

http://www.youtube.com/watch?v=viYFL6Ssrro

[vishwakarma]

Thanks Anand... but again we can only check single email at a time.

What if I want to verify multiple emails and check whether these email exists. Let's say I have 100 emails in a excel sheet and wants to verify the email list. So, basically I don't want to verify the list manually but with the help of macro.

Hope I'm clear in stating my question this time...

Thanks,

[anandbohra]

You can upload excel file with 50000 email to verify at once
See all features of that Web site

[anandbohra]

Thanks Anand... but again we can only check single email at a time.

What if I want to verify multiple emails and check whether these email exists. Let's say I have 100 emails in a excel sheet and wants to verify the email list. So, basically I don't want to verify the list manually but with the help of macro.

Hope I'm clear in stating my question this time...

Thanks,

you did not did full research on provided link, first of all the purpose of discussion forum is to get answer but it does not means we get all answer in our way only.
the given link solve all you purpose, you just need to supply excel or csv file & they will do the needful