Today, a great idea from Yure in Brazil.
He is using concatenation inside the HYPERLINK function to create a MailTo hyperlink. Click the cell in Excel and your e-mail program opens with the Recipient, Subject, and Body already filled in.
However... it the hyperlink grows to more than 255 characters, we are getting a #VALUE! error instead.
Can you go and vote to ask the Excel team to increase the limit for the HYPERLINK function: Community
In the meantime, you can use VBA code from Ron De Bruin to send e-mails from Excel. See his code at: Mail from Excel and make/mail PDF files (Windows)
Who remembers the time that Kelly Rowland used Excel to SMS Nelly? Here is a link to that music video.
Table of Contents
(0:00) Sending e-mail from Excel using HYPERLINK
(1:19) Hyperlink fails with more than 255 characters
(1:52) Ron De Bruin's VBA pages for emailing from Excel
He is using concatenation inside the HYPERLINK function to create a MailTo hyperlink. Click the cell in Excel and your e-mail program opens with the Recipient, Subject, and Body already filled in.
However... it the hyperlink grows to more than 255 characters, we are getting a #VALUE! error instead.
Can you go and vote to ask the Excel team to increase the limit for the HYPERLINK function: Community
In the meantime, you can use VBA code from Ron De Bruin to send e-mails from Excel. See his code at: Mail from Excel and make/mail PDF files (Windows)
Who remembers the time that Kelly Rowland used Excel to SMS Nelly? Here is a link to that music video.
Table of Contents
(0:00) Sending e-mail from Excel using HYPERLINK
(1:19) Hyperlink fails with more than 255 characters
(1:52) Ron De Bruin's VBA pages for emailing from Excel
Transcript of the video:
Here's the use you never thought of for the HYPERLINK function.
Today's question from Yure in Brazil.
He is trying to use the HYPERLINK function to send an email.
And while it's working, a lot of the time, there is a very annoying 255 character limitation.
All right, so check this out.
Here's a database over here, people who have overdue invoices.
We know how many days late they are, and we put together a nice little email here.
Dear so-and-so, courtesy reminder that your account is blank days overdue.
Please remit this amount at your convenience.
And then when it gets later, the formula kind of changes, all right?
So over here, the HYPERLINK function.
Equal hyperlink, mail to colon.
And then you put in the email address.
And then a question mark, subject equals that.
And then the body equals that.
Let's take a look at this.
If I press F2, F9, you can see that it puts together this nice little hyperlink.
And here is how awesome this is.
So I have to send these 20 some emails, right?
I just click, and even though I'm using Gmail, it just opens, right?
It's just beautiful.
Click send and then come back.
And the next one, I'll be able to send these 25 in minutes instead of 25 minutes.
But here's the limitation.
Some of these in real life are not working.
And the reason they're not working is because the hyperlink contains more than 255 characters.
So 259, 256, 259.
It's the old 255 character limit.
So if you think this is a cool idea and you think that a hyperlink should be larger.
There's already a Microsoft feedback.
“Permit hyperlink to exceed 255 characters”.
Please down in the YouTube description, there'll be this link, go and give us a vote. Now, when I talked to Yure, he said, well, okay, the 255 character limitation was really too small, so I ended up using VBA to send the emails, and I agree with that.
I use VBA to send emails as well.
And the expert on this is Ron de Bruin who has a beautiful website right here.
I'll put this link down in the YouTube description.
With all kinds of code that you can copy that would allow you to send longer emails.
Even with attachments and so on.
So check that out.
And if you need to send SMS from Excel, well apparently Kelly Rowland has a way to do that.
Well, look, I want to thank Yure for sending that great question.
And I want to thank you, especially if you went ahead and voted.
We'll see you next time for another netcast from MrExcel.
Today's question from Yure in Brazil.
He is trying to use the HYPERLINK function to send an email.
And while it's working, a lot of the time, there is a very annoying 255 character limitation.
All right, so check this out.
Here's a database over here, people who have overdue invoices.
We know how many days late they are, and we put together a nice little email here.
Dear so-and-so, courtesy reminder that your account is blank days overdue.
Please remit this amount at your convenience.
And then when it gets later, the formula kind of changes, all right?
So over here, the HYPERLINK function.
Equal hyperlink, mail to colon.
And then you put in the email address.
And then a question mark, subject equals that.
And then the body equals that.
Let's take a look at this.
If I press F2, F9, you can see that it puts together this nice little hyperlink.
And here is how awesome this is.
So I have to send these 20 some emails, right?
I just click, and even though I'm using Gmail, it just opens, right?
It's just beautiful.
Click send and then come back.
And the next one, I'll be able to send these 25 in minutes instead of 25 minutes.
But here's the limitation.
Some of these in real life are not working.
And the reason they're not working is because the hyperlink contains more than 255 characters.
So 259, 256, 259.
It's the old 255 character limit.
So if you think this is a cool idea and you think that a hyperlink should be larger.
There's already a Microsoft feedback.
“Permit hyperlink to exceed 255 characters”.
Please down in the YouTube description, there'll be this link, go and give us a vote. Now, when I talked to Yure, he said, well, okay, the 255 character limitation was really too small, so I ended up using VBA to send the emails, and I agree with that.
I use VBA to send emails as well.
And the expert on this is Ron de Bruin who has a beautiful website right here.
I'll put this link down in the YouTube description.
With all kinds of code that you can copy that would allow you to send longer emails.
Even with attachments and so on.
So check that out.
And if you need to send SMS from Excel, well apparently Kelly Rowland has a way to do that.
Well, look, I want to thank Yure for sending that great question.
And I want to thank you, especially if you went ahead and voted.
We'll see you next time for another netcast from MrExcel.
