email hyperlink subject

[David McRitchie]

Hi Spence,
A1: mySubject
B1: =HYPERLINK("mailto:soa...@whatever.com",A1)

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"spence" <spen...@carolina.rr.com> wrote in message news:4fea01c282e1$2baa51b0$2ae2...@phx.gbl...
> is it possible to have an email hyperlink
> (mailto:soa...@whatever.com) with the subject coming
> from a cell in the spreadsheet. i.e. cell A1 contains a
> number or whatever, can that be made to be the subject of
> a hyperlinked email???

[spence]

[David McRitchie]

Hi Spence,

A message (email) of "it did not work" does not convey
much meaning. And forces me to ask "what did it do
instead of work".

You would get faster replies and probably more accurate if
you stuck to your thread in the newsgroup, and others would
also benefit and chip in from the in the newsgroup. Though
I do try to answer email -- they sometimes get lost when I
think I might be able to answer but not right away.

You will have to post a text example of what you have in
the cell(s) and what you want to see for the link and where
the link is to go. My reply uses the A1 cell as the link on
the left side of the HYPERLINK Worksheet Function and
you supply what you want to see on the right side of the
HYPERLINK Worksheet Function. You could get both
parts from other cells or concatenations of cells and
constants. Naturally the use of a worksheet function
requires the additional cell (column) to be used.

The following works A1 and the formula in B1
mailto:dmcri...@msn.com || =HYPERLINK(A1,"David")

This is not in the form that your question was asked and
it will fail with "cannot open the specified file"
dmcri...@msn.com || =HYPERLINK(A3,"David")

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" <dmcri...@msn.com> wrote in message news:OLTGSOugCHA.2592@tkmsftngp09...

> Hi Spence,
> A1: mySubject
> B1: =HYPERLINK("mailto:soa...@whatever.com",A1)
>

[spence]

it opened the email up, but there was nothing in the
subject box. running excel XP

>.
>

[jaf]

Hi Spence,
Try this mailto:m...@here.com?subject=Test of subject line

If you open the hyperlink dialog box there is an email icon on the left
which will format the hyperlink for you.

--
John
johnf202 at hotmail.com

"spence" <spen...@carolina.rr.com> wrote in message

news:acbb01c2834c$8b129840$3bef2ecf@TKMSFTNGXA10...

[David McRitchie]

Hi Spence,
Sorry I misread question and you could not have been
more explicit that you wanted the subject filled in.

They HYPERLINK Worksheet Solution then would be
of no use to you.

You will need a macro solution see Ron de Bruin's notes at
Some Coding examples for use with Sendmail
http://www.rondebruin.nl/sendmail.htm

Since this is the misc newsgroup you may need some help
with a macro, I would suggest an Event macro
http://www.mvps.org/dmcritchie/excel/event.htm

Unlike regular macros install by RClick on worksheet
tab name, view code, and paste following code.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
ThisWorkbook.FollowHyperlink _
"mailto:dmcri...@msn.xcom?subject=" & _
Target.Value & "&body=This is a test."
End Sub

You can customize the above as much as you want. Use
Chr(10) in a concatenation for new lines within the body.

DoubleClick on the cell with Subject and the email
will be composed for you.

More information on Sending Email besides Ron's link above:

Q241498 -- XL2000: How to Use an Excel Database to Send E-Mail
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241498

Also see Sending Personalized Email from Excel, Tip 86 John Walkenbach.
http://j-walk.com/ss/excel/tips/tip86.htm

---

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"spence" <spen...@carolina.rr.com> wrote $3bef2ecf@TKMSFTNGXA10...

> it opened the email up, but there was nothing in the
> subject box. running excel XP

"spence" <spen...@carolina.rr.com> wrote ...

[David McRitchie]

In playing with this I wondered what to do with a subject
that has an ampersand in the sendto subject. The
solution is to code as %26 which is the HTML token for
hex 26 which is the ampersand.

Rather than messing up spreadsheet with
A28: Tools %26 Things

recode to get "Tools & Things" automatically with

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
ThisWorkbook.FollowHyperlink _
"mailto:dmcri...@msn.xcom?subject=" & _

Replace(Target.Value, "&", "%26") & _

"&body=This is a test."
End Sub

Unleashing the Power of mailto URLs, By Robert Husted
http://developer.netscape.com/viewsource/husted_mailto/mailto.html

HTH, mailmerg.htm is where I will have links