Create E-Mail with Hyperlink to File

[Barry]

I have searched for two days for a solution to this problem without success.

I am trying to automatically create an E-Mail in VBA when the value of a
hyperlink field on a form changes in MS Access. I successfully created an
E-Mail with a link but the link contains # (pound signs) as follows:
<file:\\#Y:\ADLO_Projects\RM\Access\TDA\ARL-2009-081.doc#>

The VBA code that I am using is below.

Any assistance will be greatly appreciated.

Private Sub Text199_Change()
Dim TRACKING As String
Dim EMAILADD As String
Dim DOCLINK As String

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

TRACKING = Me![ARL TRACKING NO]
EMAILADD = Me!EMAIL
DOCLINK = Me![Document Link]

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = EMAILADD
.CC = "(e-mail address removed)"
.Subject = "Service Contract ARL Tracking NO " & TRACKING
.ReadReceiptRequested = False
.Body = "<file:\\" & DOCLINK & ">"
.Display
End With
End Sub

 

[Jack Leach]

Use the Replace function to extract the pound signs if they're causing an
issue:

..Body = "<file:\\" & Replace(DOCLINK, "#", "") & ">"

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Barry]

Thanks so much. That worked like a charm!

Use the Replace function to extract the pound signs if they're causing an
issue:

.Body = "<file:\\" & Replace(DOCLINK, "#", "") & ">"

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I have searched for two days for a solution to this problem without success.

I am trying to automatically create an E-Mail in VBA when the value of a
hyperlink field on a form changes in MS Access. I successfully created an
E-Mail with a link but the link contains # (pound signs) as follows:
<file:\\#Y:\ADLO_Projects\RM\Access\TDA\ARL-2009-081.doc#>

The VBA code that I am using is below.

Any assistance will be greatly appreciated.

Private Sub Text199_Change()
Dim TRACKING As String
Dim EMAILADD As String
Dim DOCLINK As String

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

TRACKING = Me![ARL TRACKING NO]
EMAILADD = Me!EMAIL
DOCLINK = Me![Document Link]

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = EMAILADD
.CC = "(e-mail address removed)"
.Subject = "Service Contract ARL Tracking NO " & TRACKING
.ReadReceiptRequested = False
.Body = "<file:\\" & DOCLINK & ">"
.Display
End With
End Sub