Excel VBA Test if email address is valid

Hi

I use the following code to send a Word doc as the body of an email.
When there is an invalid email address I get the message below
I want to stop this prompt and rather do something else
Can I use code to test if the email address is valid

Sub SendDocAsMsg()
    Dim wd As Word.Application
    Dim doc As Word.Document
    Dim itm As Outlook.MailItem
    Dim ID As String
    Dim blnWeOpenedWord As Boolean
   
    Set wd = CreateObject("Word.Application")
   
    wd.Visible = True
   
    Set doc = wd.Documents.Open(Filename:="C:\Users\this\toEmail.doc", ReadOnly:=True)
    Set itm = doc.MailEnvelope.Item
    With itm
        .To = "this@email.com"
        .Subject = "My Subject"
        .Send
    End With
   
    doc.Close
    wd.Quit
 
    Set doc = Nothing
    Set itm = Nothing
    Set wd = Nothing
   
End Sub

Image3.png
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
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.

Roy CoxGroup Finance ManagerCommented:
Here's a Function that I found for a project years ago. It works in Excel and Word

Option Explicit



Public Function ValidEmail(pAddress As String) As Boolean
     '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
        ValidEmail = .Test(pAddress)
    End With
    Set oRegEx = Nothing
End Function

Open in new window


Place the code ina Stabdard Module.

Try adding it to your code like this


Sub SendDocAsMsg()
     Dim wd As Word.Application
     Dim doc As Word.Document
     Dim itm As Outlook.MailItem
     Dim ID As String
     Dim blnWeOpenedWord As Boolean
     
     Set wd = CreateObject("Word.Application")
     
     wd.Visible = True
     if not ValidEmail("this@email.com") then 
        msgbox "No valid email address provided"
       exit Sub
     End If

     Set doc = wd.Documents.Open(Filename:="C:\Users\this\toEmail.doc", ReadOnly:=True)
     Set itm = doc.MailEnvelope.Item
     With itm
         .To = "this@email.com"
         .Subject = "My Subject"
         .Send
     End With
     
     doc.Close
     wd.Quit
   
     Set doc = Nothing
     Set itm = Nothing
     Set wd = Nothing
     
 End Sub

Open in new window

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
[ fanpages ]IT Services ConsultantCommented:
The Regular Expression pattern stated above ("^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$") will incorrectly identify four (or longer) domain names as invalid.  For example, ".com" is accepted, but ".info", ".mobi", ".name", & ".museum" will be considered invalid.

There is a discussion of more suitable patterns here:

[ http://www.regular-expressions.info/email.html ]
0
Roy CoxGroup Finance ManagerCommented:
Interesting link, I'll be checking it out later. Thanks
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you both
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 Excel

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.