I have someone’s email address in an Excel spreadsheet cell, an email subject line in a second cell, and email content in a third cell.
How can I combine them all into one cell as a hyperlink that will, when clicked, open up an Outlook email window with all of those items in their proper places?
Assuming A1 has the address, B1 has the subject line, and C1 has the content, use this formula:
=HYPERLINK(CONCATENATE(“mailto:”,A1,"&subject=",B1,"&body=",C1),“Click to email”)
The cell will display “Click to email” with a hypertext link (like an email address does in Excel) that will open your default email program. It works with Outlook, but doesn’t seem to work with the Microsoft Mail app/tile/whetever they’re called that’s in Windows 8.
Since you’re making a URL, you’ll need to be wary of URL encoding issues. Most importantly, make sure that your body and subject don’t have any ampersands in them. Either rephrase your text or replace each ampersand with “%26” (no quotes). Outlook can handle most of the other details, but double-check your generated emails before you send them out.
Thanks for the info. I tried it this morning, and it works with plain text as the body; this will save me a good bit of typing in the future. Is there a way I can include HTML in the body? Can I just copy/paste the source code from an HTML document, and replace any ampersands with %26?
The holy grail here would be for me to click once on a hyperlink in an Excel cell, and then just click ‘send’ on the email when it comes up all filled out and ready to go.
Email is still officially a plain text format. I don’t think the mailto URL type is equipped to encode HTML email. You can futz with it and see if you can get it to work but I suspect you’ll get an outbound plain text email consisting of the raw HTML code instead of what you want.
This is a surprise, since I can send/receive email containing hyperlinks and graphics. Such emails look fine whether I view them in Outlook, or on a web-based email service like gmail or Comcast.
I’m not looking to send a complicated HTML page with lots of stuff going on; what I want to send is just plain text with a couple of hyperlinks embedded in it.
He wasn’t real precise. There *is *HTML-formatted email. What there isn’t, within the confines of the mailto: interapp link protocol, is a way to specify the content to be treated as html.
Try this: Open Outlook and start an email to yourself. Make sure it’s set for html format rather than plain text or rtf.
Now type <b> this is a test </b>
Do you see bold text, or angle-brackets and b’s? Answer: angle brackets and b’s. The text is valid html, but the Outlook editor isn’t treating your input as html. It’s treating it as plain tedt that happens to include angle brackets.
Hit send. Once the email arrives open it.
Do you see bold text, or angle-brackets and b’s? Answer: angle brackets and B’s. The text is valid html, but the Outlook editor at the sending end definitely didn’t treat it as html. It treated it as plan text which coincidentally contained angle brackets.
So when it got to the other end, it was text within an html-formatted email. And the text included angle brackets. Which are treated as angle brackets not as html instructions.
If all you want to do is embed urls it might work anyway. Just put them in there as plain http://whatever etc. Do not use <a> tags.
Many email clients will autoparse those into clickable links at the receiving end even if they’re actually just plain text in the body of the email as sent by your Outlook.
Last of all, if you can do VBA programming you can bypass Outlook entirely and successfully send fully html-formatted email from your spreadsheet. But unless you’re already something of a VBA developer that’s not something we can teach you to do from scratch in a message board post.
There have been email clients that for years have used some method of formatting text despite it being outside of the boundaries of the official email standard. Before HTML pushed the other alternatives off-stage some email programs supported Rich Text Format (RTF) and there even a few before that that used something called “setext”. Since there was no official standard, for a long time there was a moderate risk that the recipient would see formatting code gobbledebook instead of formatted email. There’s still a minor risk of that but nowadays more often because someone has explicitly set their email preferences to NOT RENDER the html formatting. (There are also a small handful of people using text-only non-GUI email clients).
Anyway, so yeah, more often than not you can use it with the reasonable expectation that the recipient can view it. It’s still nonstandard, officially. ETA: And as LSL Guy points out with great examples, the mechanism for creating it within an email client is not to type in your own HTML encodings; the mechanism by which HTML email is sent is above and beyond the generation of text-string markup characters.
One of the few remaining consequences of that is that the mailto URL type (the algorithm for an external program to tell the OS to tell the designated email application to generate an email) doesn’t contain a mechanism for telling the email client program “the body is in HTML”
I’d quibble with the idea that HTML email is non-standard. It is most certainly defined within the MIME standards. Which are RFCs 2045-2049. Those are no less standards than text-based email is. Which is now defined in RFC 5322, which superseded 2822, which superseded the original RFC 822.
None of which matters to our OP. What does matter is practicalities.
You’re technically right that HTML support is, strictly speaking, optional in email clients. But graphics support is still optional in browsers; how many websites do you see that refuse to use anything but raw text just in case somebody shows up with a text-only browser? Answer: None.
In fact, one advantage of the OP using Outlook as a client to send html-formatted email is that Outlook will also automatically include a text-only version of the content. So someone who is using a text-only email reader will still get something useable. Someone seeing raw HTML gobbledygook coming from Outlook’s HTML-formatted email is someone using a buggy piece of 1995-era software, not a modern text-only email client. Those folks may (IMO) be safely ignored by all content developers.
Now there are folks who write email engines who don’t bother with both HTML and plain text content streams. Or who don’t get the headers right per the published standards and just hope the client app is smart enough to auto-detect and auto-parse what’s coded as plain text content which happens to include HTML syntax that the sender intends to be treated as HTML content.
In that case of being a buggy sender sending RFC-invalid email, yes a valid standards-compliant text-only client may show the end user HTML gobbledy gook.
The solution to that? Don’t be a standards non-compliant sender.
Yeah, good point. So does (or can) Thunderbird.