CODEWITHSUNDEEP
excelvbalotus-notes
user15343371
user15343371

Reputation: 41

How to keep rows which do not have a new line character from splitting, when importing the Body of a Lotus Notes e-mail into Excel using VBA?

I have an Excel VBA program that will find a Lotus Notes email with specific text in the subject and import the body into the Excel spreadsheet.

I can find the email, and import the data to Excel. Unfortunately, it seems to be creating a new line where there isn't a new line character.

I noticed that the breaks in the Excel sheet match up with the line wrapping in Word when I cut and paste the Body into a Word doc and the doc is in portrait mode.
If I change Word to landscape, it looks like the e-mail.
Changing the Excel sheet to landscape had no effect.

I also tried changing the split to use vbCR and vbLf independently of each other without positive results.

Is there a line length option I can set? How can I keep all the data associated with one line in the e-mail with one line in Excel?

Sub LNExtract()

Dim NSession As Object          'NotesSession
Dim NMailDb As Object           'NotesDatabase
Dim NDocs As Object             'NotesDocumentCollection
Dim NDoc As Object              'NotesDocument
Dim NNextDoc As Object          'NotesDocument
Dim NItem As Object             'NotesItem
Dim view As String
Dim filterText As String

view = "Folder\SubFolder"       'Name of view or folder to retrieve documents from
filterText = "SubjectLineText"     'Optional text string to filter the view

Set NSession = CreateObject("Notes.NotesSession")
Set NMailDb = NSession.GETDATABASE("", "")  'Default server and database
If Not NMailDb.IsOpen Then
    NMailDb.OPENMAIL
End If

Set NDocs = NMailDb.GETVIEW(view)
NDocs.Clear
    
'Apply optional filter
    
If filterText <> "" Then
    NDocs.FTSEARCH filterText, 0
End If
  
Set NDoc = NDocs.GETFIRSTDOCUMENT
Do Until NDoc Is Nothing
    Set NNextDoc = NDocs.GETNEXTDOCUMENT(NDoc)
    Set NItem = NDoc.GETFIRSTITEM("Body")
    If Not NItem Is Nothing Then
    Lines = Split(NItem.Text, vbCrLf)
    Range("A1").Resize(UBound(Lines) + 1, 1).Value = Application.WorksheetFunction.Transpose(Lines)
    End If
    Set NDoc = NNextDoc
Loop

End Sub

Upvotes: 2

Views: 135

Answers (1)

Emma Leis
Emma Leis

Reputation: 2900

Where you have NItem.Text in your code, I'm fairly sure that gives the same result as the GetFormattedText method of the NotesRichTextItem class, which limits the line length. Relevant documentation is here:
https://help.hcltechsw.com/dom_designer/9.0.1/appdev/H_GETFORMATTEDTEXT_METHOD.html

In contrast, the GetUnformattedText method doesn't limit the line length, so you should get a better result if you replace NItem.Text with NItem.GetUnformattedText().

Upvotes: 3

Related Questions