Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending hyperlink via e-mail (Excel '97)

    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())

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sending hyperlink via e-mail (Excel '97)

    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

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending hyperlink via e-mail (Excel '97)

    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.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sending hyperlink via e-mail (Excel '97)

    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. <pre>Sub SendFileLink()
    Dim strLink As String
    Dim objOL As New Outlook.Application
    Dim objMail As MailItem
    <font color=red>Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Set objFSO = New Scripting.FileSystemObject
    Set objFile = objFSO.GetFile(ActiveWorkbook.FullName)</font color=red>
    <font color=blue>strLink = "file://" & objFile.ShortPath</font color=blue>
    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
    <font color=red>Set objFile = Nothing
    Set objFSO = Nothing</font color=red>
    End Sub</pre>

    You can try first by remarking out the red lines and changing the line in blue to <pre> strLink = "file://" & ActiveWorkbook.FullName</pre>

    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

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Darkest Kent, UK., Kent, England
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending hyperlink via e-mail (Excel '97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •