HOW TO EMAIL HYPERLINK OF ACTIVEWORKBOOK

[K]

Hi all, I have resarched every where but i didnt found the suitable
answer. I tried looking on Ron's Web and also few other webs but i
couldnt found the answer. My problem is that i want to eamil
hyperlink (no attachement no any thing else just hyperlink of
activeworkbook) to other person. the macro works nearly 90% for me as
it puts the file path in email body but that path is just a text as i
want it to be hyperlink so people can go in that link to see the file.
Please Please can anybody help me as i am very pissed off searching
for this too long.

======================================================>

Function HypToPath(hyp As Hyperlink) As String

Dim CurrAdd As String
Dim GoBack As Long
Dim CurrFldr As String
Dim CAddStrip As String
Dim i As Long
Dim OldDir As String

CurrAdd = hyp.Address
CAddStrip = Replace(CurrAdd, "..\", "")
CurrFldr = hyp.Parent.Parent.Parent.Path
OldDir = CurDir

GoBack = (Len(CurrAdd) - Len(CAddStrip)) / 3


If GoBack > 0 Then
ChDir CurrFldr

For i = 1 To GoBack
ChDir ".."
Next i

If Not CurDir Like "?:\" Then
CAddStrip = "\" & CAddStrip
End If

HypToPath = CurDir & CAddStrip


ChDir OldDir
ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
HypToPath = CurrAdd
Else
HypToPath = CurrFldr & "\" & CurrAdd
End If


End Function

**********************************
Sub Mail_Selection_Range_Outlook_Body()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng =
ActiveSheet.Range("A13:L58").SpecialCells(xlCellTypeVisible)
'You can also use a range if you want
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is
protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Range("T7028").Value
.CC = ""
.BCC = ""
.Subject = "PLEASE AUTHORISE" & " (" & Range("A13").Value &
")"
.Body = HypToPath(Range("a12").Hyperlinks(1))
.Display 'or use .Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
Range("M4").Value = "EMAILED FOR AUTHORISATION"
End Sub

<======================================================

 

[Ron de Bruin]

Hi K

Use links in the body

.Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
.Body = "file://Yourcomputer/YourFolder/Week%202.xls"

Example for a file on a website
.Body = "http://www.rondebruin.nl/files/EasyFilter.zip"

 

[K]

Hi K

Use links in the body

    .Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
    .Body = "file://Yourcomputer/YourFolder/Week%202.xls"

Example for a file on a website
    .Body = "http://www.rondebruin.nl/files/EasyFilter.zip"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
























- Show quoted text -

Hi Ron, Thanks for replying. The sultion you told me to put the link
in Body is great but thing is if i put the folder name and file name
but if some other person save it in different name how would macro
know. the situation at the moment is like
C:\Document\unkown folder\unkown filename. The macro above works good
but it put the path in eamil body as text not as hyperlink. how can i
have hyperlink and also i want macro to conform that file has been
saved somewhere. i did email you about this problem you might see my
email which i sent to you on (e-mail address removed) . please do
reply.

 

[Ron de Bruin]

i did email you about this problem you might see my

email which i sent to you on (e-mail address removed) . please do reply.

I reply to you this evening or tomorrow (very busy)



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi K

Use links in the body

.Body = "file://Yourcomputer/YourFolder/Week2.xls"

If there are spaces use %20
.Body = "file://Yourcomputer/YourFolder/Week%202.xls"

Example for a file on a website
.Body = "http://www.rondebruin.nl/files/EasyFilter.zip"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
























- Show quoted text -

Hi Ron, Thanks for replying. The sultion you told me to put the link
in Body is great but thing is if i put the folder name and file name
but if some other person save it in different name how would macro
know. the situation at the moment is like
C:\Document\unkown folder\unkown filename. The macro above works good
but it put the path in eamil body as text not as hyperlink. how can i
have hyperlink and also i want macro to conform that file has been
saved somewhere. i did email you about this problem you might see my
email which i sent to you on (e-mail address removed) . please do
reply.