email hyperlink - cell value in subject line

[boustrophedon]

I want to make an email hyperlink that includes text and a cell value in the
subject line, like this: Status Change [Data!A1] .

Is there an easy way to do this or do I have to VB it?

 

[Conan Kelly]

boustrophedon,

Use the HYPERLINK() function in your formula:

=HYPERLINK("mailto:[email protected]?subject=Status Change " &
Data!A1,"Test Email")

You can also concatenate to create the email address if the info is in cells
on the worksheet.

HTH,

Conan

 

[boustrophedon]

Presto, it works! Thank you, Conan!

I had already tried the ampersand and cell id but apparently didn't
configure it correctly. Excel's help for HYPERLINK should include this info,
because I can't be the only one who wants to do this.

 

[boustrophedon]

Conan (or someone else), let me push this one step further. Can I include
some "canned" text for the body of the email in that same HYPERLINK command?

I know, I know ... feature creep.

 

[Conan Kelly]

boustrophedon,

Sure it is possible.

If you noticed in the "mailto:" link, the first part was the email address,
then a question mark, then "subject=text to be displayed in subject line".

Then to add body text, add an ampersand (&) and "body=canned text for body".

It may or may not be necessary to replace spaces with "%20" (only the spaces
that are between quotes...any that are outside of quotes can remain spaces).
I'm not sure. Mess with it to see what will work.

Using the formula I originally posted:

=HYPERLINK("mailto:[email protected]?subject=Status%20Change%20" &
Data!A1 & "&body=Canned%20text%20for%20body","Test Email")

If you want, you can put cell references in your formula to insert the body
text, the "Friendly_name", etc...

(body text is in cell C5 of the same sheet the formula is being used on)
=HYPERLINK("mailto:[email protected]?subject=Status%20Change%20" &
Data!A1 & "&body=" & C5,"Test Email")

HTH,

Conan

 

[Mechroneal]

How would I use this and reference an email address stored on the sheet?

 

[George.Ananya]

Dear Mechroneal,

To automatically generate emails with 'to' field and 'subject' field filled
in the compose window of email clients like Outlook express or Lotus notes
with details/contents from the cells of an excel file (referencing) using the
'mailto:' utility then use the function 'HYPERLINK'

Generate an excel file with email IDs in one column

Case 1 referencing 'email' and 'subject' matter from cells

if email and subject need to be referenced from a cell then type the
following HYPERLINK formula to any cell

=HYPERLINK("mailto:" & A440 &"?subject=" &B1, "Click to send email")

The cell where you have typed the above formula will sport the text "click
to send email" (or what ever you specify there) which would be underlined
indicating its a clickable hyperlink

A440 - The cell value between the 2 consecutive Ampersand's "&---&" after
'mailto:' will refer to the email address in that particular cell ( here
A440) that would be pasted automatically in the 'to' field of the compose
window of your email client (outlook express, lotus notes) when you click the
cell with the above formula.

(the space between the cell value and &'s dosen't matter)

&B1 - The cell value following the '&' after the ?subject= in the formula
refer to the subject matter (text) in that particular cell (here B1) that
would be pasted automatically in the 'subject' field of the compose window of
your email client (outlook express, lotus notes) when you click the cell with
the above formula.

To effortlessly apply the formula to all email ids entered consecutively in
one column put the formula in the adjacent column in the adjacent cell to
the first email id entry.

The idea is that the formula should be entered into a cell in line with the
1st email entry so that by entering 2 formulas consequtively in a column
applied to consecutive email entries can generate the formulas effortlessly
for the rest as explained below.

Put in the formula the cell references (cell values) for first email ID
entry and put the appropriate reference (cell value) of the cell where
subject matter resides, apply the formula to the next cell below it and in
the formula replace with the cell value of the 2nd email Id and subject
matter as before.Select the two cells sporting the formula entered and pull
on the '+'mark at the bottom right of the selection and the formula gets
updated with cell values of the consecutive cell values in the email Id
column.

Pull untill the last entry of the email Id column to get the clickable links
for the email Id column in the adjacent column.

The above is useful when you need to change the Subject. Just change the
cell contents where you have typed the subject message and it would be
applicable to all emails generated by clicking the formula coulmn.

Case 1 referencing 'email' from cells and subject matter being specified in
the formula

if email need to be referenced from a cell and the subject need to be
specified in the formula then type the following HYPERLINK formula to the cell

=HYPERLINK("mailto:"&A441&"?subject=hello","Clicktosendemail")

and follow the rest as above.

Hope this is clarified

If you need mail merging solutions for outlook express please feel free to
write to me - (e-mail address removed)

Best Regards
George Kottackakathu
India
+919944904304

 

[Shira]

If I could take this question one step further - I have a spread that has one
column listing "lastname, firstname" in each cell. I'd like to convert those
to another column and create email addresses for them.

I've already learned to split the cells so that I can separate the data from
one column into two (from last,first, to last in one cell and first in
another) but I'm curious to find out if there is a formula that will do this
all at once with a designated company email address.

Example: (e-mail address removed)

Thank you!

 

[DREW@SRP]

I am trying to insert a hyperlink that when clicked upon the current excel
spreadsheet is sent via e-mail...how can i do this?? i know how to make the
hyperlink go to e-mail but it is not containing the information on the
spreadsheet?? can you please help?

 

[igbert]

Hi,

Is it possible to include the contents of multiple cells as message with
single line spacing in between?

i.e.

Account No. 12345678

Firm Name ABC Ltd

Amount $1,999.00



Igbert

 

[Ron de Bruin]

If you use Outlook code this is possible

See
http://www.rondebruin.nl/mail/tips2.htm

Hi,

Is it possible to include the contents of multiple cells as message with
single line spacing in between?

i.e.

Account No. 12345678

Firm Name ABC Ltd

Amount $1,999.00



Igbert



__________ Information from ESET Smart Security, version of virus
signature database 4282 (20090727) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4282 (20090727) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[igbert]

Hi Ron,

Thanks for the response. Our compnay only use Lotus Notes.


Igbert

 

[Ron de Bruin]

See
http://www.excelkb.com/?cNode=1X5M7A

Hi Ron,

Thanks for the response. Our compnay only use Lotus Notes.


Igbert



__________ Information from ESET Smart Security, version of virus
signature database 4284 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4284 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[singh.madhulica]

I want to make an email hyperlink that includes text and a cell value in the
subject line, like this: Status Change [Data!A1] .

Is there an easy way to do this or do I have to VB it?

Thanks the HYPERLINK thingie worked