Excel - Email File Path

[JohnOB]

Hi

I have been struggling trying to figure out how to do this and wonder if someone could help.

I would like to have a button in an excel spreadsheet that when pressed would launch a new email, that had a subject and the body of the email contained the file path of the spreadsheet, and it was a link that they could click.

Basically it is like a check list that various people fill out, and I want someone to be able to press a button to send an email to someone which says something like "You are required to complete the relevant section of the below spreadsheet, please click the file location below to open the file".

I have managed in the past to have a button which attaches the file to the email, but cant work out how to get the file path in (and ensure it is in a format which makes it a clickable link).

Any advice appreciated

Thanks

Thanks

"Stupid isn't not knowing the answer, it's not asking the question

 

[SkipVought]

Hi,

but cant work out how to get the file path in (and ensure it is in a format which makes it a clickable link).

You want to be CERTAIN that the path is not related to YOUR DRIVE mapping. Rather it references a network share.

Dim MessageBody as String, a, i as integer

a = split(ThisWorkbook.Path, "\")

MessageBody = "Your message " & vblf & "\\NetShr\"

for i = 1 to ubound(a)
  MessageBody =MessageBody & a(i) & "\"
next

MessageBody =Left(MessageBody, Len(MessageBody)-1)

ought to work just fine.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]