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:

Thank you!



"George.Ananya" wrote:

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","Clickt osendemail")

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 -

Best Regards
George Kottackakathu
India
+919944904304





"Mechroneal" wrote:

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

"Conan Kelly" wrote:

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:

?subject=S tatus%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)
?subject=S tatus%20Change%20" &
Data!A1 & "&body=" & C5,"Test Email")

HTH,

Conan





"boustrophedon" wrote in message
...
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. ;)