Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Mailto hyperlinks in forms

Posted on 2003-02-20
11
Medium Priority
?
766 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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