Update filepath in a cell with VBA

Hi,

I have a 4 cells which contain the followng information, To: Email address, Subject: Text, Body: Text, File Path: File path (as Hyperlink).

I need the file path cell to update automatically when the file is saved somewhere new. This filepath is then used in an email that is generated with code so that the person opening the email can click on the link and open the file.

What I cannot seem to do is to get the file path to be something that updates automatically (which is functionality that I must have)but that also remains as a hyperlink for when it goes in the email (I have an additional problem at the moment which is that the hyperlink does not seem to work in the email - it keeps saying file cannot be found. I don't know if this is something to do with our systems and the way I am referring to the file?!?)

The cell with the file path currently loks like this:-

Cell AX Cell AY
File path file:///E:\PART_TIME_LEAVE\PART_TIME_PS_LEAVE_RECORD_EMAIL_VERSION_STATUTORY.xlsm

The code that generates the email looks like this:-

Please Login or Register  to view this content.

Any help greatly appreciated!

Many thanks
D

Hi,

It is in cell AY6.

Thanks
D

When I click on the link in the email it still says cannot find file

No it is not open

It is on a local network drive and yes I have access.

That works perfectly!! Thank you sooooooo much!