The essence of this method is to use the standard Excel function HYPERLINK (HYPERLINK), which was originally designed to create links to external resources in the cells of the sheet. For example, like this:
The first argument to the function is a link, the second is the placeholder text in the cell that the user sees. The trick is that you can use a standard construction from the HTML markup language as a link mailtoAn that creates a mail message with the given parameters. In particular, here is such a construction in the formula:
will generate when the user clicks on the link, this is the message:
If necessary, you can add a copy (CC) and a hidden copy (BCC) and text (Body) to the created letter to several recipients. Here is a formula, for example:
=HYPERLINK(“mailto:[email protected], [email protected]?cc=[email protected]&bcc=[email protected]&subject=friendly gatherings& Body =Friends!%0AI have an idea.%0AWhy don’t we clap a glass?“;”Send”)
=HYPERLINK(«mailto:[email protected], [email protected][email protected]&[email protected]&subject=Friendly get-togethers&body=Friends!%0AAI have an idea.%0AAShould we clap a glass?”,”Send”)
will make us a full-fledged mail message with a bunch of recipients, a subject and text:
When entering such a long construction, be careful not to overdo it with extra spaces and quotes. Also do not forget to insert a separator into the body (body). %0A (percentage, zero and English A) if you want to spread your text across multiple lines.
The advantages of this method are simplicity, any similar methods involve the use of macros. There are also cons:
- unable to attach file to message (mailto does not support this feature for security reasons)
- the maximum length of text in the first argument of the HYPERLINK function is 255 characters, which limits the length of messages
- to send a letter, you must manually click on the link.
Having danced a little with a tambourine, you can even create a simple form that will create a text string from the given fragments with parameters as the first argument of the HYPERLINK function:
The formula in E2 will be:
=»mailto:»&C2&», «&C3&»?cc=»&C5&», «&C6&»&bcc=»&C8&», «&C9&»&subject=»&C11&»&body=»&C13&»%0A»&C14&»%0A»&C15&»%0A»&C16&»%0A»&C17
- Mailing list with PLEX add-on
- Various ways to send mail messages from Excel