| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Function for Checking Email Syntax
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
Mike, This code works perfectly. I can't tell you how many folks i research/sales will be happy when we send out emails to large number (I don't work for a spammer). I am wondering if these two things coul be added to the working code: 1) If the entry is blank (we have many w/o emails) can it just b skipped? 2) Can there be an option to Delete, like there is, and and add in a option to "Go to that Cell", like an edit option? Thanks, Andr -- atr00 ----------------------------------------------------------------------- atr000's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56254 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
Mike, Again, thanks a lot. This code really helps the headaches I have. M background is coding perl and doing cgi before a switch to mor administrative stuff. With that, I lost a lot of my ability to writ code, and Microsoft's VB stuff is foreign to me. After I shot this code around to some of the researchers, someone i now wondering if we can do this (and I plan to do some homework her and see how far I can go on my own). We have a spreadsheet tha contains companies sales has relationships with. Someone wants to be i an IE window, right click on a company name (highlighted), and the search the spreadsheet. If it finds anything, do a popup window i javascript with the info. I assume a little javascript + VB would d this? Many years ago before the integration of search engines and bars I made something like this in Javascript for IE that would searc dict.org with a right-clicked word (2000-2001). Thanks, Andre -- atr00 ----------------------------------------------------------------------- atr000's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56254 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Function for Checking Email Syntax
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Call a function within Excel VBA --- need help can't figure out syntax correctly | Excel Worksheet Functions | |||
| What is the syntax for function text parameters in VBA (Excel 07) | Excel Worksheet Functions | |||
| syntax for using Excel worksheet function | Excel Programming | |||
| Email Syntax error | Excel Programming | |||
| I need the syntax for the cube root function in excel. | Excel Discussion (Misc queries) | |||
Linear Mode
