Email hyperlink of this Workbook

[newboy18]

Please help, I am trying to create a button in my
spreadsheet that will send an email with a hyperlink to
this Workbook, no matter what it is called or where it is.

So far I have:
A button to VBA that gets the Workbooks full name and
saves it in Cell C6
Sheets("Sheet1").Range("C5") = ActiveWorkbook.FullName

Cells C6 Concatenates Cells C1 to C5 to give a mailto:
mailto:Doe, John;?cc=Smith,
John;&subject=Test;&body=C:\Email.xls

And a Followhyperlink command to kick off the email.
ActiveWorkbook.FollowHyperlink Address:=Sheets
("Sheet1").Range("C6")

This nearly works except the filename in the body of the
email is not a hyperlink, how do I convert the text to a
hyperlink?

[Dick Kusleika]

newboy

I don't think that's possible. At least I haven't been able to find anyone
whose done it. If you are using outlook, you could automate it and use the
HTMLBody property to put a hyperlink in the body. Post back if you need
details on that.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"newboy18" <newb...@msn.com> wrote in message
news:050a01c3194e$198eac70$a001...@phx.gbl...

[Dave Peterson]

I used Outlook Express and this almost worked for me:

Option Explicit
Sub testme()

Dim myString As String
Dim myFilename As String
myFilename = Application.Substitute(ThisWorkbook.FullName, " ", "%20")
myFilename = Application.Substitute(ThisWorkbook.FullName, "\", "/")

myString = "mailto:Doe,John?cc=Smith,John&subject=Test&body=file:////" & _
myFilename

ThisWorkbook.FollowHyperlink myString

End Sub

But if the path/filename had spaces, then the "almost" hyperlink was:

file:////C:/My Documents/excel/this is book 1.xls
(the hyperlink stopped at the first space. The %20 replacement didn't help.)

But if the path/filename had no spaces, it worked ok. (But it pointed at my C:
drive. You must be doing this on a network???)

It looks like Netscape Messenger 4.78 doesn't like spaces, too.

Maybe there's a way around this problem, but I didn't see it.

--

Dave Peterson
ec3...@msn.com

[newboy18]

Thanks Dave,
I found that if I put &body=<file://",myFilename,">"
at the end of my concatenation it works, the < and > make
it work.
Only problem now is that the emails body is stuck in
Paragraph mode instead of Normal, this means that I get
extra linefeeds in the main body.
Any ideas?

>.
>

[newboy18]

I quick update, I only have a problem when Outlook is set
to HTML, both rich text and text work OK.
Looks like the problem is how do I get some HTML code into
the body of the email.

[Dave Peterson]

If you're using outlook, you may want to visit Dick Kusleika's site. He has
lots of sample code there.

http://www.dicks-clicks.com

--

Dave Peterson
ec3...@msn.com