1

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?

0

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)

enter image description here

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.

enter image description here

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

| improve this answer | |
  • Problem is the display text is the name of the person- so if I try anything with formulas I get the display names, not the email addresses. I can't find a way of getting the actual link target in a formula. – Craig Graham May 4 '16 at 11:33
  • Oh, you mean the cell value is literally the name of the person, and somebody has right-clicked the cell, added a hyperlink and embedded the email address that way? Ahh. OK yep we can deal with that. Hang on a tick. – Andi Mohr May 4 '16 at 11:42
  • Yup. Don't know why there's not just a seperate email column but it's not me maintaining it so it is what it is. – Craig Graham May 4 '16 at 11:44
0

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 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.