Solved

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

Posted on 2014-11-16
14
269 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 26

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
identify all unused queries, forms and reports 10 29
lync, outlook 1 20
Help with SQl and UNION 7 16
select over clause 1 10
Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
If you don't know how to downgrade, my instructions below should be helpful.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 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

14 Experts available now in Live!

Get 1:1 Help Now