Sending hyperlink via e-mail (Excel ’97)

Topic

New Reply

Can anyone tell me what needs to be done to this line in order to mail the active worksheet as a hyperlink instead of sending the whole file?

ActiveWorkbook.SendMail Recipients:=”Mr Recipient”, Subject:=(“ABC.xls @ ” & Now())

Reply | Quote

Replies

ActiveWorkbook.SendMail will send the active workbook. Could youexplain what you meany by sending it as a hyperlink ?. Do you mean sending a worksheet with a hyperlink to the active workbook ?

Andrew C

Reply | Quote

Sorry, wrong teminology. What I meant was to send a shortcut in a mail pointing the user to the original file which is saved on a network volume. I can’t run the risk of someone saving the sent workbook to their C: drive, editing it and then saving it to the network and knocking out any changes anyone else may have made.

Thanks, Andrew.

Reply | Quote

Marc,

Sorry for the delay in responding – did not catch your last response earlier.

If Outlook is you email client, the following code will include a link to the activeworkbook in the message body text. It will require that you set a reference to Outlook in your VBA references, and probably the Microsoft Scripting Runtime Library, as I have used that to get the short path/file name as the Long name does not seem to translate to a hyperlink if the pathname includes spaces.

Sub SendFileLink()
    Dim strLink As String
    Dim objOL As New Outlook.Application
    Dim objMail As MailItem
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Set objFSO = New Scripting.FileSystemObject
    Set objFile = objFSO.GetFile(ActiveWorkbook.FullName)
    strLink = "file://" & objFile.ShortPath
    Set objOL = New Outlook.Application
    Set objMail = objOL.CreateItem(olMailItem)
    With objMail
        .To = "Recipient1;Recipient2"
        .Subject = "File Link"
        .Body = strLink & vbCrLf & vbCrLf & "Message"
        .Send
    End With
    objOL.Quit
    Set objMail = Nothing
    Set objOL = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub

You can try first by remarking out the red lines and changing the line in blue to

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

as it may work on your system, especially if you do not have spaces in your path name.

Hope that is what you are looking for.

Andrew C

Reply | Quote

Andrew –

I haven’t had an opportunity to try this yet, but I’ll have a look in the next few days when my workload is under control again.

Thanks very much for your help. It is greatly appreciated.

Marc

Reply | Quote