Avatar of pcalabria
pcalabria
Flag 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
Microsoft AccessOutlook

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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.
Joe Howard

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.
jonoakley

The function you are requesting would be a spammers dream.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim Dettman (EE MVE)

You can do a DSN lookup on the domain name.  See:

https://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.
Gustav Brock

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
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!  :-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bonjour-aut

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
bonjour-aut

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pcalabria

ASKER
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
Gustav Brock

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

/gustav
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pcalabria

ASKER
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!
Gustav Brock

You are welcome. Thanks for the feedback.

/gustav