Mailing multiple mailto: links in an Excel spreadsheet

I have an Excel spreadsheet that has a list of mailto: hyperlinks in one of the columns.
I'd like to be able to send an email to a selection of them, but clicking on one- even as part of a selection- just sends that one link to the mail program, and trying to copy them copies only the display text- there seems no way to copy the underlying link.

I also can't find a way of accessing the underlying link from a formula in another cell to concatenate the different logical groups of people into single links containing all their addresses.

I've googled around and found many solutions for links with multiple addresses in them, but not one that takes a set of links each containing a single address as the starting point.

Anyone know how to do it?

2 Answers

We're going to have to use a bit of VBA here, but not to worry - it's nice and easy!

Press Alt+F11. This will bring up the Visual Basic Editor. From the top menu bar, click Insert then Module. Paste the following code into the window that appears on the right:

Function GetEmailAddress(EmailCell As Range) As String GetEmailAddress = Replace(EmailCell.Hyperlinks(1).Address, "mailto:", "")
End Function

You can close this window now and go back to your spreadsheet.

Add a column to the right of your list of names containing links. We're going to store our email addresses here. Enter this formula and copy down:

=GetEmailAddress(A2)

Concatenate the email addresses you're interested in in another cell, and hyperlink the result:

=HYPERLINK("mailto:"&A2&","&A3&","&A4&","&A5,"email people")

This produces a link saying email people as shown in cell D4 in the screenshot. When you click the link it sends the list of addresses to your email client.

Note - because we've added some VBA code we will need to save the file as a .xls or .xlsm file.

Since the accepted answer involves making an extra column, and I'm actually not too bothered about having a final hyperlink- I'm quite happy with just copying and pasting a string to the email program- I've adapted the accepted answer with this function. Might be useful to someone doing similar and it didn't work as a comment.

Function GetEmailAddress(EmailCell As Range) As String Dim addressList As String Dim sep As String For Each c In EmailCell.Cells addressList = addressList & sep & Replace(c.Hyperlinks(1).Address, "mailto:", "") sep = "," Next GetEmailAddress = addressList
End Function