Place an Excel File Path as a Link in a Lotus Notes Email Body

Anthro84

New Member
Joined
Oct 29, 2013
Messages
2
I have an Excel sheet that I use as a mailing automatism for reports. As it currently is it attaches an actual copy of the excel workbook to the email and send them out. The mailer contains several different people, and they get different report each day. Due to the size of some of the files, I am starting to run into an issue where I cannot sent the emails anymore because they are too big, so I am wanting to switch to sending links to the files instead, and I have hit a wall.


I use Lotus Notes 8.5. The VBA will cycle through a range, and each cell has a list of report delimited by a ",". It takes the list and passes it to the mailer as a string. The mailer takes the string, turns it into an array and splits it out, and then checks to make sure the reports are current. One email could have up to 10 different reports in it. I have tried creating an HTML MIME email to include the links. Here is the code I currently have:


Code:
Sub Send_HTML_Email(ByRef Name As String, ByRef Address As String, ByRef Reports As String)
         
        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
        Dim Array1() As String
        Dim Links As String
        Dim gRange As Variant
        Dim i As Integer
         
        SendTo = "myEmail@address.com"
        subject = "My Subject " & Name & "."
        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
         
         Array1 = Split(Reports, ",")
        
        i = 1
        
        For Each gRange In Array1
        
        Select Case gRange
        
            Case "Report name 1"
            Reports = "G:\file Location\Report Name 1.xlsx"
            Case "Report name 2"
            Reports = "G:\file Location\Report Name 2.xlsx"
            Case "Report name 3"
            Reports = "G:\file Location\Report Name 3.xlsx"
            Case "Report name 4"
            Reports = "G:\file Location\Report Name 4.xlsx"
            Case "Report name 5"
            Reports = "G:\file Location\Report Name 5.xlsx"
            Case "Report name 6"
            Reports = "G:\file Location\Report Name 6.xlsx"
        End Select
        
            If Reports <> "" And Format(FileDateTime(Reports), "mm/dd/yyyy") = Format(Now, "mm/dd/yyyy") Then
            
            Select Case gRange
                
                Case "Report name 1"
                Links = "G:\file%20Location\Report%20Name%201.xlsx"
                Case "Report name 2"
                Links = "G:\file%20Location\Report%20Name%202.xlsx"
                Case "Report name 3"
                Links = "G:\file%20Location\Report%20Name%203.xlsx"
                Case "Report name 4"
                Links = "G:\file%20Location\Report%20Name%204.xlsx"
                Case "Report name 5"
                Links = "G:\file%20Location\Report%20Name%205.xlsx"
                
            End Select
                
                If Links <> "" Then
                HTMLbodyi = "<a href="file://" & Links & "></><br>"<br />** * * * * * * *End If<br />** * * * * * * *<br />** * * * * *HTML = "<html>" & vbLf & _<br />** * * * * *"<head>" & vbLf & _<br />** * * * * *"****** http-equiv=""Content-Type"" content=""text/html; charset=UTF-8"" />" & vbLf & _<br />** * * * * *"</head>" & vbLf & _<br />** * * * * *"<body>" & vbLf & _<br />** * * * * *"<p>" & gRange.Value & "</p>" & _<br />** * * * * *HTMLbodyi & _<br />** * * * * *"</body>" & vbLf & _<br />** * * * * *"</html>"<br />** * * * * *<br />** * * * * *i = i + 1<br />** * * * * * * *<br />** * * * * *End If<br />** * * *<br />** * * *Next gRange<br />** * * * <br />** * * * <br />** * * *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
I'm using the Case statement to switch the Report and Links set based on the array from the cell with the different report names in it. One persons cell may only have `Report name 1` and `Report name 3`, while the next person has all of them.


I really appreciate any help I can get!


The emails will send, but they are either blank, or they get to the first `HTMLbodyi` and only include the initial < a and the rest in blank.
 

Anthro84

New Member
Joined
Oct 29, 2013
Messages
2
I got it working. Good answer on Stack Overflow on this one. Here is the link if anyone runs into this.

html - Posting Links to Excel Documents in Lotus Notes - Stack Overflow<a href="file://" & Links & "></><br>"<br />** * * * * * * *End If<br />** * * * * * * *<br />** * * * * *HTML = "<html>" & vbLf & _<br />** * * * * *"<head>" & vbLf & _<br />** * * * * *"****** http-equiv=""Content-Type"" content=""text/html; charset=UTF-8"" />" & vbLf & _<br />** * * * * *"</head>" & vbLf & _<br />** * * * * *"<body>" & vbLf & _<br />** * * * * *"<p>" & gRange.Value & "</p>" & _<br />** * * * * *HTMLbodyi & _<br />** * * * * *"</body>" & vbLf & _<br />** * * * * *"</html>"<br />** * * * * *<br />** * * * * *i = i + 1<br />** * * * * * * *<br />** * * * * *End If<br />** * * *<br />** * * *Next gRange<br />** * * * <br />** * * * <br />** * * *NSession.ConvertMime = False " don't="" convert="" mime="" to="" rich="" text
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top