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>

 

[pgrad]

Hi All,

I am stuck on the same problem. I am creating targetted email program which is working fine apart from the lack of hyperlinks to web pages.

I have hyperlinks in cells BR3 and BS3 but when adding them to my code and sending the email the display as plain text.

Apologies if I'm being really dense but I can't seem to get this to work.

Here is an extract of the code:

'Eleventh variable
   If var011 = 1 Then
      EmailBody = EmailBody & vbCrLf & _
         Bullet & " " & "- You have no other contact id"
   
   End If
   
   vaMsg = "Dear " & _
   Range("A2").Text & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BQ1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   EmailBody & Chr$(10) & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BR1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BS1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BT1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BU1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BV1") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _
   Range("BW1") & Range("br3").Hyperlinks

   
   'Insert subject here
        stSubject = "Online Directory Listing"

Any advice would be fantastic!

Thanks
Paul

 

[pgrad]

Hi All,

The answer can be found here:

http://en.allexperts.com/q/Using-Lotus-Notes-1427/Lotus-Notes-creating-Hotspot.htm

Cheers
Paul