Hyperlink Email Field Error

I am having an issue with an email field set as a hyperlink in the properties
If I click on the field, I can email directly from the field,but my Cmd button to email the contact in the top of the form, pastes two email addresses. when I looked in the table, there is a long string like the following:
 Cheryle@......com#mailto:Cheryle@......com# (example address)
Needless to say outlook won't email this address.
I have the email field set to hyperlink, so that I can click on the field to email, but would also like to email from link on form as well.
Is there a way to fix the code to work, or am I stuck with one option to email, either from the cmd button (and cleaning up the hyperlink stuff in the table) or the clicking on the field.
Below is a screen shot of macro
 email issue
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simon BallChief information OfficerCommented:
you need to feed the shortened email address by stripping the character before the #( which is the start of the hyperlink "code"

I would declare a string variable and pop the email address field into it, taking Left([employeeemailaddress] up to the  # character
dim EmpEmail as string
EmpEmail = Left(employeeemailaddress, InStr(employeeemailaddress, "#") - 1)

Open in new window

Then use EmpEmail in the code.
Simon BallChief information OfficerCommented:
aaah, just realised my error.  conver the macro into VB code.... and run that from the button...

you can click on a macro and save as a module to get it translated into VBA by access, then use that as the onclick event of the command button.
Simon BallChief information OfficerCommented:
or, you could have a field on the form called empEmail, which is hidden, and who's value is as above... then reference that field in the macro.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The correct way to work with the Hyperlink data type and parse out the parts is to use the HyperlinkPart method.

See:  HyperlinkPart Method and HyperlinkPart Method

CherylecAuthor Commented:
When I try and convert the macros to VB, I get an error"there was a problem converting the macros, do you want to continue, I click yes but it just does the same thing and doesn't convert anything
any suggestions?
I have the form open in design view and click on the convert to vb icon int he design ribbon

TheHiTehcoach: I will research your papers as well, I would like both functions to work
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:

<<When I try and convert the macros to VB, I get an error"there was a problem converting the macros, do you want to continue, I click yes but it just does the same thing and doesn't convert anything
any suggestions?>>

Well, that caused it is a bug in Access 2010. I believe it was fixed with the SP! update. But the SP1 update has other issues.

There is not problem doing what you what, at least with VBA code. I rarely use Macros. The Macros design has been greatly improved in Access 2010 for amking Web Databases.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:

Here is the VBA code to use in a commnd button's On CLick Event

Private Sub Command234_Click()

' the following line prints to the Immediate Window (ctrl-g) 
     Debug.Print HyperlinkPart(Me.txtEmailAsHyperLink, acAddress)

' Create an Email
     Application.FollowHyperlink HyperlinkPart(Me.txtEmailAsHyperLink, acAddress)

End Sub

Open in new window

*** Change Me.txtEmailAsHyperLink to match for control's name

FWIW: I find it best to not use the hyperlink data type for email address.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CherylecAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.