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:-

Private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA" (ByVal nBufferLength As Long, ByVal _
lpBuffer As String) As Long
Const MAX_PATH = 260

Public Sub CreateMail()

Dim strFileName As String
Dim strFile As String
Dim objOutlook As Object
Dim objMail As Object

Randomize
     
strFile = GetTmpPath & "Attachment " & Right(Rnd(), 5) & ".pdf"
strFileName = CreatePDF(ActiveSheet, strFile)
 
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
 
With objMail
       .To = Range("AY3").Value
       .Subject = Range("AY4").Value
       .Body = Range("AY5").Value & vbNewLine & Range("AY6").Value
       .Attachments.Add (strFile)
       .Display
End With
 
Set objOutlook = Nothing
Set objMail = Nothing
  
End Sub

Function GetTmpPath()
    Dim sFolder As String
    Dim lRet As Long
    sFolder = String(MAX_PATH, 0)
    lRet = GetTempPath(MAX_PATH, sFolder)
    If lRet <> 0 Then
        GetTmpPath = Left(sFolder, InStr(sFolder, _
        Chr(0)) - 1)
    Else
        GetTmpPath = vbNullString
    End If
End Function
     
Function CreatePDF(wb As Object, strFile As String) As String

On Error Resume Next
wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile
On Error GoTo 0
If Dir(strFile) <> "" Then CreatePDF = strFile

End Function

Any help greatly appreciated!

Many thanks
D