Excel Hyperlink question

[sterjohn]

I wonder if someone could please help?
I have a column of email addresses in plain text in excel 2016 that I need to convert to clickable email links?
Is there a way that I can change all the email addresses to link at the same time?
Any help would be appreciated.
Kind regards,
John.S
Australia

 

[Col Delane]

Hi John


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

A
​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

2
​

[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Formula[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

3
​

[/TD]
[TD]Email Address[/TD]
[TD]

Subject​

[/TD]
[TD]

Body​

[/TD]
[TD]

Hyperlink​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

4
​

[/TD]
[TD]general@afl.com.au[/TD]
[TD]Go Eagles[/TD]
[TD]2018 Premiers[/TD]
[TD]general@afl.com.au[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

5
​

[/TD]
[TD]abcengagement@abc.net.au[/TD]
[TD]Go Glory[/TD]
[TD]2019 Champ's[/TD]
[TD]abcengagement@abc.net.au[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

6
​

[/TD]
[TD]edletters@afr.com.au[/TD]
[TD]Who doesn't like Collingwood?[/TD]
[TD]No-one[/TD]
[TD]edletters@afr.com.au[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: .
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in E4 = HYPERLINK( "mailto:" & B4 & "?subject=" & C4 & "&body=" & D4, B4)
Note: No spaces between "&body="
Easy-peasy!

 

[sterjohn]

Thanks verymuch for your advice on this Col.
I am wondering if I use this method, whenever I enter a new cell, will the newcell automatically include the formula?
I tried using avba code, (copy and pasted it as I’m an absolute novice with this), it worksfor the existing sheet but when I add a new
email address, ina new cell, it doesn’t copy the code into the new cell..
Just not surewhat to do here!
Any help wouldbe greatly appreciated..
Thanks again,
John.S
Ballarat, Vic

 

[Col Delane]

Hi John

I am wondering if I use this method, whenever I enter a new cell, will the new cell automatically include the formula?

It won't if your range is just an "ordinary" group of cells in the spreadsheet. However, if you convert that range to an Excel Table (select a cell inside the range & press Ctrl+T, then ensure your field headings are the 1st row of the table), the formula cells should automatically replicate in the new rows (just enter new data in the first blank row under your existing table, and hey presto the formula should be replicated).

 

[sterjohn]

Hi Col,
just wanted to say thanks for your help with my Excel problem, you're advice was great and really appreciated..
All the best,
John.S

 

[Col Delane]

No worries - thanks for the feedback.

 

[sterjohn]

Hi Col,
sorry to bother but I seem to have hit another snag here..
When I try to copy all the new email links that have been created in the table into the we have been speaking about into the BCC field, I get a message from the mail program
saying that the “format of the email address isn’t correct”..
I’m assuming that because all the addresses have the “mailto” prefix?
Would you know how I can get around this?
Thanks again Col,
Kind regards,
John.S

 

[Col Delane]

Interesting - as I don't get that problem when I copy the values from column E (Hyperlink) and paste into an email.

Is the "mailto" displaying in the cell of your Hyperlink field (as opposed to just being in the formula)? If so, it shouldn't be, for the hyperlink formula I included in my post #2 =Hyperlink( link_location, [friendly name] ) has that prefix only in the first argument, as below.

= HYPERLINK( "mailto:" & B4 & "?subject=" & C4 & "&body=" & D4, B4 )
You need the second argument with a simple direct link (e.g. =B4 ) to the Email Address field of your data table (as you can see that value gets used in both arguments)

This function is a little unusual, in that it returns two values but only displays one. The link_location argument doesn't display but is used to populate an email when you click on the cell to send an email directly from Excel (as you will notice that this argument also populates the Subject and mail Body fields of the email), whereas the [friendly name] argument returns the displayed value which is what you get when you copy the cell and paste it elsewhere (like a formula is when you use Copy + Paste Special Values)

I hope that clears your snag.

 

[sterjohn]

I'm sorry formy ignorance on this Col, I'm definitely not an expert by any stretch!
Basically, what I have, is two columns (A and B) plain text email addresses inColumn A and just need the links to be in column B.
For example, first email in Col A, Cell 3 is: a.david@dse.vic.edu.au
Can you suggest a formula to make this into a link in Col B, Cell 3 without"mailto" prefix?
I will then just copy the formula down.
Hope this isn't asking too much!!
Kind regards,
John.S

 

[Col Delane]

can you suggest a formula to make this into a link in col b, cell 3 without "mailto" prefix?

You should be able to use the formula I provided, and then when you want to send an email (which I now presume you're sending the same bulk email to multiple recipients in one go) just select the range of email addresses displayed in the Hyperlink formula column, copy, and paste into the address field of your email program.

If you're not interested in including the subject or email text in your spreadsheet, then just modify the formula to:
= HYPERLINK( "mailto:" & A3 , A3 )
which will still allow you to copy & paste multiple addresses as above, but also to click an individual cell in the Hyperlink formula to open a new email with the address field of your email program populated with that email address.

The minimalist approach is:
B3 = hyperlink( A3, A3 )