Hyperlink field in the message of email in access form

Sivasan
Sivasan used Ask the Experts™
on
Hi There,
I have a button in a access form and on click event I send email to the receipient.
On the message i have a field which is a hyperlink field in my access table.
The email is sent without any issue, but the hyperlink field just display the path. Is there a way i can make it real hyperlink on the email the user receives so they can just click on it?

please see code below

I have two fields Email1 and email2, i use sendobject to send email. Lk is my hyperlink field, i send the value of this in email. The user receives this on their email as a path d:\test2\log.maf
I want them to be able to just click on the link and go to the form. It shows the path but unable to click on it. Please see code below.
Will appreciate if somebody can show me the syntex to send hyperlink field on send object.
thx

Private Sub Emailbtn_Click()
Dim emailse As String
    If IsNull(Me.Email1) Or Me.Email1 = "" Then
  MsgBox "Please Enter alteast one email to send email.  !", vbOKOnly, "No emails will be sent."
       ' Me.Email1.SetFocus


Else
    If IsNull(Me.Email2) Or Me.Email2 = "" Then

emailse = [Email1]


Else

emailse = [Email1] & ";" & [Email2]


End If


DoCmd.SendObject _
    , _
    , _
    , _
     "" & emailse, _
    , _
    , _
    "Your action needed", _
    "Please check this link  " & " " & Me.Lk.Value, _
    False

End sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you saying you want to give any email addressee you contact direct access to a form contained in the file you describe as d:\test2\log.maf on your local machine by just clicking on the hyperlink address?  Have you checked with your network supervisor about this.  It would seem to me to violate standard network security protocols as a start.

Author

Commented:
Well the location of the file will be on a shared network drive and only users who have access to that drive will only be using the form.
I just gave an example about the location of the file.
Also on this form, the email address that would be given will be only internal who would have access to that particular drive.
Please let me know how I can include hyperlink field on the message.
thanks

Commented:
the hyperlink field has to be prepended with - http://servername/sharename 

check with you network administrator what values to use for the server name and sharename.
Ensure you’re charging the right price for your IT

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

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Two issues here:

1. This is an issue with using Hyperlink Datatypes as Fields.
Hyperlinks in Access display the link, but it stores the "Full Path"

For ex: if you type: www.amazon.com
...in a Hyperlink field, it will appear as such (as a clickable Hyperlink)

However, Access stores this internally as: www.amazon.com#http://www.amazon.com#
So you need to isolate the text between the Pound symbols.

2. "I want them to be able to just click on the link and go to the form."
This is not possible using SendObject.

You must use code like this, but substitute: .HTMLBody  for .Body
http://support.microsoft.com/kb/161088
You obviously must still create a function to isolate the "Real" hyperlink (http:/www.amazon.com) and the text you want to display, (http:/www.amazon.com)
Finally, you must format this as a standard HTML Hyperlink, in order for it to be "clickable".
In other words, the .HTMLBody text will need to look something like this:
   .HTMLBody= <a href="http:/www.amazon.com">http://www.amazon.com</a>

JeffCoachman



Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sorry, correction:
This sentence:
"You obviously must still create a function to isolate the "Real" hyperlink (http:/www.amazon.com) and the text you want to display, (http:/www.amazon.com)"
...should read:
"You obviously must still create a function to isolate the "Real" hyperlink (http://www.amazon.com) and the text you want to display, (www.amazon.com)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
and fix this as well:

In other words, the .HTMLBody text will need to look something like this:
   .HTMLBody= <a href="http:/www.amazon.com">http://www.amazon.com</a>

...should read:
In other words, the .HTMLBody text will need to look something like this:
   .HTMLBody= <a href="http://www.amazon.com">http://www.amazon.com</a>

Author

Commented:
Hi GralL and boag2000,
Thanks for your response.
boag2000, I tried the eample in your link but it opens outlook and expects me to put the email and more over for some reason, I get error.
The code i had using sendobject  would send the email from the access form to the recipients in Email1 and email2 field.

I did add a module per your example to my access as seen below.
----------

Option Explicit

Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment

         
      End Sub

----------------------

what would be the code for on click of the email button?

Private Sub Emailbtn_Click()


End sub
--------
Right now per your link i did the on click event code like this but doesn't seem to work as I like. Please review code and let me know how I do this without outlook opening.

-----
Private Sub Emailbtn_Click()
Dim emailse As String
    If IsNull(Me.Email1) Or Me.Email1 = "" Then
  MsgBox "Please Enter alteast one email to send email.  !", vbOKOnly, "No emails will be sent."
       ' Me.Email1.SetFocus


Else
    If IsNull(Me.Email2) Or Me.Email2 = "" Then

emailse = [Email1]


Else

emailse = [Email1] & ";" & [Email2]


End If




' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add(& emailse)  ' trying to add the emails entered
              objOutlookRecip.Type = olTo

             

             ' Set the Subject, Body, and Importance of the message.
             .Subject = "Action needs attention"
              .HTMLBody = " <a href= d:\Q\Log form .maf> Click to Access this</a> "

             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
           
             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

            ' Should we display the message before sending?
             If DisplayMsg Then
                .Display
            Else
                  .Send
            End If
           
          End With
          Set objOutlook = Nothing



End sub
-------------------------



MIS Liason
Most Valuable Expert 2012
Commented:
Remember the sample codes I post are examples, they will need to be adapted to work in your specific environment.
For example, here is a sample that (as best as I can tell) simulates your design.
It works fine for me.
You will have to study it carefully and adapt it to work in your database.

JeffCoachman
Access-EEQ26606114HyperlinkInEma.mdb

Author

Commented:
Hi boag
Yes it work the only thing is instead of a web url my link would be form on a local shared directory L:\Test\Inc\log.maf so how would I give this in .htmlbody ?

I tried this
.HTMLBody = "<a href=" & "L:\Test\Inc\log.maf" & ">" & "Click here to access" & "</a>"
 but says can cannot find L:\Test  make sure path is correct
how do  i define the path here?

Also is it possible to diable the send dialog boxes that appear when we click on send button?
thanks
S

Author

Commented:
Was able to get it
did

.HTMLBody = "<a href=" & " ' L:\Test\Inc\log.maf ' " & ">" & "Click here to access" & "</a>"

and it worked.
Can you tell me if I can disable the to alert box trying access outlook and sending box that appear after I click send button
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
You will have to invest in a program like this:

http://www.contextmagic.com/express-clickyes/

;-)

Author

Commented:
Great thanks a million for all your help Jeff.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
... And thank you for an interesting and thought provoking question...
;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial