Create Hyperlink in lotus notes email from excel

excelpinto

Board Regular
Joined
Feb 14, 2008
Messages
53
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>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Use the Hyperlinks property rather than the Value property.
e.g.
Code:
Sub Test()
  Debug.Print Range("A1").Value
  Debug.Print Range("A1").Hyperlinks.Item(1).Address
End Sub
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,215,336
Messages
6,124,334
Members
449,155
Latest member
ravioli44

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