Microsoft Office Forums How to have a Hyperlink Mailto with more than 255 characters.

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2019, 10:40 AM
Mark2438 Mark2438 is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2007
Novice
How to have a Hyperlink Mailto with more than 255 characters.
 
Join Date: Aug 2019
Posts: 4
Mark2438 is on a distinguished road
Default How to have a Hyperlink Mailto with more than 255 characters.

Hello, people,

I've done a lengthy search, found many people with the same problem, but no answers.
How, in Excel 2007, to have a Hyperlink Mailto with more than 255 characters.
Looked at Ron de Bruin's RDBMail and SendMail add-ins, but they work only with MS Outlook.
I'm using Mozilla's Thunderbird.
Attached is a sample spreadsheet with my attempts, with or without the Concatenate function.
When the email is longer than 255 characters, Excel says #VALUE!

I simply want to Click on the cell, and it will open up a new Tbird outgoing email.

Any help would be Greatly appreciated.
Mark2438
Attached Files
File Type: xlsx excel email over 255 13.xlsx (8.7 KB, 1 views)
Reply With Quote
  #2  
Old 08-25-2019, 11:21 AM
NoSparks NoSparks is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

You can't get around the 255 character limit of the hyperlink,
but you can change your approach and use the Worksheet_Followhyperlink event to call a macro that will shell out to Thunderbird.

I put some email addresses on rows 10 to 30 and copied your existing other stuff.
Ran the AddHyperlink sub to put in the hyperlinks.
Use the Worksheet_Followhyperlink event to get the row for the info and to call the SendEmail macro.

Code to shell to Thunderbird found here:
How can I automatically send email from Thunderbird with Excel VBA? - Stack Overflow

Hyperlink to run macro found here:
Link Hyperlink to Excel Macro — Excel Dashboards VBA and more

Doc to add hyperlink using code found here:
Hyperlinks.Add method (Excel) | Microsoft Docs

Hope this helps.
Attached Files
File Type: xlsm Email_using_Thunderbird.xlsm (18.4 KB, 2 views)
Reply With Quote
  #3  
Old 08-25-2019, 02:13 PM
Mark2438 Mark2438 is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2007
Novice
How to have a Hyperlink Mailto with more than 255 characters.
 
Join Date: Aug 2019
Posts: 4
Mark2438 is on a distinguished road
Default

NoSparks,. . . YOU Are BRILLIANT ! I Love you !
I just downloaded your spreadsheet, and It Works ! I don't know How it works, But It Does !
I've seen a few other VBA solutions that allegedly might work, but:
I can't even install the durn things; can't even get them to do anything.
And That Right There Pisses Me Off ! Never done any VBA stuff before, but
I ain't no dummie; been Working with computers for 45 years.
Back then, I was programming Assembler language on IBM mainframes.

Searched for "how to install an excel vba script" and read all of:
How to Install and Uninstall A VBA Add-in File For Microsoft Office — The Spreadsheet Guru
How To Use The VBA Editor In Excel: Explained Step-By-Step
How Do I Create a Microsoft Excel Script? | It Still Works
Insert and run VBA macros in Excel 2016, 2013 - step-by-step guide
How to Create Visual Basic for Applications (VBA) in Excel with Examples
How to Create a MS Excel Script | Chron.com
Still, cannot make a FUNCTION or SUB script do anything.
I even tried getting MrExcelHTMLMaker do do something; no success.
So as not to waste too much of your time,
can you point me toward some further reading to learn how / why this thing now Runs?

You mentioned a StackOverflow thing to make Tbird auto-send the email. I don't want that.
I only want the outgoing email to open up, and your VBA thing does exactly that.
I will need to edit the email before hitting Send.

I'm going thru your spreadsheet, to try to determine HOW the VBA thing runs.
In Excel > Developer > Visual Basic, there are 8 items under Modules.
Of course, zzChangeLog is not really neccesary, but I'll leave it there.
And zAntiquatedCode is also probably not used?
mUI is empty. Why is it there?

I assume that the main control part is in Sheet1 (test255), also called Sheet1 (Code).
I see where the TO and SUBJECT and BODY come from, but how does the operable Link get into column A?
I don't see how column A cells are filled.
That info is needed because in my real spreadsheet, the operable links are in column F.

Tried to make a minor change (changing "Click here" to "Click"), but the spreadsheet didn't change.
Even saved the file (as a different name), exited Excel, then re-opened the file.
I searched; that "Click here" text now appears nowhere in all 12 modules,
but the spreadsheet still says "Click here". Why is that?
Crap. FergetAboutIt. I simply changed the text IN the Spreadsheet. That worked.

In that same module, you referenced the TO cells with Set rng = .Range("B10:B30")
My real spreadsheet will have anywhere from 50 to 150 rows.
Can I set that to Set rng = .Range("B2:B200") even though cells B190 to B200 will probably always be empty?

Crap. Just to test, I changed that to Set rng = .Range("B10:B20") and cell A28 STILL WORKS.
OK. I apparently don't know how to change any of the parts in the VBA script.

Now, I need to learn how to adapt your VBA thing to my real spreadsheet.

THANKS VERY MUCH. I clicked to Add to your reputation.
Reply With Quote
  #4  
Old 08-25-2019, 05:05 PM
NoSparks NoSparks is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Mark, since my last post I've seen your same question on multiple forums.
Please have a read of this and adhere to common forum etiquette.

Thanks.
Reply With Quote
  #5  
Old 08-25-2019, 09:19 PM
NoSparks NoSparks is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Glad it works.
Any issues applying it to your real worksheet just come back and ask, we can sort it out.

Your second post wasn't there for me when I made my second post and I never received email notification of it.

Quote:
In Excel > Developer > Visual Basic, there are 8 items under Modules.
Of course, zzChangeLog is not really neccesary, but I'll leave it there.
And zAntiquatedCode is also probably not used?
mUI is empty. Why is it there?
What the heck are you talking about ?
The workbook I posted has all the code on the test255 sheet module, that's it, no other modules exist.
Do you have more than one workbook open ?

Quote:
how does the operable Link get into column A?
I don't see how column A cells are filled.
That's what the AddHyperlink sub did when it was run, and it only needed to be run once, although
if you changed the TextToDisplay and did run it again it would have overwritten the existing hyperlinks.
I left it in so you could see how the links were done.
The range of email addresses is hard coded to range("B10:B30") then for each cell in that range the hyperlink is built in the cell offset 1 column to the left (the -1).

I don't know what your actual text will be in the cells for the email body.
Based on what you have in the worksheet you attached, I would change the body line in the code so the line feeds are changed.
Code:
    body = Replace(Range("D" & lnkrw), "%0D%0A", Chr(10)) & Replace(Range("E" & lnkrw), "%0D%0A", Chr(10))
Reply With Quote
  #6  
Old 08-25-2019, 11:57 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Hi and welcome
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question

Do not post any further responses in this thread until a link has been provided to these cross posts.
__________________
Please note I am not active on this forum anymore
Reply With Quote
  #7  
Old 08-26-2019, 10:26 AM
Mark2438 Mark2438 is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2007
Novice
How to have a Hyperlink Mailto with more than 255 characters.
 
Join Date: Aug 2019
Posts: 4
Mark2438 is on a distinguished road
Default

Mea culpa, mea culpa, mea maxima culpa.
Yes, I apparently cross-posted, and I sincerely apologize.
I did not realize that it was uncool, but the linked explanation makes that pretty clear.
And understandable. I shan't do it again.
On the other site(s), I have provided a link to this post, so the solution is known.
My humble Thanks to this forum, and specifically to NoSparks, for the time and effort spent.
Mark2438
Reply With Quote
  #8  
Old 08-26-2019, 01:32 PM
Mark2438 Mark2438 is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2007
Novice
How to have a Hyperlink Mailto with more than 255 characters.
 
Join Date: Aug 2019
Posts: 4
Mark2438 is on a distinguished road
Default

BOUNTIFUL THANKS to you, NoSparks, for your continuing help on this matter.
I hope that my previous post is sufficient to keep me from getting 86'd.

What the heck are you talking about ?
Do you have more than one workbook open ?

No other workbooks are open, but now I understand:
As mentioned earlier, I had tried to use MrExcelHTMLMaker, so that script was still active.
I unchecked it, restarted, and all is well. I now see only your script.

I understand how you referenced the column A cells with the "-1" offset. Cool.

Again, I changed it to Set rng = .Range("B10:B20") and TextToDisplay:="Click"
then Saved the file, exited Excel, and reopened the file.
Cell A28 Still Works, and all the column A cells still say "Click here".
Apparently, I need to "run" the script again, after re-opening the file?
Is the script not automatically Run when the file is Opened?

You noticed: in my original sample cells, the INTRO cells have %0D%0A for CR/LF,
while the BODY cells do not. That's because: some time ago, the emails were not displaying correctly.
I've just changed the text in the INTRO cells, to look like the BODY cells, and it works well. No more %0A%0D.
Reply With Quote
  #9  
Old 08-26-2019, 02:12 PM
NoSparks NoSparks is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Quote:
Apparently, I need to "run" the script again, after re-opening the file?
Is the script not automatically Run when the file is Opened?
No, not run automatically, could be if that's a necessity but I can't envision that.
If you edit the macro all you need to do (while the cursor is in the macro) is hit F5 to run it.

Good luck with your project.
Reply With Quote
  #10  
Old 08-26-2019, 11:20 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline How to have a Hyperlink Mailto with more than 255 characters. Windows 7 64bit How to have a Hyperlink Mailto with more than 255 characters. Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

To be perfectly clear, cross posting is NOT prohibited. All we ask ( same as other forums) is that you ADD LINKS to your cross posts on every forum you posted in.
__________________
Please note I am not active on this forum anymore
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to have a Hyperlink Mailto with more than 255 characters. Stop Word from displaying {HYPERLINK "mailto:my_email@provider.com"} in Print Layout View Stargehzer Word 2 11-17-2018 07:07 PM
Strange Characters appear when selecting SHOW ALL NON PRINTING CHARACTERS ann Amber Word 1 08-01-2015 08:06 PM
How to have a Hyperlink Mailto with more than 255 characters. mailto Hyperlink in MailMerge robpgreer Mail Merge 3 05-06-2011 04:27 PM
Decoding mailto subjects smaslin Outlook 0 03-09-2009 08:09 AM
mailto in word cinvernizzi Word 0 02-17-2009 03:03 PM


All times are GMT -7. The time now is 05:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft