Create Hyperlink in lotus notes email from excel

[excelpinto]

Hey all,

I have some vba code that will go through and generate emails for each row in a spreadsheet. One of the columns in the spreadsheet is a hyperlink but its just bringing over the "Friendly Name" in the notes email. I want it to bring over the clickable hyperlink. Any ideas?

<code>

Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = "mail\" & Mid$(UserName, 4, InStr(1, UserName, " ") - 4) & Mid$(UserName, _
InStr(1, UserName, " ") + 1, InStr(1, UserName, "/") - InStr(1, UserName, " ") - 1) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else: Maildb.OPENMAIL
End If
For r = 2 To Range("b1").End(xlDown).Row
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Email = Cells(r, 17)
'attachment = Cells(r, 4)
Subj = "ACTION NEEDED - Past Due Task: " & Cells(r, 2)
Msg = ""
Msg = Msg & "Line 1" & vbCrLf
Msg = Msg & "Line 2" & vbCrLf
Msg = Msg & Cells(r, 11) & vbCrLf <- this is where the Hyperlink is

MailDoc.SendTo = Email
MailDoc.Subject = Subj

MailDoc.Body = Msg
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
'Set up the embedded object and attachment and attach it
If attachment <> "" Then
Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = attachME.EmbedObject(1454, "", attachment, "Attachment")

End If
'Send the document
'MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
Call MailDoc.Send(False)
Next r
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing

</code>

 

[Kenneth Hobson]

Use the Hyperlinks property rather than the Value property.
e.g.

Sub Test()
  Debug.Print Range("A1").Value
  Debug.Print Range("A1").Hyperlinks.Item(1).Address
End Sub

 

[excelpinto]

Ok this sounds like a good idea but I keep getting subscript out of range errors even when I just try to run the code you gave me by itself. Any ideas?

 

[FundamentalDiscord]

That usually means there is no Hyperlink there.

 

[excelpinto]

There should be, heres whats in that cell:

=HYPERLINK("http://test.com","test")

 

[FundamentalDiscord]

That doesn't generate a Hyperlink in the way of :

Range("A1").Hyperlinks</pre>

 

[Kenneth Hobson]

In that case:

Split(Range("A1").Formula, """")(1)

 

[excelpinto]

In that case:

Split(Range("A1").Formula, """")(1)

This works awesome. Thanks! I can extract the url from that hyperlink. Any idea on how to make it a hotspot url in notes though. It still displays as just text.

 

[FundamentalDiscord]

Does it not automatically convert the link when the email is sent? Otherwise, you'll have to create as a rich text item I think. I don't remember how to do that though... It has been a long time since I have done it...

 

[Kenneth Hobson]

As FundamentalDiscord said, most email programs make it into a hyperlink when it is received. My Lotus Notes works that way. I always send one to myself when I am doing a draft to verify that file: links work properly.