Email address field “not working” on subform

[Big Pat]

I have a main form showing details of a project, with a subform in continuous view, showing details of people who are working on it. One of the fields on that subform is PI_Email_Address.

I have set the Display As Hyperlink property to Always and I assume I now need to set the Hyperlink Address using VBA.

In the source table, the field type is text, rather than hyperlink, because the tables are not local. In fact this database uses several SharePoint lists which are linked as tables. I don’t know whether that complicates things much.

I have done something similar before in another database, although in that case the email field was on a form in single view rather than continuous view. Anyway, using that approach, I came up with the following and inserted it on the Current event of the form that’s being used as a subform

Private Sub Form_Current()
'EmailIcon is a small image of an envelope inserted on the form

If Not IsNull(Me.PI_Email_Address) Then
   Me.EmailIcon.HyperlinkAddress = "MailTo:" & Me. PI_Email_Address  
   Me.EmailIcon.Visible = True
Else
   Me.EmailIcon.Visible = False
End If
End Sub

However, there are four records on my first subform of which only one has a completed email address. But the EmailIcon is visible on all four. And when I click it nothing happens anyway.

Would that code work if I attached it to a different event, or maybe even to the parent form? And/or do I need to add some code to the click event of the EmailIcon itself?

Ideally, I would like to be able to just click the email address as it’s displayed and not bother with the EmailIcon at all, but I don’t know if that’s possible.

The fact that the icon is visible on all four records leads me to suspect this is caused by having the form in continuous view. But I’d really rather not have to change that!

Thanks,

Pat.

 

[pbaldy]

You should be able to use it directly, using the click or double click event of the textbox. You can use directly with many email methods, or use FollowHyperlink and add on the MailTo bit.

 

[Big Pat]

Hi,

Thanks a lot for this. It was the Application.FollowHyperlink I was missing and you're right I can do this right from the text box. For the benefit of anyone else who happens across this thread, the code that works is

Private Sub PI_Email_Address_Click()
Dim EmailLink as String
EmailLink = "mailto:" & Me.PI_Email_Address
Application.FollowHyperlink EmailLink
End Sub

I was overcomplicating as usual. It really is that easy.

I have tested this only with Microsoft Outlook, which is good enough for me, so can't verify whether it works with other email systems.

Thanks again!

Pat.

 

[pbaldy]

Happy to help!