=Hyperlink("mailto:

[Noddy13]

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

 

[shknbk2]

The formula you have seems to work okay. Can you provide an example of what the formula looks like when you add more?

 

[Kenneth Hobson]

There is a 255 character limit for =hyperlink. A VBA hyperlink can be added or macro straight to Outlook to handle it.

 

[Noddy13]

=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.

 

[shknbk2]

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.

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.