Hyperlink formula to email with cc: not working

[melodramatic]

I've got a hyperlink formula that is working fine, until I try to add the cc: to it, and then it flat-out bombs. I've re-written with the Excel tutorial lines open, counter ampersands, prayed and cussed, and it just won't work for me. I know it's probably a simple fix, and am hoping someone will share that fix with me.

The formula I have that IS working is...


=HYPERLINK("mailto:" & "accounting@abc.com"
& "?subject=" & "Reclassification of Expenses"
& "&body=" & "Attached is a listing of the expenses that need to be reclassified from Group-D to Group A. An explanation of each receipt is listed to go along with the totals.","eMail Accounting")



But, when I try to add the cc:, it's just failing. I've tried a couple of different ways, and in both ways, I get a #VALUE! in the cell. (on all of these, returns are added in the code for ease of reading).


=HYPERLINK("mailto:" & "accounting@abc.com"
& "?cc=" & "managerA@abc.com" & "; " & "managerB@abc.com"
& "&subject=" & "Reclassification of Expenses"
& "&body=" & "Attached is a listing of the expenses that need to be reclassified from Group-A to Group B. An explanation of each receipt is listed to go along with the totals.","eMail Accounting")


=HYPERLINK("mailto:" & "accounting@abc.com" & "?"
& "cc=" & "managerA@abc.com" & "; " & "managerB@abc.com"
& "&subject=" & "Reclassification of Expenses"
& "&body=" & "Attached is a listing of the expenses that need to be reclassified from Group-A to Group B. An explanation of each receipt is listed to go along with the totals.","eMail Accounting")


=HYPERLINK("mailto:" & "accounting@abc.com"
& "&cc=" & "managerA@abc.com" & "; " & "managerB@abc.com"
& "?subject=" & "Reclassification of Expenses"
& "&body=" & "Attached is a listing of the expenses that need to be reclassified from Group-D to Group A. An explanation of each receipt is listed to go along with the totals.","eMail Accounting")



I've tried all the formula variations above with only one email for the cc, and I still get the same error. Help?

 

[shknbk2]

You might be running into the 255 characters per formula issue. When I copy your first cc formula, delete the carriage returns, and delete "with the totals" from the body, it works for me. Otherwise, I get #VALUE like you did.

A similar issue is here, but it doesn't look like there was a satisfactory answer there either. Google searching didn't help me. There are suggestions about using CONCATENATE or '&' to join long strings, but I couldn't get it to work in the hyperlink formula. Using CONCAT on its own worked fine, but when referencing that cell in HYPERLINK, it failed. Using multiple cell references in the hyperlink formula also did not work when the result of all text concatenated together was longer than 255 characters.

 

[melodramatic]

Ah, so let me try putting the body in a cell that it references instead of typing it out. I'll let you know how that goes...

 

[melodramatic]

OK, even putting the body in another cell and referencing it, it runs past the limit. I'm glad you figured that error out!

I simply removed the entire last sentence from the body. They'll figure out by the list of receipts that the list is there. And, luckily, both emails do indeed show up in the cc: slot!

Thank you so much for your help!