VBA Make link active in Lotus notes

[Katy Jordan]

Hi, does anyone know how i can make the following link active in Lotus Notes, the cell has a named range Link. I have a code that works, i.e sends email via VBA but i want to make the file path active so the user can click and open the folder.

C:\Test/2011

 

[Katy Jordan]

*bump*

 

[Katy Jordan]

Does anyone know why i cant get the hyperlink working for the below code. The code itself runs perfectly put when i open the email there is no hyperlink just text

Private Sub sendemail_to_database()
    Dim session As Object
    Dim db As Object
    Dim doc As Object
    Dim rtitem As Object
    Dim Obj As Object
    Dim messubject As String
    Dim bodytext As String

    On Error Resume Next
    Set session = CreateObject("Notes.NotesSession")
    Set db = session.GetDatabase("", "")
    Call db.OPENMAIL
    Set doc = db.CreateDocument
    doc.Form = "Memo"
    doc.SendTo = "bds_visualprogramer@yahoo.com"
    
    doc.Subject = "Test E-mail"
    doc.Body = "This is a test..." & vbCrLf & "Test...Test..."

    Screen.MousePointer = vbHourglass
        Set rtitem = doc.CreateRichTextItem("Body2")
        Set Obj = rtitem.EmbedObject(EMBED_OBJECTLINK, "", "C:\bds\Test.xls", "")
        Call doc.send(False)
        Set session = Nothing
    Screen.MousePointer = vbNormal
End sub

</pre>

 

[GlennUK]

You say "The code itself runs perfectly...", but you have On Error Resume Next set, so the code won't interrupt on errors. Take that out and see if it really runs perfectly, as a first step.

 

[Katy Jordan]

You say "The code itself runs perfectly...", but you have On Error Resume Next set, so the code won't interrupt on errors. Take that out and see if it really runs perfectly, as a first step.

Even if i take that out the path does not turn into a link just text, i have no issue in sending email i just want that path turned into a link. No one seems to know i have searched google, all i can find is peoples question i cant find one thread where someone has said that works.

 

[Katy Jordan]

*bump*

 

[Katy Jordan]

*bump*

 

[Katy Jordan]

*bump*

 

[GlennUK]

Not knowing Lotus Notes, I don't think I can help.

 

[John_w]

The easiest way is to create a HTML MIME email, like this:

Sub Send_HTML_Email()

    Const ENC_IDENTITY_8BIT = 1729

    'Send Lotus Notes email containing links to files on local computer
    
    Dim NSession As Object      'NotesSession
    Dim NDatabase As Object     'NotesDatabase
    Dim NStream As Object       'NotesStream
    Dim NDoc As Object          'NotesDocument
    Dim NMIMEBody As Object     'NotesMIMEEntity
    Dim SendTo As String
    Dim subject As String
    Dim HTML As String, HTMLbody As String
    
    SendTo = "your.email@email.com, another.email@email.com"
    subject = Now & " Lotus Notes HTML MIME email"
    Debug.Print subject
    
    Set NSession = CreateObject("Notes.NotesSession")       'using Lotus Notes Automation Classes (OLE)
    Set NDatabase = NSession.GetDatabase("", "")
    
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
    
    Set NStream = NSession.CreateStream
            
    HTMLbody = "< p>Links to files on local computer:< /p>" & _
        "< a href='file:///f:\temp\excel\test.csv'>test.csv< />< br>" & _
        "< a href='file:///f:\temp\excel'>Excel folder< />"
    
    HTML = "< html>" & vbLf & _
            "< head>" & vbLf & _
            "< meta http-equiv=""Content-Type"" content=""text/html; charset=UTF-8"" />" & vbLf & _
            "< /head>" & vbLf & _
            "< body>" & vbLf & _
            HTMLbody & _
            "< /body>" & vbLf & _
            "< /html>"
    
    NSession.ConvertMime = False     'Don't convert MIME to rich text
    
    Set NDoc = NDatabase.CreateDocument()
    
    With NDoc
        .Form = "Memo"
        .subject = subject
        .SendTo = Split(SendTo, ",")
        
        Set NMIMEBody = .CreateMIMEEntity
        NStream.WriteText HTML
        NMIMEBody.SetContentFromText NStream, "text/html; charset=UTF-8", ENC_IDENTITY_8BIT
    
        .Send False
        .Save True, False, False
    End With
    
    NSession.ConvertMime = True      'Restore conversion
       
    Set NDoc = Nothing
    Set NSession = Nothing
   
End Sub

NOTE: To prevent the forum rendering the HTML in the code, I've had to add a space in each opening and closing tag, for example < p> < />. You will have to remove these spaces in your code so that the HTML is constructed correctly.