Text & String Cleaning
Build Dynamic mailto: Email Links With Subject, CC & URL-Encoded Body
Assemble RFC-6068 mailto: URIs from spreadsheet recipient, CC, subject, and body cells—replacing spaces with %20 via SUBSTITUTE so mail clients never truncate subjects at the first space.
Prevent broken mailto: subjects by encoding spaces as %20!
Account-receivable teams, event coordinators, and CRM ops analysts love generating personalized one-click email links directly from spreadsheet rows: mailto:recipient@domain.com?cc=...&subject=...&body=...—but raw spaces inside query parameters frequently truncate the subject line in webmail clients or corporate Outlook handlers!
Replacing spaces with "%20" via SUBSTITUTE(TRIM(text), " ", "%20") produces clean RFC-6068 mailto: URIs that preserve every word of the subject and message body! Even better, checking IF(B3 = "", "", "cc=" & TRIM(B3) & "&") omits the cc= parameter cleanly whenever no CC address is entered, keeping the 255-character Excel HYPERLINK limit lean and valid!
| Email Field | Spreadsheet Cell Input | RFC-6068 Encoded Component |
|---|---|---|
| Recipient To (B2) | ap@northwind.io | mailto:ap@northwind.io? |
| Optional CC (B3) | cfo@northwind.io (or blank) | cc=cfo@northwind.io& (omitted if blank) |
| Subject Line (B4) | Invoice 1042 Past Due | subject=Invoice%201042%20Past%20Due |
| Short Body (B5) | Please remit balance | &body=Please%20remit%20balance |
RFC-6068 mailto: URI Builder With Optional CC & %20 Encoding
Validates that Recipient B2 contains "@" and encodes spaces in Subject B4 and Body B5 as "%20".
=IF(ISERROR(FIND("@", B2)), "Invalid Recipient Email", "mailto:" & TRIM(B2) & "?" & IF(OR(ISBLANK(B3), TRIM(B3) = ""), "", "cc=" & TRIM(B3) & "&") & "subject=" & SUBSTITUTE(TRIM(B4), " ", "%20") & "&body=" & SUBSTITUTE(TRIM(B5), " ", "%20"))Verify Total URI Length Against Excel's 255-Character HYPERLINK Limit
Checks character length of the generated mailto: URI (Excel's HYPERLINK function returns #VALUE! if URI exceeds 255 characters).
=LEN("mailto:" & TRIM(B2) & "?" & IF(OR(ISBLANK(B3), TRIM(B3) = ""), "", "cc=" & TRIM(B3) & "&") & "subject=" & SUBSTITUTE(TRIM(B4), " ", "%20") & "&body=" & SUBSTITUTE(TRIM(B5), " ", "%20"))Try the example
Use the original values or change them to test your understanding.