Sometimes it is difficult to create a clickable email in MS Excel. when we write the email the email turns out to be in a text form and with the help of a certain formula we can convert this email into a hyperlink or a clickable form.
The problem with using the hyperlink function in Excel is it needs special syntax when you want to send an email and Excel doesn’t give you help while you’re typing this, so there are no special arguments that you can just work your way through.
Step by Step Explanation of making email clickable in excel
When you have a list of emails and want to create dynamic email hyperlinks to these. Now if you were going to do this manually with just right-mouse click here, go to the link, select the email address, and type in the email address
It starts with a question mark then the subject equals and then it puts the text that I typed in. Now, this is the syntax that we need inside our formulas.
So the question mark is the start of defining the attributes. If we have more attributes here, we need to use the ampersand. So let’s make this dynamic because you could have a lot of emails and it’s going to cost you a lot of time if you had to do each one manually.
So we’re going to use the hyperlink function. All the syntax we need for the email we have to put in the first argument here called link location.
So remember, Excel gave us that mail to syntax that’s the syntax we need here. And since we’re typing text inside a function, we need to use the quotation marks so that was mailto and then there was a colon.
Now add quotation marks again and because we want to combine this to a cell reference I need the ampersand and that’s my cell. Now let’s say I just want the email address, I don’t have a subject line or body text here, so I’m just going to go to the next argument which requires the friendly name, so basically what I want my link to be called and I’m going to put send an email, quotation, close bracket, press enter.
Now when I click on this, I get Kim’s email address in the To field right here. Okay, so let’s expand on this and add a subject line. One option is to just type the text directly in your formula or make it dynamic. So let’s just make it dynamic.
Now, all we have to do is to expand on the link location argument by adding in the subject. Now again, because I’m combining some syntax, some text in my formula, I need to use the ampersand quotation.
Now the syntax we saw before starts with a question mark then it is a subject and equal sign and now I’m going to add the quotation and combine those with this cell. Since I’m planning to drag this down, I’m going to fix this reference by pressing F4.
The subject is right there. What about adding Cc to this? So before we add the body text, let’s take a look at Cc. Let’s add it here and make sure our boss is on Cc. How do we update this? We just continue writing here this time because I’m adding more attributes to this. I need the ampersand. The syntax for Cc is just cc, add the equal sign, and combine it with this cell reference and I’m going to fix it with the F4 key.
Let’s add another attribute for the link location. This time I’m going to add the body text. So again, &body equals, and now let’s combine it with this cell. And let’s fix it and press enter, double-check. It’s all there.
Aayush Kumar Gupta is the founder and creator of ExploringBits, a website dedicated to providing useful content for people passionate about Engineering and Technology. Aayush has completed his Bachelor of Technology (Computer Science & Engineering) from 2018-2022. From July 2022, Aayush has been working as a full-time Devops Engineer.