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

Mark2440 · Aug 24, 2019

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.
I've tried, with or without the Concatenate function.
When the total email is longer than 255 characters, Excel says #VALUE!
I simply want to Click on the cell, and it will open up a new outgoing email.

Any help would be Greatly appreciated.
Mark2440

Kenneth Hobson · Aug 24, 2019

The limit is probably due to Thunderbird and your web browser.

You should get one to compose before automating it. There are several examples. It is just a matter of building the string to use in Shell(). The concept is similar to MailTo but uses command line options allowed by Thunderbird. Search for "excel vba thunderbird".

I would show code but I can not test it since I don't use Thunderbird anymore. Most of the solutions that you will find require a SendKeys() to actually Send a composed email.

You can post the working routine when ready for more help. If stuck, post back.

Mark2440 · Aug 25, 2019

Well, thanks for the reply, Kenneth, but me thinks that you are completely off-base.
It is EXCEL that has the limit of 255 characters in a Hyperlink.
I'm SURE that this problem has nothing to do with Tbird or the Firefox browser.
Did you even Look at the sample spreadsheet that I included?
I have No idea what you're referring to with the Shell() or SendKeys().

Kenneth Hobson · Aug 25, 2019

Yes, the =Hyperlink() function is 255/256 character limited. I was offering an alternative "VBA" solution.

I don't have Excel 2007 so I don't know if it can handle long pasted URLs as a hyperlink. If it can, then another alternative VBA solution can be used. If you are married to the hyperlinks MailTo protocol approach, the Range Hyperlinks method might be your best choice.

I would prefer the first alternative myself. One can add attachments and more. Character translations are not an issue either. For the 2nd alternative method, the VBA code could use Replace() to do the characters to html character translations later if needed.

As for Shell() and Sendkeys() commands, those are VBA commands. This is a VBA macros forum. You can click in or next to a word in the VBE, Visual Basic Editor to get specific help for command words. F2 can be used to browse all commands by category. I am not a fan of SendKeys() but it is the only solution for some things.Since you would just be opening to compose, it would not be needed as I explained.

Both of the alternative methods can make use of the Worksheet Selection or Change events to trigger runs.

Mark2440 · Aug 25, 2019

Thanks for the continued conversation, Mr. Kenneth.
I'm hesitant to try a VBA solution, because I have tried, but cannot get Any VBA SUB or FUNCTION thing to even execute.
AND, I'd prefer to Avoid the extra hassle of going thru Excel > Developer > Macros > select it > Run.
I simply want to Click on the cell, and it will open up a new outgoing email.
Thus, maybe VBA is Not the solution?

Never heard of "range hyperlinks". I'll look it up.
Never heard of "Worksheet Selection or Change events". I'll try to find what that is.

Thanks.

Kenneth Hobson · Aug 25, 2019

I don't know of any other way to solve it other than VBA. There are many ways to run a macro. Some are automatic and some are manual. e.g. Command buttons, shapes, from Developer ribbon or custom ribbon, worksheet or workbook events, hot keys, etc.

For the range hyperlinks solution, a change event would update the hyperlink. e.g. If any cell in B2:E10, where row 10 is last row used in the sheet is changed, the hyperlink is added to column A for each changed row. The range to monitor for change can be whatever you want.

For the Thunderbird solution, Selecting a defined cell in Column A would run it. Or, select a row, and then run a macro using data from B:E for that row to open compose and fill in Thunderbird.

There are some videos here. https://chandoo.org/wp/excel-vba/videos/
Controls like Command button, help can be found here. https://chandoo.org/wp/form-controls/

The hyperlinks method by worksheet change event can be done like this. Copy your worksheet or workbook and test. Right click the tab for that worksheet, View Code, and then paste. With version 2007 I can not be sure this will run. It works fine in 365. To force it to update column A and add the hyperlinks, select the column B cells, and then Cut (Ctrl+X) and Paste (Ctrl+V). All done...

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim h As Hyperlink, r As Range, c As Range, n As Long
    
    Set r = Intersect(Target, Range("B2:E" & Cells(Rows.Count, "B").End(xlUp).Row))
    If r Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    On Error Resume Next
    For Each c In r.Rows
        n = c.Row
        Set r = Cells(n, "A")
        With r
            .Hyperlinks(1).Delete
            '"mailto:"&B2&"?Subject="&C2&"&Body="&D2&E2
            .Hyperlinks.Add r, "mailto:" & Cells(n, "B") & "?Subject=" & Cells(n, "C") & "&Body=" & _
                Cells(n, "D") & Cells(n, "E"), , , "Click Here"
        End With
    Next c
    
    Application.EnableEvents = True
End Sub

Mark2440 · Aug 25, 2019

My first question, Mr.Kenneth,:
Can a VBA solution be created wherein I would simply Click on a cell in column A and the outgoing email would appear in Tbird?
If that IS possible, then I'd be open to a VBA solution.
HOWEVER, I previously mentioned that I cannot get Any VBA SUB or FUNCTION thing to even execute.
And That Right There Pisses Me Off !
I ain't no dummie, and I've 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:

https://www.thespreadsheetguru.com/blog/how-to-install-uninstall-vba-add-in-file-microsoft-office

How to Open & Use the VBA Editor in Excel (Visual Basic)

In this guide, we’ll focus on the most basic parts of the Visual Basic editor and talk about modules and containers. Read more here.

spreadsheeto.com

How Do I Create a Microsoft Excel Script?

Actions can be automated in Microsoft Excel using both macros and VBA scripting, and set to activate on-command or when a condition is met.

itstillworks.com

Insert and run VBA macros in Excel - step-by-step guide

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

www.ablebits.com

VBA in Excel: What is Visual Basic for Applications, How to Use

VBA stands for Visual Basic for Applications, In this tutorial we learn with examples to Create Excel Macro like Hello world, Simple EMI calculator.

www.guru99.com

How to Create a MS Excel Script

Whether your company uses Excel for payroll and accounting purposes, project management or data collection, being able to automate common processes is hugely beneficial. Excel features a macro recorder under the Developer tab on the Ribbon that allows you to record basic macros, but some...

smallbusiness.chron.com

Still, cannot make a FUNCTION or SUB script do anything. Should be easy, me thinks.
So as not to waste too much of your time, can you point me toward some further reading to get a VBA script to run?
I started looking at some of the Chandoo videos that you provided a link to, but I cannot understand his English.

Mark2440 · Aug 25, 2019

p.s. I don't care at all about the friendly "Click here" text. I just want this thing to work.

Kenneth Hobson · Aug 25, 2019

If you followed my instructions in post #6, the hyperlinks range method would have worked but for 2007, I can't be sure.

Right click the tab for that worksheet, View Code, and then paste.

Obviously, you would first copy the copy from #6.

Sounds like you don't have macros enabled in your security options. File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable All... and Trust access to VBA... > Ok > OK.

Of course you should save the file as an XLSM file type or XLS (older versions).

Quick Lessons:
For 2007, some of this may be wrong but concepts are similar. e.g. Ribbon in 365 might be a menu/toolbar in 2007.

When developing VBA code and interacting with it as a user or a coder, enable the Developer ribbon. Right click your ribbon, and Customize it. Check the box on right for Developer. You can open the VBE, Visual Basic Editor from Developer or Alt+F11. In the View menu, I enable windows: Project Explorer (Ctrl+R), Properties (F4), Code (F7), and Immediate (Ctrl+G). Those are my main windows most often used as I develop code.

Lets do a simple example. This is what I use to quickly test one line of code. In the Immediate window, type:

Code:

MsgBox "Hello World!"

After pressing the enter key at the end, it will execute.

2nd example, make a public sub. These are usually stored in Modules. In a new blank workbook, the Project Explorer will show Book1. You can right click it or one of its worksheet object or its ThisWorkbook object and select Insert > Module. Notice that Project Explorer added a Modules object with Module1 added under it and open in the Code window. The View menu can add Modules as well. In the code window:

Code:

Sub Hi()
  MsgBox "Hello World!"
End Sub

With cursor in the sub, press the ">" looking icon for run, or Run menu or F5. Before running code, it is best to Compile it first. That is in the Debug menu. I customize the toolbar and put it next to my Reset icon since I used it so often.

Once that works, add a hidden Personal workbook. I use mine to store most often used macros. An easy way to create one from scratch is to record a macro. Select Record in Developer, name it, and then click a different cell, Stop recording. Check Project Explorer to see that it was added.

Mark2440 · Aug 26, 2019

Mea culpa, mea culpa, mea maxima culpa.
I apparently did something called cross-posting, 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.
A solution to this 255-character-max problem is revealed at