HYperlink problems

[TD Rich]

Hello,

I have a piece of code that inserts a hyperlink into the body of an email. The problem i am having is that when copying the link which is shown in full as follows:

<TABLE style="WIDTH: 191pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=255><COLGROUP><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 191pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=255>http://tdlontns217:19313/sites/Comp...nt Trading/PAT eastor22010-10-13T14_53_56.xml
</TD></TR></TBODY></TABLE>

it will only copy up to

http://tdlontns217:19313/sites/Compliance/PAT/Personal

as the link, the rest of the address is copied but does not form part of the hyperlink. It seems to be cutting the link off when it reaches the first '%20' part of the address. Can anybody help me out with this as it is driving me crazy!!!!

Regards,

TD Rich.


code in full

Sub emailnew()
Dim outlookapp As Object
Dim mitem As Object
Dim cell As Range
Dim subj As String
Dim emailaddr As String
Dim recipient As String
Dim msg As String
Dim findit As Range
Dim manager As String

Set outlookapp = CreateObject("outlook.application")
Set findit = Range("A3:A200")
For Each cell In findit
If cell.Value > 0 Then
subj = "Outstanding PAT Confirmation"
recipient = cell.Offset(0, 4).Value
emailaddr = cell.Offset(0, 5).Value
outstanding = cell.Offset(0, 1).Hyperlinks(1).Address
manager = cell.Offset(0, 12).Value
msg = recipient & vbCrLf & vbCrLf
msg = msg & "The following Personal Account Trade (PAT) has not been confirmed. " & vbCrLf & vbCrLf
msg = msg & outstanding & vbCrLf & vbCrLf
msg = msg & "Please advise Compliance Dept. if you have executed this trade" & vbCrLf & vbCrLf
msg = msg & "Regards" & vbCrLf & vbCrLf
msg = msg & "Compliance Dept"
Text = msg
Set mitem = outlookapp.createitem(0)
 
With mitem
.To = emailaddr
.Subject = subj
.body = msg
.cc = manager
.bcc = "dept@tdsecurities.com"""
.display
End With
End If
Next
End Sub

 

[Andrew Poulsom]

Try changing:

msg = msg & outstanding & vbCrLf & vbCrLf

to:

msg = msg & "<" & outstanding & ">" & vbCrLf & vbCrLf

 

[TD Rich]

That works!!!!! Thanks Andrew!

 

[Norie]

Does the hyperlink work?

I always thought you needed to ude HTMLBody for that.

 

[TD Rich]

yeah, you have to hold down ctrl and click but it does work

 

[Norie]

Well you learn something new every day.