D
doodlebug
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
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