Hey Everyone,
I use this macro to insert a very long hyper link
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
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
For some reason, when I use =sum to add up the body text, it adds up to over 4000 (which is clearly wrong)
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 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)