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]

Did you test this on your system? Also when the email is sent what will be the the link the that will be shown , i am confused on the HTML part as you have few file paths there.

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.

 

[John_w]

Yes, I tested it and it works on my system.

There are 2 links. The text shown for the links are "test.csv" and "Excel folder".

Remember, as previously mentioned you have to remove the space in each HTML tag, otherwise the HTML will be improperly formed and the links probably won't show.

 

[Katy Jordan]

Yes, I tested it and it works on my system.

There are 2 links. The text shown for the links are "test.csv" and "Excel folder".

Remember, as previously mentioned you have to remove the space in each HTML tag, otherwise the HTML will be improperly formed and the links probably won't show.

How does the code no where to put the links in the text body? For example if i have my Email Text like this, how does the code know to put the link after my text?


"Hi

The the Rec report has been completed and saved down as per below file path, please click the link.

File path

Regards

Katy"

When you run the code will the link appear as one line to allow the user to click on it

 

[Katy Jordan]

Yes, I tested it and it works on my system.

There are 2 links. The text shown for the links are "test.csv" and "Excel folder".

Remember, as previously mentioned you have to remove the space in each HTML tag, otherwise the HTML will be improperly formed and the links probably won't show.

John did you manage to look at my issue above?

With email address rather than me physically typing them out in code i have a few email address in Sheets("Emailaddy") Col A, Row 6 onwards, how would i reflect this in code?

 

[John_w]

Have you run the code? If so, compare the email it generates with the code and it should be obvious how the email text body is generated and where the links are put. The only changes you need to make for the code to run and send an email are to change the SendTo address(es) (in the SendTo variable) and remove spaces from the HTML tags. Yes, the local file links (URLs) will probably be incorrect for your computer, but are still valid as a proof of concept.

If you are still unsure, the HTMLbody variable holds the text of the email body, with paragraph tags (< p>) for each text paragraph and anchor tags (< a>) for the links. This variable is then concatenated to the HTML variable between the body opening and closing tags (< body> and < /body>). Therefore you would need to change the HTMLbody string for your particular email text.

To use email addresses from a range of cells, for example A1:A3 on Sheet1, change:

change:

.SendTo = Split(SendTo, ",")

to:

.SendTo = Sheets("Sheet1").Range("A1:A3").Value

 

[Katy Jordan]

Ok i ran the code and i did not get any errors, which is good, but the body of my email text is on a excel spreadsheet, say for example sheet(3) ColA8:A33, how do i get the code to pick up this body text from excel?

Have you run the code? If so, compare the email it generates with the code and it should be obvious how the email text body is generated and where the links are put. The only changes you need to make for the code to run and send an email are to change the SendTo address(es) (in the SendTo variable) and remove spaces from the HTML tags. Yes, the local file links (URLs) will probably be incorrect for your computer, but are still valid as a proof of concept.

If you are still unsure, the HTMLbody variable holds the text of the email body, with paragraph tags (< p>) for each text paragraph and anchor tags (< a>) for the links. This variable is then concatenated to the HTML variable between the body opening and closing tags (< body> and < /body>). Therefore you would need to change the HTMLbody string for your particular email text.

To use email addresses from a range of cells, for example A1:A3 on Sheet1, change:

change:

.SendTo = Split(SendTo, ",")

to:

.SendTo = Sheets("Sheet1").Range("A1:A3").Value

 

[Katy Jordan]

John, i can get the code to work, but how do i get the body text from Excel as specified above? This is the trick part. I do not want to hard code Body text.

Have you run the code? If so, compare the email it generates with the code and it should be obvious how the email text body is generated and where the links are put. The only changes you need to make for the code to run and send an email are to change the SendTo address(es) (in the SendTo variable) and remove spaces from the HTML tags. Yes, the local file links (URLs) will probably be incorrect for your computer, but are still valid as a proof of concept.

If you are still unsure, the HTMLbody variable holds the text of the email body, with paragraph tags (< p>) for each text paragraph and anchor tags (< a>) for the links. This variable is then concatenated to the HTML variable between the body opening and closing tags (< body> and < /body>). Therefore you would need to change the HTMLbody string for your particular email text.

To use email addresses from a range of cells, for example A1:A3 on Sheet1, change:

change:

.SendTo = Split(SendTo, ",")

to:

.SendTo = Sheets("Sheet1").Range("A1:A3").Value

 

[Katy Jordan]

If anyone else that has lotus notes could you please advice how i would I get code to look at body text from Excel, i have managed to get it to work via hard coding, but i dont want to hard code body text.

 

[Katy Jordan]

*bump*

 

[John_w]

Similar to the way I showed you for the email addresses. For example, if the text is in cell A4 on Sheet1, change:

    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< />"

to:

    HTMLbody = "< p>" & Sheets("Sheet1").Range("A4").Text & "< /p>" & _
        "< a href='file:///f:\temp\excel\test.csv'>test.csv< />< br>" & _
        "< a href='file:///f:\temp\excel'>Excel folder< />"

Put each paragraph of text between a paragraph opening tag (< p>) and its closing tag (< /p>) in the HTML code - see http://www.w3schools.com/tags/tag_p.asp. The < br> tag is a line break - see http://www.w3schools.com/tags/tag_br.asp