asked on

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

mbonaci

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?

Bill-Hanson

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.

Bill-Hanson

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.

ASKER CERTIFIED SOLUTION

Bill-Hanson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbonaci

All that is already posted :)

Bill-Hanson

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.

mbonaci

I didn't mean to trash anything, no need for frantic posts...

Bill-Hanson

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.

Hammer8

ASKER

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

mbonaci

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.

Bill-Hanson

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.

Bill-Hanson

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.

Hammer8

ASKER

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