=Hyperlink("mailto:

Noddy13

New Member
Joined
Jan 25, 2010
Messages
45
Hi All,

I have a one off task where I need to send multiple individual emails to various email addresses. I have all the addresses in row 2 and the text in cells in column L.

This is what I am have come up with.

=HYPERLINK("mailto:"&B2&";"&C2&";"&D2&";"&E2&";"&F2&";"&G2&"?subject="&L2&"-"&A2&"&Body="&L2&"-"&A2&"%0A%0A"&L3&"",A2)

Where B2:G2 and email addresses, A2 is text and L2:L8 and also text. I get the #value error when I try to add any more to the formula.

TIA
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
The formula you have seems to work okay. Can you provide an example of what the formula looks like when you add more?
 
Upvote 0
There is a 255 character limit for =hyperlink. A VBA hyperlink can be added or macro straight to Outlook to handle it.
 
Upvote 0
=HYPERLINK("mailto:"&B2&";"&C2&";"&D2&";"&E2&";"&F2&";"&G2&"?subject="&L2&"-"&A2&"&Body="&L2&"-"&A2&"%0A%0A"&L3&"%0A%0A"&L4&"",A2)

If I try to add in an extra line of text in the email I end up with the #value error.

I am wondering if there is a limit to the amount of text it will allow me to add in.

Thanks for your help.
 
Upvote 0
Kenneth Hobson is correct. There is a limit to the character length of the hyperlink. The formula for the hyperlink can be shorter than the limit, but it doesn't matter if the length of the hyperlink is greater than the limit once it is formed based on the referenced cells. As soon as you add the text from L4, the length goes above the limit, and the error occurs.

Taking his suggestion to use a VBA hyperlink, I've created the code below that you could use. This allows you to create a hyperlink with a character length much longer that the formula's limit. However, I'm not sure how familiar you are with VBA, so you might need some help to implement. Let us know. You would need to save the workbook as a macro-enabled book (*.xlsm) and have Excel set up to allow the use of macros.

Put this code in the sheet code for the sheet with the hyperlink. This code will automatically update the hyperlink every time one of the cells identified in the "hyperlinkCells" variable is changed. The list includes all of the cells you used in the hyperlink formula. You might want to tweak the cell list in case there are others that you want to use.

Also, you can change the "cellWithTheHyperlink" variable to the cell address of the cell you want the hyperlink to show up in. You hadn't said before what cell it is.

The "Address" in the code is a conversion of the formula to VBA references. This is where you would modify the code to change the hyperlink address.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, hyperlinkCells As String, a As Variant, i As Integer
    Dim cellWithTheHyperlink As Range
    
    'Put the cells that make up the hyperlink references in the string variable here.
    'These will be the cells that, if they change, cause the hyperlink to refresh
    [COLOR=#0000ff][B]hyperlinkCells [/B][/COLOR]= "A2,B2,C2,D2,E2,F2,G2,L2,L3,L4"
    
    'Change this to be the cell where the hyperlink is to be
    Set [COLOR=#0000ff][B]cellWithTheHyperlink [/B][/COLOR]= Range("A5")
    
    a = Split(hyperlinkCells, ",")
    For i = 0 To UBound(a)
        Set r = Union(Target, Range(a(i)))
        If Not r Is Nothing Then
            ActiveSheet.Hyperlinks.Add _
                Anchor:=cellWithTheHyperlink, _
                [COLOR=#0000ff][B]Address[/B][/COLOR]:="mailto:" & Range("B2").Value & ";" & Range("C2").Value & ";" & Range("D2").Value & ";" & _
                    Range("E2").Value & ";" & Range("F2").Value & ";" & Range("G2").Value & _
                    "?subject=" & Range("L2").Value & "-" & Range("A2").Value & _
                    "&Body=" & Range("L2").Value & "-" & Range("A2").Value & "%0A%0A" & Range("L3").Value & Range("L4").Value, _
                TextToDisplay:=Range("B2").Value
            Exit For
        End If
    Next
End Sub

There are lots of ways to achieve the same result. This is just one way. If you are not familiar with VBA, hopefully it isn't too hard to understand and use.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top