?
Solved

Mailto hyperlinks in forms

Posted on 2003-02-20
11
Medium Priority
?
763 Views
Last Modified: 2008-02-01
Hi

I have an e-mail field in a form. The e-mails are stored as hyperlink. When I click the e-mail Outlook should start automatically.

Because access assumes that all hyperlinks start with http:// I need to find a way to transform the http:// to mailto:

On http://www.donkarl.com/ I found the following code:

Private Sub EMail_AfterUpdate()
If InStr(Me!EMail, "@") > 0 Then
    If InStr(Me!EMail, "#http") > 0 Then
        Me!EMail = "#mailto:" & Mid(Me!EMail, 1, InStr(Me!EMail, "#http") - 1) & "#"
    End If
End If
End Sub

For some reason this doesn't work for me ! When I click an e-mail my browser is started not Outlook.

I don't think the code itself is the problem. The problem in my opinion is that the code is not executed when I click the e-mail.

What leads me to that conclusion is the fact that I put some syntax error in the code on purpose and didn't get an error message when I clicked the e-mail.

I have been trying to solve this problem for several hours now so I would really appreciate some help :)

Magbert
0
Comment
Question by:Magbert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 2

Expert Comment

by:NPluis
ID: 7985981
The code is executed when you change/add the field. If you want it to execute when you click on it you should place it in the Private Sub EMail_Click() event

0
 

Author Comment

by:Magbert
ID: 7986340
When I place the code on the click event I get the following error message: "Compile error. Can't find project or library"

Private Sub EMail_Click() is highlighted in yellow, Mid in blue.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 7986512
If I can make a suggestion - don't use hyperlink fields.

You can use text fields instead and do something like:
DoCmd.SendObject acSendNoObject,,acFormatTXT,Me!txtEmailAddress

in the OnDoubleClick event of the text box, or even provide a seperate "Send Email" button. I suspect the code you've got will cause both your default mail program and your browser to open if you click on it. Also, it permanently changes the data in your field, which you may not want.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Magbert
ID: 7986660
This is the solution I had before. But the DoCmd.SendObject does not seems to work on all computers. On Computers with WinNT4 SP5 Outlook crashes when I use the DoCmd.SendObject.

So I updated all e-mails with mailto:. Now everything works fine on all computers I tested the database on.

It's just not very nice to have a mailto: in front of the e-mail addresses. That's why I wanted to use the code mentioned above.
0
 
LVL 2

Expert Comment

by:NPluis
ID: 7986703
I assumed Email was the control name on which you click. Go to properties of the textbox. tab 'Events' onclickevent select [Event Procedure] and then ... Place this code between 'Private sub ......' and 'End Sub'
If InStr(Me!EMail, "@") > 0 Then
   If InStr(Me!EMail, "#http") > 0 Then
       Me!EMail = "#mailto:" & Mid(Me!EMail, 1, InStr(Me!EMail, "#http") - 1) & "#"
   End If
End If
0
 

Author Comment

by:Magbert
ID: 7986758
This is is exactly where I put the code. I still get the error message I mentioned above. Seems to be a problem with the Mid function.

Could it be that there is a problem with the format of my e-mail field ? When I try to select a format in properties the format list is empty.
0
 
LVL 2

Accepted Solution

by:
NPluis earned 800 total points
ID: 7986944
Check your references and see if it's missing one.
0
 

Author Comment

by:Magbert
ID: 7987096
Yes, that was the problem. The code works now ! But I begin to suspect that shanesuebsahakarn was right (as always :)). The data in my field is changed permanently.

I guess I will have to live with entering a mailto: in front of every e-mail address. At least I managed to automate this. When I click the field a mailto: is automatically inserted. So I just have to type the actual e-mail address.

Of course the best solution would be to have a code like the one above that doesn't change the field entry (or only changes it temporarily). Perhaps I will try to figure out a solution when I have more time.

Thank you all for your help
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 7987137
Magbert,

Try something like:
Dim hlink As String

hlink = Me!EMail
If InStr(hlink, "@") > 0 Then
  If InStr(hlink, "#http") > 0 Then
      hlink = "#mailto:" & Mid(hlink, 1, InStr(hlink, "#http") - 1) & "#"
  End If
End If
FollowHyperlink hlink
0
 

Author Comment

by:Magbert
ID: 7987464
I get the idea

I gave it a quick try and found out the following:

1. If you click an empty field you get the error message: "Invalid use of Null"

2. If you click a e-mail address you get the error message Can't follow hyperlink to Magbert hotmail.com# (@is missing). If you click on End the browser is openend.

Have a nice evening

Magbert
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 7988622
I'll take a look - just thought it might help. The Instr is getting the wrong part of the hyperlink somewhere.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question