This should get you real close to what you want. Put this code in the sheet
module of the sheet that you want the hyperlinks on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fileToOpen As String
Dim MyPath As String
Dim SaveDriveDir As String
If Not Application.Intersect(Range("I1:I65000"), Target) Is Nothing Then
SaveDriveDir = CurDir
MyPath = "P:\DNSDC\LSA\QUERIES\EMAILS SENT\"
ChDrive MyPath
ChDir MyPath

fileToOpen = Application _
.GetOpenFilename("Email Files (*.eml), *.eml")
If fileToOpen = "False" Then Exit Sub
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fileToOpen

ChDrive SaveDriveDir
ChDir SaveDriveDir
Else: Exit Sub
End If
End Sub

Mike F

"Chris Hankin" wrote in message
...
Hi, I'm hoping please that someone could advise on how I can do the
following:

When a use clicks in Column I of my workbook named Queries, I want Excel
to
automatically do the following:

This is what I now do manually:

Click on Insert
Click on Hyperlink
Click on Browse (the Browse button adjacent to the drop-down box named
Link
To File or URL
Select the following drive and directory: P:\DNSDC\LSA\QUERIES\EMAILS
SENT\
The tick box Use Relative Path For Hyperlink is checked

So basically when a user clicks in Column I, I want Excel to automatically
show the Insert Hyperlink Window
with the above-mention drive and directory pre-selected so that the user
only needs to select the .EML file and click on the OK button.

Any help would be greatly appreciated.

Kind regards,

Chris.