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)))