Access text box - would like to clean up entered email addresses with VB

[boomerang]

Hi,
I've got a text box in an Access Db that the user must enter a valid email address into. (This is done by copying from Outlook 2010). As a result, sometimes copying from Outlook brings the contact's name and angular brackets. Like so: Fred Bloggs <fred@bloggs.co.uk[/EMAIL"]fred@bloggs.co.uk"]fred@bloggs.co.uk[/EMAIL]>. Sometimes, however, if the contact is just an email address in Outlook, that's all that gets copied. A regular, vanilla email address.
So there's two things I'm trying to do:
If what's been pasted in from Outlook includes a name and angular brackets, remove 'em.
If what's been pasted in isn't a valid email address, complain. What's entered might be nothing, or it might be "bob@bob." (lacks TLD)

I was hoping to use a regex

.pattern = "[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}"

to identify the email address
but I don't really much idea how to go about writing such a relatively complicated routine for my level of experience.

I've been advised that if the email were always enclosed in the final <> I could:

 
Public Function fmt(email As String) As String
pos = InStrRev(email, "<")
If (pos > 0) Then
   email = Mid$(email, 1 + pos, 1 + Len(email) - pos)
   email = Left$(email, Len(email) - 1)
End If
fmt = email
End Function

or

replace(mid(email,instrrev(email,"<")+1,len(email)),">","")

But this isn't the case.
I was also told that for a regexp check I should add a reference to the "Microsoft VBScript Regular Expressions library" (tools > references). I have done this.

Public Function fmt(email As String) As String
pos = InStrRev(email, "<")
If (pos > 0) Then
email = Mid$(email, 1 + pos, 1 + Len(email) - pos)
email = Left$(email, Len(email) - 1)
End If
fmt = email
With New RegExp
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "^\S+@\S+\.\S+$"
If Not .Test(fmt) Then fmt = ""
End With
End Function

This apparently returns a valid email address, or "" if its not valid.



I might need my hand holding a bit if anyone has the inclination!

[ajetrumpet]

so it looks like you added the vbscript library to your references that took care of the problem?? or you DIDN'T?? I'm confused.

by default, vba does not sport anything close to things like PHP's regex. That's complex stuff and built into the language. MS has not done that with their client side languages. No reason to, that's really why.

So is this problem solved then, or not?? What is the issue with the vbscript library you are referring to?

[boomerang]

Hi, sorry, I can see what confused you - my question was very unclear in parts. I have amended it to reflect my true and current situation.

I have not managed to come up with any workable solution to date.

I would still appreciate help.

[ajetrumpet]

i dont sir any change in the post sir. I'm sorry.

please answer my question if you would like help from me.

[boomerang]

Hi ajetrumpet

I have indeed made changes to the wording of my post to make clearer my difficulties. But I don't mind spelling them out again, you are, after all, proposing to help me for free - and I am very grateful for that.

I've got a text box in an Access Db that the user must enter a valid email address into. (This is done by copying from Outlook 2010). As a result, sometimes copying from Outlook brings the contact's name and angular brackets. Like so: Fred Bloggs <fred@bloggs.co.uk[/EMAIL"]fred@bloggs.co.uk"]fred@bloggs.co.uk[/EMAIL]>. Sometimes, however, if the contact is just an email address in Outlook, that's all that gets copied. A regular, vanilla email address.
So there's two things I'm trying to do:
If what's been pasted in from Outlook includes a name and angular brackets, remove 'em.
If what's been pasted in isn't a valid email address, complain. What's entered might be nothing, or it might be "bob@bob." (lacks TLD)

I don't know how to do this. None of the code I've posted is a help or solution to me on its own.

[ajetrumpet]

I've got a text box in an Access Db that the user must enter a valid email address into. (This is done by copying from Outlook 2010). As a result, sometimes copying from Outlook brings the contact's name and angular brackets. Like so: Fred Bloggs <fred@bloggs.co.uk[/EMAIL"]fred@bloggs.co.uk"]fred@bloggs.co.uk[/EMAIL]>. Sometimes, however, if the contact is just an email address in Outlook, that's all that gets copied. A regular, vanilla email address.
So there's two things I'm trying to do:
If what's been pasted in from Outlook includes a name and angular brackets, remove 'em.
If what's been pasted in isn't a valid email address, complain.

alright then. I get it. FINALLY!

even if VB did have a regex function, you don't need it. this is not tough. All you need to do is use some and/or all of these functions:

INSTR()
LEFT()
RIGHT()
MID()
LEN()
TRIM()

check those out in access's vba help files. they are all simple to comprehend. Outlook only has two possibilities in a FROM line of an email object, in terms of format for the email address:

• <address name> email address here
• email address

the first one appears if the contact is already in the address book, RIGHT?? I believe that's correct. so the first option is all you need to fix. the second can be copied right in.

so...to cut off the bracketed address "contact name" in the first scenario, all you need to use is RIGHT, LEN, TRIM and MID. or a combination of them. look them up and see what they do. I'm sure it'll be easy to figure out.

Since you speak of regex, I assume you have exp. with server side languages?? VBA is water off a ducks back compared to the complexities of the server side string manipulation functions. You should be fine.

[boomerang]

even if VB did have a regex function, you don't need it.

Thanks. But at the moment the DB does check if the address entered is valid. As I said in my post, I want it to complain if the address entered isn't in the correct format. e.g. someone may type an address manually or not enter one at all.

I assume you have exp. with server side languages??

No, not really. I know what these languages are, but I don't necessarily know how to code with them. I just bodge it.

I'm not sure if I'll be able to come up with a workable solution, but I'll try...

[ajetrumpet]

As I said in my post, I want it to complain if the address entered isn't in the correct format. e.g. someone may type an address manually or not enter one at all.

OK, so you want to complain. that doesn't tell me anything buddy boy.

but try a message box on the LOST FOCUS event of the box maybe?? that's how you force people to enter data. one of the ways anyway. there's hundreds, but unless you know Access well you might as well take the first piece of advice you can get. as I said, a message box will be just fine. it would end up looking something like this:

private sub textbox_lostFocus(

if isnull(me.textbox) or myCheckFunction(me.textbox) = ''NOT VALID'' then
   msgbox "sorry dude, you need to enter a valid email address!"
      me.textbox.setfocus
end if

end sub

it's really that simple.

the other way would obviously be to set a complex MASK in the table's field at the table level. check out the help file in VBA to get the various syntax characters that are required for custom data masks.

[boomerang]

Thanks, buddy boy!

I'll look at this on Monday.

[ajetrumpet]

Thanks, buddy boy!

calling ME "buddy boy" won't get you anywhere. I'm the one with the expertise here my friend.

but in this case, I'll make an exception I suppose. Good luck with it.

[boomerang]

calling ME "buddy boy" won't get you anywhere. I'm the one with the expertise here my friend.

I can't work out if you're joking or not...I'll presume you are. Thanks for your help.

[ajetrumpet]

The smile gives it away, sir.

But seriously, if I WAS serious, I'd have to do the following before I could find you and beat you up:

• hack this forum and get your IP from the logs
• track the IP by narrowing down your location first with WHOIS
• check the government databases for more activity at the same IP, although that doesn't usually work I would guess
• hope that you're not masking your IP with something like TOR

and then some. So, hmmmm....even if I was mad at you, I have better things to do with my day than hunt you down.

And again, good luck to ya. Hope it works out.