Create a Holiday Email in Excel, using HYPERLINK function

         It seems like this year passed by particularly fast, and now we are at a point when people expect to receive Holiday Greetings from us. Did you know to that you can compose your holiday email entirely in Excel, using the HYPERLINK function? We need to meet two basic requirements: 1) set up a default email client (Outlook, LotusNotes, Thunderbird, GroupWise, etc.,) AND 2) your entire message (including To/CC/BCC/Subject/Body fields) needs to be 255 characters long or shorter. While this method requires no macros, nor VBA code, it cannot actually “send” the email for you, it will open it in your default email client, but you still need to click the SEND button.

          HYPERLINK function has the following syntax (link_location,[friendly_name]). This means that the only required parameter is link_location, passing a sample email address argument, we can create a simple email link using this syntax: =HYPERLINK(“mailto:Email1@example.com”). I would strongly advise to also name your hyperlink, otherwise the cell containing it will appear to be blank. Unfortunately, we can find rather limited documentation on HYPERLINK function within Excel Help section. However, Microsoft did publish additional information about this function. This article can help us easily add To,CC,BCC,Subject, and Body fields to our email template.

         To add the first additional field to our hyperlink, we need to add question mark (?) prefix to that field. To add any additional fields, we need to use ampersand (&) prefix. While I used the question mark for the Subject field (Email etiquette requires us to always include subject line in our online communications, otherwise we risk people ignoring our emails,) Excel could’ve easily accepted it for any other email fields. Using HYPERLINK’s syntax, we can identify Subject as ?subject=Our subject line goes here, CC as &CC=Our Carbon Copy recipients go here, and BCC as &BCC=Our Blind Carbon Copy recipients go here. To send the below email, our hyperlink will be the following:
         =HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&C8&”&CC=”&C5&”&BCC=”&C6,C7). WHERE: C4 is our To field, C5 is our CC field, C6 is our BCC field, C7 is our Subject field, , and C8 is our Body field.

         Testing this formula, we realize that there is a slight problem with our body section: two line fields (empty lines) between our holiday wishes, and this blog link are ignored by our email client. (By the way these line feeds were entered using ALT+ENTER keyboard combination.) The reason for this is that we actually need to convert this command into its hexadecimal equivalent . Best practices suggest that due to typewriter setting differences between various operating systems and email clients, we need to use BOTH: line feed code (0A), as well as carriage return (0D) code. HTML syntax also requires us to use percentage sign (%) prefix. Using the above table, and CHAR Excel function, we determine that our line feeds could be identified by using CHAR(13) formula. To solve our predicament, we now need to replace all occurrences of CHAR(13) in the body of our email (cell C8) with %0D%0A.

         Since we don’t necessarily know exact position of the character that needs to be replaced, or the number of its occurrences for that matter, we cannot use Excel’s REPLACE function, but can in fact utilize its SUBSTITUTE function. This function has the following syntax: (text,old_text,new_text,[instance_num]). Since we need to replace ALL occurrences of the line feeds found, we don’t need to specify the optional instance number parameter. Our text is cell C8, new_text is %0D%0A, finally, our old_text is CHAR(13). We now need to replace argument C8 in our master formula, with SUBSTITUTE(C8,CHAR(10),”%0D%0A”) The resulting formula becomes:
         =HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&CC=”&C5&”&BCC=”&C6,C7)

         The next step is handling required and optional email fields. We already decided that both: To and Subject fields should be required. Let’s go ahead and make the body (text) of our email required as well. We can use Excel’s data validation rules [Data – Data Validation – Settings – Text length] to ensure that all of these three fields are always filled out. Note that you can separate multiple email addresses by coma. By design, both: CC and BCC fields are always optional, so we need to come up with some logic to accommodate different scenarios when they might or might NOT be present. There are 4 possible outcomes:

         1. CC field is blank, but BCC is not blank; this means that we need to specify BCC field ONLY in our hyperlink and omit any coding for the CC field.
         2. BCC field is blank, but CC is not blank; this means that we need to specify CC field ONLY in our hyperlink and omit any coding for the BCC field.
         3. BOTH: CC and BCC fields are blank, this means that we need to omit coding for both: CC and BCC fields.
         4. BOTH: CC and BCC fields are NOT blank, this means that we need to include coding for both: CC and BCC fields.

         Since any given email address should be at least 6 characters long, we will deem any entries that are less than 6 characters long to be invalid. This magic number is derived through a simple calculation: 1 character in the email box, 1 character for the @ sign, 1 character for the domain name, 1 character for the “.” symbol, and 2 characters for country-code TLD (top level domain: .US, .CA, etc.)

         The final formula is not overly challenging, but it does require some patience, and perhaps building blocks to be constructed:

         
=IF(LEN(C5)>5,IF(LEN(C6)>5,HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&CC=”&C5&”&BCC=”&C6,C7),HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&CC=”&C5,C7)),IF(LEN(C6)>5,HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&BCC=”&C6,C7),HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”),C7)))

         If you don’t want to go through the setup steps described, you can download my Excel solution here .