#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#5
|
|||
|
|||
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:
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:
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)) |
#6
|
||||
|
||||
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.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Quote:
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. |
#10
|
||||
|
||||
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.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#11
|
|||
|
|||
[QUOTE=Mark2438;144383]Hello, people,
Plz can u help how it can be work in Mac OS & outlook plz |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |