Email link to worksheet from public drive

[Thanek]

Hey there,

1. I have a worksheet I'm using for project management. I would prefer if there were no extra copies of the files floating about so I don't want to send attachments of the file. The excel file is stored on a public drive and I would like to include a button in excel which opens an outlook email window and includes a link directly to the file on the shared public drive.

2. My biggest problem is the public drive may have defaulted to a different letter on different terminals. Is there a way to link the path using the drives IP or something constant?

Thanks!

 

[montecarlo2079]

I use this one quite a bit


Sub Make_Outlook_Mail_With_File_Link()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
"Bryan," & _
"Here are the weekly report :" & _
ActiveWorkbook.Name & " " & _
"Click on this link to open the file : " & _
"bill.jones@123.com
.CC = ""
.BCC = ""
.Subject = "Weekly Payment Report"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Else
msgbox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub

 

[Thanek]

Well this does open an outlook email and fill in literally "ActiveWorkbook.Name" but I'm not understanding how it should point to the file path? And is this the file's absolute path or something other terminals would be able to follow back?

 

[montecarlo2079]

Sub Make_Outlook_Mail_With_File_Link()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "<font size=""3"" face=""Calibri"">" & _
"Boss,<br><br>" & _
"Here are the weekly updated Payment figures :<br><B>" & _
ActiveWorkbook.Name & "</B> <br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & _
""">Link to the file</A>" & _
"<br><br>Thanks," & _
"<br><br>JOE SCHMO</font>"

On Error Resume Next
With OutMail
.To = "EMAIL ADDRESS GOES HERE"
.CC = ""
.BCC = ""
.Subject = "Weekly Payment Report"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Else
msgbox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub


put the file path in this line "<A HREF=""file://" & ActiveWorkbook.FullName & _