How can I include a HYPERLINK() to a Google Sheets document within a mailto: HYPERLINK() in Google Sheets?

Other Questions Google Sheets
1

I am trying to add a HYPERLINK() that points to another Google Sheets document in the body of a mailto: HYPERLINK(). However, it seems that the mailto: format does not recognize the embedded HYPERLINK() properly. Even though the syntax appears correct, the final output shows the link as plain text in both the draft and the received email.

I’ve attempted various strategies such as escaping double quotes around the URL, using a HYPERLINK() on a cell that contains the target document’s URL in both link and text formats, but nothing has worked. The result is always just plain text, whether in the draft or the inbox.

My aim is to improve user experience by creating an easily recognizable link like “Phil’s file” instead of the long URL, making it more user-friendly for recipients. My Google Sheets setup is designed to allow for simple email communications using intuitive names, but I find scripts unsuitable because users need a straightforward link to click on.

Here’s my current formula:

=IFERROR(ARRAYFORMULA(HYPERLINK("mailto:"&C2:C&"?Subject=Here is your call list&Body=Hi "+ARRAYFORMULA(E2:E)+"!"+"%0A%0AThanks for helping.%0A%0A"+D2:D,B2:B)))
2

Successfully embedding a hyperlink within a mailto link in Google Sheets can indeed be challenging due to how mailto links are interpreted by email clients. Often, these clients only render plain text in the email body without special formatting or clickable links. From my experience, a viable workaround is to use Google Apps Script to send your emails. This way, you have more control over the email content, allowing you to format the body with clickable links. You could trigger the script from the sheet itself, which makes it user-friendly. Although this approach might initially seem complex, it offers flexibility and ensures that your links work as intended once you’re familiar with setting up scripts in your Google environment.