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. It worked, thanks a lot sweep. It uses a macro, which is a more complicated way to obtain the same result. However, if there are blanks in the column (empty cells i.e. With no email addresses), this macro keeps the cells blank whereas the hyperlink function creates a '0' link (that links to 'mailto:').
Every website has a 'win' — the intended action that a site visitor undertakes. Most websites support several possible wins. For example, a site may allow you to sign up for an email newsletter, register for an event, or download a whitepaper. Email offers a low-friction way of building a connection, so a mailto link on your site makes for a great general-purpose win.
Mailto links are links on web pages that point to an email address. When a website visitor clicks on one of these mailto links, the default email client on that person's computer opens and they can send a message to that email address specified in the mailto link. For many users with Windows, these links will pop open Outlook and have an email all ready to go based on the criteria you have added to the 'mailto' link.
![Mailto Link Multiple Emails Mailto Link Multiple Emails](/uploads/1/2/5/0/125023904/587371192.png)
These email links are a great way to provide a contact option on your website, but they do come with some challenges.
How to Create a Mailto Link
To create a link on your website that opens an email window, use a mailto link. For example:
To send email to more than one address, simply separate the email addresses with a comma. In addition to the address that should receive this email, you can also set up your mail link with a CC, BCC, and subject line. Add those optional items to the link by separating them with a question mark.
To avoid potential errors in your HTML, use %20 instead of a space. For example, the string test emailshould be represented as test%20mail.
For example, to specify an email that's sent to two addresses and CC'd to one address, and which specifies a subject line, use the following link:
The Downside of Mailto Links
As easy as these links are to add, and as helpful as they can be for many users, there are also downsides to this approach. Many spam programs crawl websites harvesting email addresses to use in their spam campaigns or to perhaps sell to others who will use these emails in this fashion.
Even if you don’t get a lot of spam, or have a good spam filter to try to block this type of unsolicited and unwanted communication, you might still get more email than you can handle. To help prevent this from happening, consider using a web form on your site instead of a mailto link.
Using Forms
Consider using a web form in place of a mailto link. Those forms can also give you the ability to do more with these communications since you can ask specific questions in a way that a mailto link does not allow for. With the answers to your questions, you may be able to better sort through email submissions and respond to those inquiries in a more informed manner.
In addition to being able to ask more question, using a form also has the advantage of not (always) printing an email address on the web page for spammers to harvest.
I have an Excel spreadsheet that has a list of
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.
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?
David 'mArm' Ansermot
2131 gold badge4 silver badges14 bronze badges
Craig GrahamCraig Graham
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:
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:
Concatenate the email addresses you're interested in in another cell, and hyperlink the result:
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.
Andi MohrAndi Mohr
3,4504 gold badges21 silver badges44 bronze badges
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.
Craig GrahamCraig Graham