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.
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
>
>
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.
Originally Posted by 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
>
>
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
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
>
>
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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks