Send mail with hyperlink path

[CVDK]

Hi all
I'm planning to send an e-mail with a hyperlink to an excel file. The link needs to be sort of dynamic in the sense that you update the excel file run the macro which creates a new folder, saves the copy of the original file in the folder and sends an e-mail with a link to the new workbook (so far I can send an e-mail with the workbook) but in order to save (a little) space we just send a link to the file.

The remaining problem is that the path contains a space which f***s up the hyperlink. How do I make the entire path a link (it works if I write the full path so it's static but not if it's dynamic, as it is below)?

Thanks in advance
Christian

Sub test1()
   Var = <a href=file://" & ActiveWorkbook.FullName & ">Click here</a>"
'   Dim myItem As Outlook.MailItem
'   Dim myOlApp As Outlook.Application
   
   Set myOlApp = CreateObject("Outlook.application")
   Set myItem = myOlApp.CreateItem(olMailItem)
   With myItem
       .To = "recipients"
       .Subject = "Report " & data
       .htmlBody = "<p>Dear Sir </p>" + Var
       .send
   End With
    
End Sub

file://"

 

[Andrew Poulsom]

This may help you:

http://www.mrexcel.com/forum/showthread.php?t=520756