VBA throws error 1004 when adding a Hyperlink

Question

I have a Macro that creates a Mailto Link with email addresses input in one cell, and the body of the email made up of 3 different cells, the mailto Strin in the code snippet. This link should then be placed into a certain cell.

With Sheets("Menu")
    .Hyperlinks.Add _
        anchor:=.Cells(row, 8), _
        Address:=mailTo, _
        TextToDisplay:="mailTo", _
        ScreenTip:="MailTo link"
End With

However I sometimes get an

error 1004

in the above code. Removing parts of the email body resolves the issue so I originally thought that it might be related to the length of the mailTo String. However this does not seem to be the case since I've gotten it to work with different length strings.

What other possible reasons are there that could result in the Hyperlinks.Add function failing?

What do the variables Row and mailTo contain? I believe as long as the variable Row is correct and the variable Mailto contains something like "mailto:[email protected]" or "mailto:[email protected]?subject=YourSubject" then this should work. — Xabier
– Xabier, Commented Dec 21, 2017 at 14:34
It seems there is a limit to how long a mailto can be after all, I was looking in the wrong place. — DriveThomas
– DriveThomas, Commented Dec 21, 2017 at 15:24

Vityata · Accepted Answer · 2017-12-21 14:47:39Z

2

There could be any of several reasons. For example, Row may be 0:

edited Dec 21, 2017 at 14:47

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

answered Dec 21, 2017 at 14:37

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cora Muirgen · Accepted Answer · 2017-12-21 14:53:14Z

2

There is a maximum worksheet hyperlink limit of 65536. You could be running up against this*.

If so, an alternative is to use Excel's HYPERLINK formala.

*(see https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?CorrelationId=6fdacf7e-c207-4932-a46d-b82e7fa0679b&ui=en-US&rs=en-US&ad=US&ocmsassetID=HP010342495)

answered Dec 21, 2017 at 14:53

Cora Muirgen

1294 bronze badges

Comments

DriveThomas · Accepted Answer · 2017-12-21 15:22:22Z

It seems that there is a limit to how long the hyperlink can be, 2012 characters as returned by the len function.

The reason I didn't originally discover this was because I was only looking at the mail text before converting to a mailto.

The conversion replaces new lines and spaces with %0A and %20 respectively. So "Hello World" is 11 characters long but in the Mailto it would look like this "Hello%20World" and have a length of 13. Since I was looking at the Text before the conversion and adding/removing spaces it seemed to me like mailtos of different length where working.

Thank you for everyone that offered suggestions even though they weren't what I was looking for. I hope that this can be of help some one else in the future.

Collectives™ on Stack Overflow

VBA throws error 1004 when adding a Hyperlink

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related