Excel Function for Checking Email Syntax

[atr000]

Hi,

Is there a way to create a macro/vba function in excel so that I can get a pulldown menu to run a syntax after doing a Siebel dump of emails?

Many of the emails are wrong or are incomplete and I wanted to have a way to go serially through these and have a "do you want to delete? y/n" entry like that.

Have programmed lightly in perl/html before, but am very unfamiliar with the syntax to Excel/VBA.

Thanks.

[crazybass2]

ATR,

Glad to help, but I'm unfamiliar with the Siebel dump you refer to. Are the
emails dumped as text files or into one large CSV or XLS file? Are you
talking about email files or just email addresses? Do you only want to get
the "Do you want to delete?" only for wrong or incomplete emails, or for each
entry?

Mike

"atr000" wrote:

>
> Hi,
>
> Is there a way to create a macro/vba function in excel so that I can
> get a pulldown menu to run a syntax after doing a Siebel dump of
> emails?
>
> Many of the emails are wrong or are incomplete and I wanted to have a
> way to go serially through these and have a "do you want to delete?
> y/n" entry like that.
>
> Have programmed lightly in perl/html before, but am very unfamiliar
> with the syntax to Excel/VBA.
>
> Thanks.
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[NickHK]

Add a reference to "Microsoft VBScript Regular Expressions 5.5" and use a
function like this, taken from VBScript, so declare variable etc):

Function RegExpTest(sEmail)
RegExpTest = False
Dim regEx, retVal
Set regEx = New RegExp
' Create regular expression:
regEx.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' Set pattern:
regEx.IgnoreCase = True
' Set case sensitivity.
retVal = regEx.Test(sEmail)
' Execute the search test.
If Not retVal Then
Exit Function
End If
RegExpTest = True
End Function

or maybe this
"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-
Z\.]*[a-zA-Z]$"

I don't use RegExp, so I can tell you the syntax you need, but there are
various help/versions on the net.

NickHK

"atr000" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Is there a way to create a macro/vba function in excel so that I can
> get a pulldown menu to run a syntax after doing a Siebel dump of
> emails?
>
> Many of the emails are wrong or are incomplete and I wanted to have a
> way to go serially through these and have a "do you want to delete?
> y/n" entry like that.
>
> Have programmed lightly in perl/html before, but am very unfamiliar
> with the syntax to Excel/VBA.
>
> Thanks.
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile:
http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>

[atr000]

Mike,

Siebel dumps to a huge XLS. One of the categories is "Email" and I wanted to be able to syntax check just that.

Thanks.

ATR,

Glad to help, but I'm unfamiliar with the Siebel dump you refer to. Are the
emails dumped as text files or into one large CSV or XLS file? Are you
talking about email files or just email addresses? Do you only want to get
the "Do you want to delete?" only for wrong or incomplete emails, or for each
entry?

Mike

"atr000" wrote:

>
> Hi,
>
> Is there a way to create a macro/vba function in excel so that I can
> get a pulldown menu to run a syntax after doing a Siebel dump of
> emails?
>
> Many of the emails are wrong or are incomplete and I wanted to have a
> way to go serially through these and have a "do you want to delete?
> y/n" entry like that.
>
> Have programmed lightly in perl/html before, but am very unfamiliar
> with the syntax to Excel/VBA.
>
> Thanks.
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[crazybass2]

ATR,

What formats do you want to allow? Or do you want to check each email addy
individually?

ie.
[email protected] -> Good
name.provider.com -> Bad
[email protected] -> Good
@provider.com -> Bad

Do you just want to check for the existence of "@" and "." ?

Give me a few details and I'll work something up. Also, what column is
Email in?

Mike

"atr000" wrote:

>
> Mike,
>
> Siebel dumps to a huge XLS. One of the categories is "Email" and I
> wanted to be able to syntax check just that.
>
> Thanks.
>
> crazybass2 Wrote:
> > ATR,
> >
> > Glad to help, but I'm unfamiliar with the Siebel dump you refer to.
> > Are the
> > emails dumped as text files or into one large CSV or XLS file? Are
> > you
> > talking about email files or just email addresses? Do you only want to
> > get
> > the "Do you want to delete?" only for wrong or incomplete emails, or
> > for each
> > entry?
> >
> > Mike
> >
> > "atr000" wrote:
> >
> > >
> > > Hi,
> > >
> > > Is there a way to create a macro/vba function in excel so that I can
> > > get a pulldown menu to run a syntax after doing a Siebel dump of
> > > emails?
> > >
> > > Many of the emails are wrong or are incomplete and I wanted to have
> > a
> > > way to go serially through these and have a "do you want to delete?
> > > y/n" entry like that.
> > >
> > > Have programmed lightly in perl/html before, but am very unfamiliar
> > > with the syntax to Excel/VBA.
> > >
> > > Thanks.
> > >
> > >
> > > --
> > > atr000
> > >
> > ------------------------------------------------------------------------
> > > atr000's Profile:
> > http://www.excelforum.com/member.php...o&userid=36498
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=562542
> > >
> > >
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[atr000]

Mike,

Thanks for the response.

I tried what the function was above and get a "User-defined type not defined" message.

Basically, my emails consist of mostly [email protected], [email protected] - the regEXP I really want to check for a valid: [email protected] so it will pass an outlook test, nothing more.

I wanted to possibly highligh a whole column and click a button to a subrouting (is this possible) and then just run it only on the highlighted column.

I am very unfamiliar with the Excel scripting, so I can figure some of the things out like adding functions, but much of this is greek, unfortunately.

Thanks.
-Andre

[crazybass2]

Andre,

Place the following code in the sheet module (right click the sheet tab and
select view code) of the sheet where your email addys are. Then select the
email addresses and run the macro. This macro will go through each email
selected and flag you when an email address is not of the form
[email protected] where something may have name.name syntax and sss is
any extension. I think by viewing the code you can understand what each
condition is. If not let me know and I'll be happy to explain.

As it is now this code will not check for the existance of special
characters (^&$#, etc.) that you would not want in an email address. I think
that's what NickHK was trying to state in his. I will look into his code and
see it there is something I can work.

Mike
Option Explicit
Sub CheckEmailValidity()
Dim MyRange As Range, eaddy As Range
Dim flag As Boolean, dltemail As Variant
Set MyRange = Selection
For Each eaddy In MyRange
If InStr(1, eaddy, "@") = 0 Then
flag = True
ElseIf InStr(1, eaddy, ".") = 0 Then flag = True
ElseIf InStr(1, eaddy, "@") < 2 Then: flag = True
ElseIf InStrRev(eaddy, ".") < 2 Then: flag = True
ElseIf InStrRev(eaddy, "@") >= InStrRev(eaddy, ".") - 1 Then: flag = True
ElseIf InStrRev(eaddy, ".") = Len(eaddy) Then: flag = True
Else: flag = False
End If
If flag Then
dltemail = MsgBox("The email address " & eaddy & " is invalid. Would
you like to delete it?", _
vbYesNoCancel, "Invalid Email Address")
If dltemail = vbYes Then eaddy.ClearContents
If dltemail = vbCancel Then Exit Sub
'eaddy.Offset(0, -1).Value = "Bad"
End If
Next eaddy
End Sub


"atr000" wrote:

>
> Mike,
>
> Thanks for the response.
>
> I tried what the function was above and get a "User-defined type not
> defined" message.
>
> Basically, my emails consist of mostly [email protected],
> [email protected] - the regEXP I really want to check for a valid:
> [email protected] so it will pass an outlook test, nothing more.
>
> I wanted to possibly highligh a whole column and click a button to a
> subrouting (is this possible) and then just run it only on the
> highlighted column.
>
> I am very unfamiliar with the Excel scripting, so I can figure some of
> the things out like adding functions, but much of this is greek,
> unfortunately.
>
> Thanks.
> -Andre
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[atr000]

Mike,

This code works perfectly. I can't tell you how many folks in research/sales will be happy when we send out emails to large numbers (I don't work for a spammer). I am wondering if these two things could be added to the working code:

1) If the entry is blank (we have many w/o emails) can it just be skipped?
2) Can there be an option to Delete, like there is, and and add in an option to "Go to that Cell", like an edit option?

Thanks,
Andre

[crazybass2]

Andre,

This should do what you requested.

Option Explicit
Sub CheckEmailValidity()
Dim MyRange As Range, eaddy As Range
Dim flag As Boolean, dltemail As Variant
Dim updatenow As String
Set MyRange = Selection
For Each eaddy In MyRange
rerun:
If eaddy = "" Then
flag = False
ElseIf InStr(1, eaddy, "@") = 0 Then flag = True
ElseIf InStr(1, eaddy, ".") = 0 Then flag = True
ElseIf InStr(1, eaddy, "@") < 2 Then flag = True
ElseIf InStrRev(eaddy, ".") < 2 Then flag = True
ElseIf InStrRev(eaddy, "@") >= InStrRev(eaddy, ".") - 1 Then flag = True
ElseIf InStrRev(eaddy, ".") = Len(eaddy) Then flag = True
Else: flag = False
End If
If flag Then
dltemail = MsgBox("The email address " & eaddy & " is invalid. Would
you like to modify it?", _
vbYesNoCancel, "Invalid Email Address")
If dltemail = vbYes Then
updatenow = MsgBox("Would you like to update this address? No will
clear the email address", vbYesNo, "Update Email")
If updatenow = vbYes Then
eaddy.Value = InputBox("Please enter a valid email address", "Update
Email", eaddy.Value)
GoTo rerun
End If
eaddy.ClearContents
End If
If dltemail = vbCancel Then Exit Sub
End If
Next eaddy
End Sub


Mike

"atr000" wrote:

>
> Mike,
>
> This code works perfectly. I can't tell you how many folks in
> research/sales will be happy when we send out emails to large numbers
> (I don't work for a spammer). I am wondering if these two things could
> be added to the working code:
>
> 1) If the entry is blank (we have many w/o emails) can it just be
> skipped?
> 2) Can there be an option to Delete, like there is, and and add in an
> option to "Go to that Cell", like an edit option?
>
> Thanks,
> Andre
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[crazybass2]

Andre,

Had some time to readup on the RegExp object. Thank you Nick for opening my
eyes to this object, I can see it being very usefull.

I integrated Nicks code into mine so that it will work the same way. I also
modified his pattern a bit to be a little more flexible with the email
address matches. To get a better idea of the patterns out there for email
visit http://regexlib.com/ and type "email" in the Keyword search.

As Nick noted, to utilize this code you will need to include a reference to
"Microsoft VBScript Regular Expressions 5.5" in the VB editor. To do this,
click Tools->References. Scroll down the list and put a checkmark in the box
next to the reference name.

Here's the code:

Sub Check2()
Dim MyRange As Range, eaddy As Range
Dim flag As Boolean, dltemail As Variant
Dim updatenow As String
Set MyRange = Selection
For Each eaddy In MyRange
rerun:
flag = RegExpTest(eaddy)
If Not flag Then
dltemail = MsgBox("The email address " & eaddy & " is invalid. Would
you like to modify it?", _
vbYesNoCancel, "Invalid Email Address")
If dltemail = vbYes Then
updatenow = MsgBox("Would you like to update this address? No will
clear the email address", vbYesNo, "Update Email")
If updatenow = vbYes Then
eaddy.Value = InputBox("Please enter a valid email address", "Update
Email", eaddy.Value)
GoTo rerun
End If
eaddy.ClearContents
End If
If dltemail = vbCancel Then Exit Sub
eaddy.Offset(0, 4) = flag
End If
Next eaddy
End Sub
Function RegExpTest(sEmail)
RegExpTest = False
Dim regEx, retVal
Set regEx = New RegExp
' Create regular expression:
regEx.Pattern =
"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z]*[-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,3})$"
' Set pattern:
regEx.IgnoreCase = True
' Set case sensitivity.
retVal = regEx.Test(sEmail)
' Execute the search test.
If Not retVal Then
Exit Function
End If
RegExpTest = True
End Function

I think this is a cleaner version and will accept some off the wall emails,
but catch some glitches that my earlier code would not have.

Mike


"atr000" wrote:

>
> Mike,
>
> This code works perfectly. I can't tell you how many folks in
> research/sales will be happy when we send out emails to large numbers
> (I don't work for a spammer). I am wondering if these two things could
> be added to the working code:
>
> 1) If the entry is blank (we have many w/o emails) can it just be
> skipped?
> 2) Can there be an option to Delete, like there is, and and add in an
> option to "Go to that Cell", like an edit option?
>
> Thanks,
> Andre
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>

[crazybass2]

Andre,

Forgot to remove one of my code testing lines. Remove the line "
eaddy.Offset(0, 4) = flag" from the "Check2" procedure.

Sorry about that,
Mike

"crazybass2" wrote:

> Andre,
>
> Had some time to readup on the RegExp object. Thank you Nick for opening my
> eyes to this object, I can see it being very usefull.
>
> I integrated Nicks code into mine so that it will work the same way. I also
> modified his pattern a bit to be a little more flexible with the email
> address matches. To get a better idea of the patterns out there for email
> visit http://regexlib.com/ and type "email" in the Keyword search.
>
> As Nick noted, to utilize this code you will need to include a reference to
> "Microsoft VBScript Regular Expressions 5.5" in the VB editor. To do this,
> click Tools->References. Scroll down the list and put a checkmark in the box
> next to the reference name.
>
> Here's the code:
>
> Sub Check2()
> Dim MyRange As Range, eaddy As Range
> Dim flag As Boolean, dltemail As Variant
> Dim updatenow As String
> Set MyRange = Selection
> For Each eaddy In MyRange
> rerun:
> flag = RegExpTest(eaddy)
> If Not flag Then
> dltemail = MsgBox("The email address " & eaddy & " is invalid. Would
> you like to modify it?", _
> vbYesNoCancel, "Invalid Email Address")
> If dltemail = vbYes Then
> updatenow = MsgBox("Would you like to update this address? No will
> clear the email address", vbYesNo, "Update Email")
> If updatenow = vbYes Then
> eaddy.Value = InputBox("Please enter a valid email address", "Update
> Email", eaddy.Value)
> GoTo rerun
> End If
> eaddy.ClearContents
> End If
> If dltemail = vbCancel Then Exit Sub
> eaddy.Offset(0, 4) = flag
> End If
> Next eaddy
> End Sub
> Function RegExpTest(sEmail)
> RegExpTest = False
> Dim regEx, retVal
> Set regEx = New RegExp
> ' Create regular expression:
> regEx.Pattern =
> "^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z]*[-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,3})$"
> ' Set pattern:
> regEx.IgnoreCase = True
> ' Set case sensitivity.
> retVal = regEx.Test(sEmail)
> ' Execute the search test.
> If Not retVal Then
> Exit Function
> End If
> RegExpTest = True
> End Function
>
> I think this is a cleaner version and will accept some off the wall emails,
> but catch some glitches that my earlier code would not have.
>
> Mike
>
>
> "atr000" wrote:
>
> >
> > Mike,
> >
> > This code works perfectly. I can't tell you how many folks in
> > research/sales will be happy when we send out emails to large numbers
> > (I don't work for a spammer). I am wondering if these two things could
> > be added to the working code:
> >
> > 1) If the entry is blank (we have many w/o emails) can it just be
> > skipped?
> > 2) Can there be an option to Delete, like there is, and and add in an
> > option to "Go to that Cell", like an edit option?
> >
> > Thanks,
> > Andre
> >
> >
> > --
> > atr000
> > ------------------------------------------------------------------------
> > atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> > View this thread: http://www.excelforum.com/showthread...hreadid=562542
> >
> >

[atr000]

Mike,

Again, thanks a lot. This code really helps the headaches I have. My background is coding perl and doing cgi before a switch to more administrative stuff. With that, I lost a lot of my ability to write code, and Microsoft's VB stuff is foreign to me.

After I shot this code around to some of the researchers, someone is now wondering if we can do this (and I plan to do some homework here and see how far I can go on my own). We have a spreadsheet that contains companies sales has relationships with. Someone wants to be in an IE window, right click on a company name (highlighted), and then search the spreadsheet. If it finds anything, do a popup window in javascript with the info. I assume a little javascript + VB would do this? Many years ago before the integration of search engines and bars, I made something like this in Javascript for IE that would search dict.org with a right-clicked word (2000-2001).

Thanks,
Andrew

[crazybass2]

Andre,

I have a short background in C++ and some Java. Most of what I have learned
in VBA has been through Excel Macro Recording and utilizing the VBA help.
The rest I've learned here in this group. I don't know that the Macro
Recording will help with the task you described, but try it out for other
worksheet manipulations.

As for your next task, I'm not familiar enough with javascript, or the web
based spreadsheets, but I'm sure someone in this community is. If you can't
figure it out, I'd definately come back here.

Best of luck.
Mike

"atr000" wrote:

>
> Mike,
>
> Again, thanks a lot. This code really helps the headaches I have. My
> background is coding perl and doing cgi before a switch to more
> administrative stuff. With that, I lost a lot of my ability to write
> code, and Microsoft's VB stuff is foreign to me.
>
> After I shot this code around to some of the researchers, someone is
> now wondering if we can do this (and I plan to do some homework here
> and see how far I can go on my own). We have a spreadsheet that
> contains companies sales has relationships with. Someone wants to be in
> an IE window, right click on a company name (highlighted), and then
> search the spreadsheet. If it finds anything, do a popup window in
> javascript with the info. I assume a little javascript + VB would do
> this? Many years ago before the integration of search engines and bars,
> I made something like this in Javascript for IE that would search
> dict.org with a right-clicked word (2000-2001).
>
> Thanks,
> Andrew
>
>
> --
> atr000
> ------------------------------------------------------------------------
> atr000's Profile: http://www.excelforum.com/member.php...o&userid=36498
> View this thread: http://www.excelforum.com/showthread...hreadid=562542
>
>