Excel VBA to email a hyperlink from a textbox

OK, here goes.......

I have an excel spreadsheet on a network, and I need to be able to email a link to the spreadsheet to everyone who needs to use it.

I have done the formula =cell("filename") to get the filepath, i have created a userform which initialises textbox1 to pick up the filename. I have written a macros to create an email, it puts in the subject and the body of the message. In the body of the message is the value of textbox1, but i cannot get it to format this filepath as a hyperlink.

Here's the code in it's entirity i have done:

[VBA]

Private Sub CommandButton1_Click()
ESubject = "A Workbook has been created which requires your input."
SendTo = ""
CCTo = ""
Msg = "Please click on this link to access the new workbook : "
msg2 = TextBox1.Value
Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.CC = CCTo
.body = Msg & vbCrLf & vbCrLf & msg2
.Display
End With
Set App = Nothing
Set Itm = Nothing
Unload Me
End Sub


Private Sub UserForm_Initialize()
TextBox1.Value = Sheets("calcs").Range("b36").Value
End Sub
[/VBA]

ANy help anyone could offer would be greatly appreicated :-)

Sorry, I meant

"file::///" & ActiveWorkbook.FullNameURLEncoded

--

HTH

RP
(remove nothere from the email address if mailing direct)


"doodlebug" <[email protected]> wrote
in message news:[email protected]...
>
> OK, here goes.......
>
> I have an excel spreadsheet on a network, and I need to be able to
> email a link to the spreadsheet to everyone who needs to use it.
>
> I have done the formula =cell("filename") to get the filepath, i have
> created a userform which initialises textbox1 to pick up the filename.
> I have written a macros to create an email, it puts in the subject and
> the body of the message. In the body of the message is the value of
> textbox1, but i cannot get it to format this filepath as a hyperlink.
>
> Here's the code in it's entirity i have done:
>
>
>
> Private Sub CommandButton1_Click()
> ESubject = "A Workbook has been created which requires your input."
>
> SendTo = ""
> CCTo = ""
> Msg = "Please click on this link to access the new workbook : "
>
> msg2 = TextBox1.Value
> Set App = CreateObject("Outlook.Application")
> Set Itm = App.CreateItem(0)
> With Itm
> Subject = ESubject
> To = SendTo
> CC = CCTo
> body = Msg & vbCrLf & vbCrLf & msg2
> Display
> End With
> Set App = Nothing
> Set Itm = Nothing
> Unload Me
> End Sub
>
>
> Private Sub UserForm_Initialize()
> TextBox1.Value = Sheets("calcs").Range("b36").Value
> End Sub
>
>
> ANy help anyone could offer would be greatly appreicated :-)
>
>
> --
> doodlebug
> ------------------------------------------------------------------------
> doodlebug's Profile:
http://www.excelforum.com/member.php...o&userid=25902
> View this thread: http://www.excelforum.com/showthread...hreadid=392819
>

If you have Excel 2002 or up, you could do away with the form and just use

"file::///" & ActiveWorkbook.FullName

--

HTH

RP
(remove nothere from the email address if mailing direct)


"doodlebug" <[email protected]> wrote
in message news:[email protected]...
>
> OK, here goes.......
>
> I have an excel spreadsheet on a network, and I need to be able to
> email a link to the spreadsheet to everyone who needs to use it.
>
> I have done the formula =cell("filename") to get the filepath, i have
> created a userform which initialises textbox1 to pick up the filename.
> I have written a macros to create an email, it puts in the subject and
> the body of the message. In the body of the message is the value of
> textbox1, but i cannot get it to format this filepath as a hyperlink.
>
> Here's the code in it's entirity i have done:
>
>
>
> Private Sub CommandButton1_Click()
> ESubject = "A Workbook has been created which requires your input."
>
> SendTo = ""
> CCTo = ""
> Msg = "Please click on this link to access the new workbook : "
>
> msg2 = TextBox1.Value
> Set App = CreateObject("Outlook.Application")
> Set Itm = App.CreateItem(0)
> With Itm
> Subject = ESubject
> To = SendTo
> CC = CCTo
> body = Msg & vbCrLf & vbCrLf & msg2
> Display
> End With
> Set App = Nothing
> Set Itm = Nothing
> Unload Me
> End Sub
>
>
> Private Sub UserForm_Initialize()
> TextBox1.Value = Sheets("calcs").Range("b36").Value
> End Sub
>
>
> ANy help anyone could offer would be greatly appreicated :-)
>
>
> --
> doodlebug
> ------------------------------------------------------------------------
> doodlebug's Profile:
http://www.excelforum.com/member.php...o&userid=25902
> View this thread: http://www.excelforum.com/showthread...hreadid=392819
>

Hi,

I have the following code to do something similar to above but I need to add the filepath as a hyperlink to the email along with the body text. I have 4 cells in the Excel workbook that contain 1) Who to sen to:, 2)The Subject:, 3) A sentence for the Body of the email and 4) The filepath of the workbook as a hyperlink.

I want to add both the Body text and the hyperlink to the email so that the person opening the email can click on the link and open the file.

Any help would be greatly appreciated!

Thanks


Public Sub CreateMail()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range


Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
Set rngTo = .Range("AY3")
Set rngSubject = .Range("AY4")
Set rngBody = .Range("AY5")
End With

With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
.Display
End With

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing


End Sub