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:



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

Excel VBA to email a hyperlink from a textbox
 

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" wrote
in message ...

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

Excel VBA to email a hyperlink from a textbox
 

Sorry, I meant

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

--

HTH

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


"doodlebug" wrote
in message ...

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