How to Email Selected Excel Cells with a HYPERLINK Formula
Question details
The user wants to create a mailto hyperlink in a spreadsheet that automatically fills the recipient and subject, and includes a selected range of cells as the email body.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating the drafting of an email directly from spreadsheet cells without having to manually copy and paste the data into the email client.
- Observed behavior
- The user needs to combine cell values into a hyperlink but faces a 255-character limit, which returns a #VALUE! error if the combined text is too long.
Ensure that a default email client (such as Outlook, Windows Mail, or Apple Mail) is properly set up on your computer to handle standard 'mailto' links generated by the spreadsheet.
Use the HYPERLINK and TEXTJOIN Functions
Combine the HYPERLINK function with TEXTJOIN to dynamically build a mailto link using specific cells for the recipient, subject, and email body.
This formula method is highly effective for short messages. Keep in mind that spreadsheet formulas have a strict hyperlink limit of 255 characters. If your combined text string exceeds this limit, the formula will return a #VALUE! error.
Ensure you have designated cells for the email address (e.g., W1), the subject line (e.g., S1), and the text you want to include in the body (e.g., range A2:K3).
Select an empty cell and enter the formula: =HYPERLINK("mailto:" & W1 & "&subject=" & S1 & "&body=" & TEXTJOIN(",",,A2:K3), "Send Email")
In the TEXTJOIN function, you can replace the comma "," with "%0A" if you want the combined cells to appear on new lines in the email body.
Press Enter to apply the formula. Click the generated 'Send Email' link to open your default email client with the pre-filled information.

Use Mail Merge for Longer Cell Ranges
If your cell range exceeds the 255-character limit, using a Mail Merge in a word processor is the most reliable alternative.
Manage Spreadsheet Formulas and Emails Seamlessly with WPS Office
WPS Spreadsheet offers powerful formula support, including HYPERLINK and TEXTJOIN, fully compatible with Microsoft Excel. You can effortlessly manage data, generate automated email links, and handle large datasets for free.
- 1. Download and install: Get WPS Office for free from the official website and install it on your device.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
- 3. Apply the formula: Use the =HYPERLINK and TEXTJOIN functions exactly as you would normally. They are natively supported.
- 4. Generate email drafts: Click the linked cell to seamlessly pass data to your computer's default email client.

Frequently Asked Questions
Why does my HYPERLINK formula return a #VALUE! error?
The #VALUE! error typically occurs because the resulting hyperlink text string exceeds the strict 255-character limit in spreadsheet formulas. To fix this, you must reduce the amount of text in your target cells or switch to a Mail Merge.
Can I use line breaks instead of commas in the TEXTJOIN function for the email body?
Yes. The first argument in the TEXTJOIN function is the delimiter. To format the email body with line breaks, use the URL-encoded line break character "%0A" (with quotes) instead of a comma.
Does the mailto hyperlink work with web-based email like Gmail?
It depends on your operating system's default email client settings. If your computer's default mail handler is configured to open mailto links in a browser with Gmail, it will open a Gmail compose window. Otherwise, it will launch desktop applications like Outlook or Windows Mail.




