Is there a way to validate an email address using vba?

Hello Experts,

Is there a way that I using code, can verify that an email address is likely valid?
I've written code to do the basic stuff, like check for the @ sign, dots, and so forth,
but my cod can not tell whether the domain or mailbox is valid.

Thanks
pcalabriaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
there is no way to tell if the domain or mailbox is valid unless you have a table of valid domains and mailboxes for comparison.
0
MacroShadowCommented:
Most likely, the only way is to send the email :-)

btw, I wrote a short article on regex validations, in the article you will find a regex validation to validate most email addresses.
For a complete email validation pattern see this.
0
jonoakleyCommented:
The function you are requesting would be a spammers dream.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can do a DSN lookup on the domain name.  See:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27734066.html

For some code.  I'm sure there are many other variations out there.

I would not however rely on a ping, as many sites won't respond to a ping, but a DNS lookup will not fail to validate a domain.

Jim.
0
Gustav BrockCIOCommented:
You can't. Even if you send a mail to the address and it is received with no error, it may have been routed directly to a spam box or black hole at the receiving mail server.

The only 100% sure method is to send a mail to the address and request  a manual confirmation by a reply mail.

/gustav
0
pcalabriaAuthor Commented:
Perhaps I should have explained the application better...

We have about 35,000 email addresses of customers who have purchased products from us throughout the years.  All have opted-in to receiving emails from us, and all have corresponded at the time of purchase using the email address provide, although we have never initiated a bulk email campaign.

We tried to do so this weekend, using bulk email services including Constant Contact and BenchMark Email, however, when we uploaded the list, the service was refused because the service determined we had too many bad email addresses in our list.

I'm sure this is due to attrition of personnel and companies...

So the project is to clean up our lists enough for the Bulk Email Service to accept the list.

JIM...
I tried the code you provided, and I was able to get it to work, or at least, to run.  I'm not sure if I trust the results, as several of the items flagged as BAD do have working websites as that domain, and seem to be still using the same domain for email.   For example: sanda.n@taviscorp.com did fail, but seems like the website works.

By the way, I did have to make two changes to get the code to run... not to  say my changes are correct... I'm certainly not a pro at this... however, Windows 8 did not liked the file outputting to the c:\ root... so I changed it to a path where documents are stored... also, file 1 was not being closed when the process was true, so I added a close before the Exit Funtion.  With these two changes, I did get results... which are the results I mentioned above.

Macro...
I looked at your link... I felt like I was back in Kindergarten... I had not idea what that said but neither did I when I was in kindergarten... You're WAY ahead of me!  :-)
0
bonjour-autCommented:
I would like to point at a PHP posting, which eyplains the principal questions involved very clearly:
http://www.devshed.com/c/a/php/email-address-verification-with-php/
Having looked through this, it might be the closest test to try to open a tcp socket at port 25 as the dns lookup may fail for various reasons although the mailadress is valid.
A very simple method would be to let the job to be done by a php-script or you try to test the socket connection with VBA code.
0
bonjour-autCommented:
I tested a little bit and come to the conclusion, that the port25 test maybe to restrictive as many hosts refuse connection nowadays. Checking just for MX record maybe better.

php testscript:

<?php
$domain = trim($_GET['md']);  
  if(!checkdnsrr($domain, 'MX')) {
   echo 'no MX-record<br />';
   exit();
  }else{
   echo 'MX-record OK<br />';
  }
  if(!fsockopen($domain,25,$errno,$errstr,30)) {
   echo 'error:'.$errno."=".$errstr.'<br />';
   exit();
   }else{
   echo 'P25 socket OK<br />';
  }
  echo 'success<br />';
?>

If necessary, i can post some code, how to wrap the web-request into  a VBA function.
0
Gustav BrockCIOCommented:
>  the service was refused because the service determined we had too many bad email addresses in our list.

But that's the way to clean up your list. Except for basic syntax check, there is only trial and error.

You could build your own routine to perform this, but it isn't worth the time. Several services take care of this, for example Atomic Mail Verifier:

   http://www.amailsender.com/amv/

It's only €40 and seems to do the job, so I wouldn't hesitate (I'm not related to the supplier in any way).

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcalabriaAuthor Commented:
Bonjour... I can't speak php so I'd love to see your code in VBA!

Gustav...I agree about the price but lots may have changed since this program, that's lists Windows Vista compatibility, was introduced.  Do you know anything about it? Does it work well?  The trial does not allow download so there's no way I can tell if it did the job.

BTW...Eliminating good email addresses from our list can be extremely costly.  For example, one of the programs I tried eliminated all of my addresses at NASA send JPL, including addresses that we know are valid.

Thanks again... I will try
0
Gustav BrockCIOCommented:
No, I have no experience.
Email hasn't changed much since Vista, so it should work fine with Windows 7-10.

/gustav
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< For example: sanda.n@taviscorp.com did fail, but seems like the website works. >>

 I'd have to dig into it to see why it didn't work.  The link I posted was not my code.

 What I was simply pointing out is that it is possible to validate a domain through DNS using DSLOOKUP and there are tons of 3rd party apps as gustav pointed out and VBA code floating around to do it.

  What you want to do is look up the MX record for the domain and I know that can be done.  I use this site:

http://mxtoolbox.com/

 all the time, so I know it's quite doable.  In fact now that I say that, I just reminded myself of something.   vbSendMail, which is VB6 code (drops straight into Access though) has:

IsValidEmailAddress()

and

MXQuery()

 either of which you could use to do what you want.  vbSendMail can be found here:

http://www.freevbcode.com/ShowCode.asp?ID=109

Note this is a different approach than what I posted earlier, which was using the Windows command line utility NSLOOKUP().

You will find this more robust.  Sorry I didn't think of this before.

Jim.
0
pcalabriaAuthor Commented:
Thanks everyone!

I splits the points based upon the help provided as well as how my problem was solved.
Jim's solution, vbSendMail, was exactly the solution I was seekig.  While not 100% its certainly good enough.
With Jims solution being the one to answer my question, Gustav is certainly on target... the Atomic program is not expensive and turn-key.

Thanks everyone!
0
Gustav BrockCIOCommented:
You are welcome. Thanks for the feedback.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.