When I insert very long hyperlink

[EvonS]

Hey Everyone,

I use this macro to insert a very long hyper link

Sub insertVeryLongHyperlinkv3()
  Dim curCell As Range
  Dim longHyperlink As String
  Dim x As Long
  Dim situation As Variant
  Dim emails As Variant
  Dim EmailBody As String

  x = 2
    
  Do
    situation = Cells(x, 1)
    emails = Cells(x, "G")  'Cells(x, 7)
    EmailBody = "&body=Please use this email thread to communicate situation updates and next steps." & _
      "%0A%0A" & "Date: " & Cells(x, 2) & _
      "%0A%0A" & "Originating Agency: " & Cells(x, 3) & "%0A%0A" & "Lead Agency: " & Cells(x, 4) & _
      "%0A%0A" & "Assisting Agencies: " & Cells(x, 5) & "%0A%0A" & "Situation Information: " & Cells(x, 6)
    Set curCell = Range("H" & x) ' or use any cell-reference
    longHyperlink = "mailto:" & emails & [H1] & "?subject=" & situation & " Thread" & EmailBody ' Or a Cell reference like [C1]
  
    curCell.Hyperlinks.Add Anchor:=curCell, _
      Address:=longHyperlink, _
      SubAddress:="", _
      ScreenTip:=" - Click here to create email thread", _
      TextToDisplay:="Create " & situation & " Email Thread"
  
    x = x + 1
  Loop Until Cells(x, 7) = 0
End Sub

It works for the most part but every so often I get an 'application runtime error 1001'. When I click on debug, this part of the code gets highlighted

 curCell.Hyperlinks.Add Anchor:=curCell, _
      Address:=longHyperlink, _
      SubAddress:="", _
      ScreenTip:=" - Click here to create email thread", _
      TextToDisplay:="Create " & situation & " Email Thread"

I can get it working when I delete some data from the email body so I suspect it is a character count issue but I'm not sure.
Here are the character counts from the cells

​ ​ Body Text	Body Text	Body Text	Body Text	Body Text	Email addresses
11​	5​	61​	17​	395​	614​
11​	5​	42​	17​	435​	741​
11​	5​	51​	51​	211​	451 (CODE STOPS HERE)​
11​	5​	36​	28​	38​	325​
11​	5​	36​	8​	60​	279​
11​	5​	36​	72​	84​	352​
11​	5​	36​	44​	207​	345​
9​	5​	31​	31​	138​	431​
9​	5​	36​	35​	221​	575​
9​	5​	61​	35​	283​	660​

For some reason, when I use =sum to add up the body text, it adds up to over 4000 (which is clearly wrong)

 

[BobUmlas]

Can you use TinyUrl.com to get a shorter version of the link?