Can I create a dynamic email address in Excels hyperlink?

M

mtkatrev

I'd like to take names from a list in excel and create email addresses from
them. For example:
Associate Name:
Tom Smith
If "Tom" is in one cell (Q13) and "Smith" is in the next (R13) I can compose
an email address in cell S13 with =Q13&"."&R13&"@msn.com". I'd then like to
set up a hyperlink in cell Q13 that refers to cell S13 that contains
[email protected].
 
S

sebastienm

Hi,
You can use the HYPERLINK(url, displayed_text) formula, but it would have to
be in another column:
=HYPERLINK("mailto:" & Q13&"."&R13&"@msn.com", "mail " & Q13 & " " & R13)
Would that work for you?

Regards,
Sebastien
 
D

Dave Peterson

I think you need the mailto: portion

=HYPERLINK("mailto:"&Q13&"."&R13&"@msn.com",Q13&"."&R13&"@msn.com")
 
I

ibrettferguson

Is there a way to insert carriage returns in the HYPERLINK formula?

For instance, in the mailto, I want the body to be:
Top line
Middle line
Bottom line
 
S

sebastienm

yes, for displayed_text, enter a series of strings separated by CHAR(10) to
create a new line within the same cell. Also, and very important, format the
cell with Wrap Text (menu Format > Cell, tab Alignement):

"A" & CHAR(10) & "B"
returns:
A
B
within a single cell.

So, within the HYPERLINK formula:
=HYPERLINK("mailto:" & Q13&"."&R13&"@msn.com", "A"&CHAR(10)&"B")
would show:
A
B
highlighted as a hyperlink.

Regards,
Sebastien
 
Top