Solved

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

Posted on 2014-11-16
14
274 Views
Last Modified: 2014-11-22
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
0
Comment
Question by:pcalabria
  • 4
  • 3
  • 2
  • +4
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40446046
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40446055
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
 
LVL 9

Expert Comment

by:jonoakley
ID: 40446093
The function you are requesting would be a spammers dream.
0
 
LVL 57
ID: 40446102
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40446207
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
 

Author Comment

by:pcalabria
ID: 40446261
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40446292
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 50 total points
ID: 40446433
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 75 total points
ID: 40446800
>  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
 

Author Comment

by:pcalabria
ID: 40447005
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40447017
No, I have no experience.
Email hasn't changed much since Vista, so it should work fine with Windows 7-10.

/gustav
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 375 total points
ID: 40447181
<< 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
 

Author Closing Comment

by:pcalabria
ID: 40459324
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40459330
You are welcome. Thanks for the feedback.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Resolve DNS query failed errors for Exchange
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now