How to capture email subject lines in a column?

Question

I have Excel VBA code that is supposed to take the subject lines from my inbox in the Lotus Notes application and then copy it into a column in Excel.

It seems to populate the subject into a single cell and over-write it.

A second issue is, half way through the loop I get an error

Type:mismatch

Sub Subject_Info()

Dim v As Object
Dim vn As Object
Dim e As Object
Dim doc As Object
Dim rtitem As Variant
Dim nit As Object

View = "$All"

Set NSession = CreateObject("Notes.NotesSession")
Set NMailDb = NSession.GetDatabase("", "")
If Not NMailDb.IsOpen Then
    NMailDb.OPENMAIL
End If
    
Set v = NMailDb.GetView(View)
Set vn = v.CreateViewNav()

Set e = vn.GetFirstDocument()

Do While Not (e Is Nothing)
    Set doc = e.Document
    Set nit = doc.GetFirstItem("subject")
    Lines = Split(nit.Text, vbCrLf)
    Range("A:A").Resize(UBound(Lines) + 1, 1).Value = Application.WorksheetFunction.Transpose(Lines)
    Set e = vn.GetNextDocument(e)
Loop

End Sub

Answer 1

Background: in Notes there is no fixed format for all the items that are present in a Document. One document might contain an item "Subject" whereas another one does not... and still both documents are totally valid. This is normal in a document based database in contrast to a relational database.

In your case your code line Set nit = doc.GetFirstItem("subject") will return Nothing for a document that does not contain a subject item. Then the next line will fail as well and therefor you do not have a valid array in your "Lines" variable.

You could wrap your code in a If doc.HasItem("Subject") then clause. But that makes it unnecessarily complicated as there is already a method that fits better.

Simply replace

 Set nit = doc.GetFirstItem("subject")
 Lines = Split(nit.Text, vbCrLf)

with

 Lines = doc.GetItemValue("subject")

GetItemValue always returns an Array, independent of the existence of an item. If there is no item, then it returns an array with one empty string element.

That should solve your "Type:Mismatch" error.