When I insert very long hyperlink

EvonS

Board Regular
Joined
Jun 1, 2016
Messages
111
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hey Everyone,

I use this macro to insert a very long hyper link


VBA Code:
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

VBA Code:
 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 TextBody TextBody TextBody TextEmail 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)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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