Lotus Notes Search in VBA Excel

Hi, I looking for a way to return all Notes emails created in the last 8 days in VBA Excel.  Tried the code below but getting an error on the search string.  Any ideas?  Thanks!  Hammer8
Set sess = CreateObject("Notes.NotesSession")
    Set WS = CreateObject("Notes.NotesUiWorkspace")
    Set db = sess.GetDatabase(GetLotusNotesServerName, GetLotusNotesDBFileName)
  
    If db.IsOpen = False Then Call db.OPENMAIL
  
    'Set folder = db.GetView(FOLDER_NAME)
    Set folder = db.Search("@Created >= @Today - 8", , 0)

Open in new window

Hammer8Asked:
Who is Participating?
 
Bill-HansonCommented:
I'm getting ready to leave the office for a while, so I'll just post what I have.

Basically, you were just missing the @Adjust function to do the math on @Today.  You can't add/subtract days using standard operators.

This example will find all documents delivered to the current user's mail file within the last 8 days.
  ' open current user's email file
  Dim sess As Object, email As Object, dc As Object
  Set sess = CreateObject("Notes.NotesSession")
  Set email = sess.GetDatabase("", "")
  email.OpenMail
  
  ' search for email delivered within the specified time frame
  Set dc = email.Search("DeliveredDate > @Adjust(@Today; 0; 0; -8; 0; 0; 0)", Nothing, 0)
  MsgBox "found " & dc.Count & " memos"

Open in new window

0
 
mbonaciCommented:
First of all, you can't create folder like that.
Search method returns NotesDocumentCollection:

Set NotesDocumentCollection = notesDatabase.Search( formula$, notesDateTime, maxDocs% )


To create dateTime argument:
Dim dateTime As New NotesDateTime( Today )
Call dateTime.AdjustDay( -8 )


If your mail db is full text indexed, it's better to use FTSearch, so check it like this:

If db.IsFTIndexed Then
  Set collection = db.FTSearch( "press", maxDocs )
Else
  Set collection = db.Search ( "@Contains( Body; ""press"" )", dateTime, maxDocs )
End If


For FTSearch syntax look here:
http://www-12.lotus.com/ldd/doc/domino_notes/6.5.1/help65_designer.nsf/f4b82fbb75e942a6852566ac0037f284/3e1cfd391530284e85256e00004a7310?OpenDocument

Where are your variables GetLotusNotesServerName and GetLotusNotesDBFileName defined and initialized?
0
 
Bill-HansonCommented:
Sure, no problem.  Just a couple of questions first...

Are you sure that you want all emails that were CREATED withing the last 8 days or all emails that were DELIVERED withing the last 8 days?  Using created would return drafts as well as delivered email.

Also, just to confirm, you want only emails, no calendar entries, tasks, etc.  I only ask because the search formula above would return all document types.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Bill-HansonCommented:
Also, mbonaci is right about folders.  You can't search for a document contained in a specific folder.  If you need to find documents inside a folder, you have to iterate over each document in the folder and evaluate the field values individually.
0
 
mbonaciCommented:
All that is already posted :)
0
 
Bill-HansonCommented:
mbonaci,

>> "All that is already posted"

You're probably right, but why would you care when it answers the question?  The main point of my post is that subtracting 8 from @Today is wrong.  I don't see that in your post or the article that you linked to.  Also, answering a question about VBA with information about LotusScript is confusing and misleading.  But hey, if you're here just for the points, and you feel that you have a better chance by trashing my posts, then by all means, go right ahead.  I could care less about the points.
0
 
mbonaciCommented:
I didn't mean to trash anything, no need for frantic posts...
0
 
Bill-HansonCommented:
mbonaci,

If this type of feedback makes you uncomfortable, then I would suggest refraining from posting unhelpful entries.  If you think I am just re-posting redundant info, then please justify your comments with some details. I thought my VBA code and explanation was right on the point.
0
 
Hammer8Author Commented:
Thank you once again!  Is there a way for me to filter the results so that only EMAILS are returned (and not for example meeting requests)?  Thanks, Hammer8
0
 
mbonaciCommented:
Yes, you've taken my solution and made it more comprehensive to Hammer, I'll give you that much.
No need to insult one another, that's why I placed smiley at the end of the first sentence.

Now read your post once more, there's no mention of previous comments you're, in fact, referring to.
The polite thing would be to say something like "as mbonaci suggested...", or similar, like you stated (in your previous post) that thing about folder problem when you noticed you missed that.

Now cool off.
0
 
Bill-HansonCommented:
mbonaci,

I'm getting tired of this.  To be honest, I never got the impression that you were suggesting that the problem was with the date adjustment in your first post, so I thought I was posting new info.  When you said "All that is already posted" I thought you meant "already posted elsewhere on this site".  That is why I asked that you justify your comment with details.

Also, I was never very mad, just commenting on a pet peeve of mine (no need to cool off).  Like anyone else contributing to this site, I don't like being told that my post is worthless by another expert -- let the person asking the question decide that.  And, by the way, placing a smiley face after an insult does not change its meaning.  I think that our 'chat' would have been avoided had you posted more than just "All that is already posted".

Anyway, like I said above, I'm tired of this discussion, so this will be my last response.
0
 
Bill-HansonCommented:
Hammer8,

Well, you're probably rolling around on the floor by now.  Nothing like a heated debate on a Monday, right?

Anyway, to answer your question:

>> "Is there a way for me to filter the results so that only EMAILS are returned (and not for example meeting requests)?"

Sure, emails are stored using one of two forms, Memo and Reply.  In addition, email can be sent using an embedded form.  To catch all three cases, we need to check the Form item for all three possibilities.  Here's an example...

    Set dc = email.Search("DeliveredDate > @Adjust(@Today; 0; 0; -8; 0; 0; 0) & @IsMember(Form; 'Memo':'Reply':'')", Nothing, 0)

Note that this will still skip certain messages like delivery failures since they are stored using a different form.

Tip: When writing Search formulas, it helps to create a temp view in your database and play with the SELECT statement until you get exactly the documents you are looking for.  Then, just copy the SELECT formula and paste it into your call to db.Search.
0
 
Hammer8Author Commented:
Bill-Hanson, thank you so much for your help.  I will try this tomorrow.  Your help integrating Notes and VBA have been invaluable and have made my transition to Notes that much more bearable.  Very much appreciated!  Hammer8
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.