Hi,

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

Many of the emails are wrong or are incomplete and I wanted to have
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 unfamilia
with the syntax to Excel/VBA.

Thanks

--
atr00
-----------------------------------------------------------------------
atr000's Profile: http://www.excelforum.com/member.php...fo&userid=3649
View this thread: http://www.excelforum.com/showthread.php?threadid=56254

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.

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

ATR,

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

ie.
- Good
name.provider.com - Bad
- 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 ,
- the regEXP I really want to check for a valid:
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

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
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 ,
- the regEXP I really want to check for a valid:
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" wrote in
message ...

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