Link to home
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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.
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.
The function you are requesting would be a spammers dream.
You can do a DSN lookup on the domain name.  See:

https://www.experts-exchange.com/questions/27734066/VBA-Code-to-Check-MX-Record.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.
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
Avatar of pcalabria

ASKER

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!  :-)
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.
SOLUTION
Avatar of bonjour-aut
bonjour-aut
Flag of Austria image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
No, I have no experience.
Email hasn't changed much since Vista, so it should work fine with Windows 7-10.

/gustav
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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!
You are welcome. Thanks for the feedback.

/gustav