Check a notes mailbox for new mail

[Adaytay]

Hi folks,

We use Notes as our intranet here, and I really wish we used Outlook. Why?

I need to be able to determine if an email has been received with a specific header. Via Access.

We have a db at one of our remote sites that the users there log daily delivery shortages onto. At the end of each day, this information is then emailed to us (via Notes), when the user clicks on an option on their menu.

What I would like to happen is for this data, once received in a mailbox with a specific subject line, to be automatically detected and the information contained therein imported into one of our other dbs. This would ideally be triggered automatically, however I am not averse to manually initiating the procedure via an option on the menu.

The ideal solution of course would be to use a SQL server or something to have a true multi-site DB, as I believe this would eliminate the need to send any data! Unfortunately the makeup of our network prevents from having this - the different sites on the WAN cannot see each other!

Does anyone have any suggestions? I can kind of visualise how I want to have this set up, but am clueless on how I would go about checking the contents of a mailbox.

Any thoughts would be appreciated,

Ad

[XL-Dennis]

Hi Ad,

First of all, working with Notes gives us a hard time no doubt about that.

Here You find some examples (by me )when it comes to work with Notes and particual the example where LotusSQL is in use:
http://www.excelkb.com/default.aspx?...&pNodes=0U7Q0Q

Although I haven?t tried it myself I believe it should be workable to setup a connection to the e-maildatabase (nsf) and use a SQL-statement that include a date (today) and the subjectline. It will require that You locate the fields that nsf-file has so You can setup the SQL-statement correctly.

Please let me know the progress as I have an interest when it comes to work with Notes and Excel (although it gives me some grey hair).

Kind regards,
Dennis

[Adaytay]

Hiya Dennis,

I did look into the use of NotesSQL but decided that (a) as a third-party (well, IBM) app needed to be installed, and (b) our network policiy will not allow for any additional software to be installed, I decided to go down an object / COM based method.

I've actually found some code that works in VB6 that does half of what I want it to do. Apart from the fact that it doesn't quite work in VBA.

The code I've got opens a new connection to Notes (we're using 6), opens the mailfile, and then scans the contents of the Inbox (although it could do any folder). It does this by searching for a specific string in the subject line, and if it finds it, the system *should* then open the mail, detach the first (or all) attachments to a predetermined location, then move the mail itself to a different folder - meaning that there'll only ever be one entry in the Inbox - which is what I want.

I've put a post on EE as well as a few other forums, to see what kind of response I get. Once I've come up with a complete working solution I will post it here to share

Thanks for responding though - definitely the direction I would have gone were it not for the restricitive policies of my employer.

Ad

[XL-Dennis]

Hi Ad,

OK, policy exist for good reasons

I've actually found some code that works in VB6 that does half of what I want it to do. Apart from the fact that it doesn't quite work in VBA.

Do You mind to share it at present?

TIA,
Dennis

[Adaytay]

Here's the process as it stands now. It fails on the Instr() line (with ntItem.VALUES(0))

[vba]
Public Sub DetachEmail()

Dim ntSession As Object, ntDatabase As Object, ntView As Object
Dim ntNavigator As Object, ntViewEntry As Object, ntDocument As Object
Dim rtitem As Variant, pos As Variant
Dim ntItem As Object
On Error GoTo ErrHandler

Set ntSession = CreateObject("Notes.NotesSession")

'Enter the notes password here...
'ntSession.Initialize '("password")
Set ntDatabase = ntSession.GETDATABASE("", "maildb.nsf")
If Not ntDatabase.ISOPEN Then ntDatabase.OPENMAIL

Set ntView = ntDatabase.GETVIEW("($Inbox)")
Set ntNavigator = ntView.CREATEVIEWNAV()
Set ntViewEntry = ntNavigator.GETFIRSTDOCUMENT()

Do While Not (ntViewEntry Is Nothing)
' for each document in my inbox
Set ntDocument = ntViewEntry.Document
Set ntItem = ntDocument.GETFIRSTITEM("subject")
' Only procees the email if it contain the
' particular subject line we're interested in '
pos = InStr(1, ntItem.VALUES(0), "Sample Subject")
'###################################
'#The code fails on the above line when trying to determine ntItem.VALUES(0)
'###################################

If pos = 1 Then
Filename = "H:\mypicture.jpg"
Set rtitem = ntDocument.GETFIRSTITEM("$file")
For Each Z In rtitem.VALUES
Set Object = ntDocument.GETATTACHMENT(Z)
Object.EXTRACTFILE (Filename)

' In my case I know that the email will only ever contain
' one attachment so I can exit after one pass
' through the loop but you could extract multiple
' attachments if your email contained them '
Exit For
Next
' Copy the processed email to another folder so
' we don't accidentally process it again
ntDocument.PUTINFOLDER "Personal", True
' Now we can safely remove it from our inbox
ntDocument.REMOVEFROMFOLDER "($Inbox)"
' I'm only interested in the first document with
' the relevant subject line so I can exit
' after processing it
Exit Do
End If
Set ntViewEntry = ntNavigator.GETNEXTDOCUMENT(ntViewEntry)
Loop

EndSub:
Exit Sub


ErrHandler:
info = "A data file detach error has been detected. Message is: " & Err.Description
MsgBox info
Resume EndSub

End Sub
[/vba]

Ad

[jamescol]

Have you thought about reversing the order of things? Since the email comes into the user's Notes mail DB, it would be simple to write a LotusScript macro that the proper incoming email would trigger. Notes could then dump the info into your Access DB.

Also, instead of relying on a user's mail DB, you could even setup a workflow-type DB just for this purpose. I understand that may not work for several reasons, but I thought I'd mention it.

Cheers,
James

[Adaytay]

Hi James,

I did consider that option. However, it would require a lot of work because:

1) The facility could be run by one of six staff members, each with their own email.
2) The email will always be sent two people. Policy here does not allow for "mail-in-dbs" that are not assigned to an individual, as is the case here.
3) We're not allowed to create scripts. And for the purpose of this db, it doesn't warrant passing a request through to the Notes team.

Ad

[XL-Dennis]

Hi Ad,

Thanks for the code

Recently I visit an old client that nowadays use Domino/Notes where they use lot of agents to control the e-mailflow. Very impressive but from my point of view it require a policy that support it which obviously is not the case here.

Except for that I hate LotusScript...

Kind regards,
Dennis

[Adaytay]

Would you beleive I've managed to write a process that actually works flawlessly. It's taken from the original code but I've tweaked it substantially to get it to work properly.

[vba]
Public Sub DetachFirstfromNotes()

'Procedure Name - DetachFirstfromNotes
'Procedure purpose - to detach the first attachment from an email with a given subject (strSubject)
'
'Written by Adam Taylor (adaytay) - September 2004. Original VB6 code from http://the-big-o.port5.com/article66.html.
'
'For more information email admin@adaytay.com

Dim ntSession As Object, db As Object, dc As Object, doc As Object
Dim counter As Long, rtitem As Object

Set ntSession = CreateObject("Notes.NotesSession")

Set db = ntSession.GETDATABASE("NUN_NO_01", "adaytay.nsf") 'Server and maildb name...

If Not db.ISOPEN Then
db.OPENMAIL
End If

Set dc = db.GETVIEW("($Inbox)")
Set doc = dc.GETFIRSTDOCUMENT

strSubject = "MiSTAutoMail - Daily Report" 'Enter the subject line to search for here.

Do While Not (doc Is Nothing)

If InStr(1, doc.GETITEMVALUE("Subject")(0), strSubject) > 0 Then 'Check to see if subject line is in the current item...
'It is. Process the item

strFilename = "H:\somepicture.jpg" 'Full path and filename that you want to save the attachment as
Set rtitem = doc.GETFIRSTITEM("$file")

For Each Z In rtitem.VALUES
Set ntObject = doc.GETATTACHMENT(Z)
ntObject.EXTRACTFILE (strFilename)
Exit For
Next

' Copy the processed email to another folder so
' we don't accidentally process it again
doc.PUTINFOLDER "Personal", True

' Now we can safely remove it from our inbox
doc.REMOVEFROMFOLDER "($Inbox)"

' I'm only interested in the first document with
' the relevant subject line so I can exit
' after processing it
Exit Do

End If
Set doc = dc.GETNEXTDOCUMENT(doc)

Loop

End Sub
[/vba]

Any comments on how it could be improved?

Ad

[XL-Dennis]

Ad,

Looks good to me although I prefer to use a for each for looping through the collection of e-mails. But this will not have any major impact of performance.

You may also add code to release the objects in use from memory in the end:
[vba]
Set rtITem = Nothing
Set Doc = Nothing
'......
[/VBA]

Thanks for sharing it!

Kind regards,
Dennis

[jamescol]

Ahh! Those pesky corporate policies always get in the way

Hi James,

I did consider that option. However, it would require a lot of work because:

1) The facility could be run by one of six staff members, each with their own email.
2) The email will always be sent two people. Policy here does not allow for "mail-in-dbs" that are not assigned to an individual, as is the case here.
3) We're not allowed to create scripts. And for the purpose of this db, it doesn't warrant passing a request through to the Notes team.

Ad

[systemcrash]

Awesome job Adatay...

but can we do anything to autoname the file instead of

"strFilename = "H:\somepicture.jpg" 'Full path and filename that you want "

Can is there a way that we can retrieve directly the attached file name ?

[yulsa]

Repair Outlook Express and Windows Address Book tools. - Repair and recovery Outlook Express folders and emails. Undelete Outlook Express messages. Recovering and repairing Windows Address Book.
and
Easy Outlook Express repair tools. - Repairing a damaged or corrupted Outlook Express folders ands messages.

[mohitstellar]

Here you can try Stellar Outlook Express Repair software which is an excellent software to repair & restore corrupt Outlook Express DBX files effectively. Outlook Express Repair tool successfully repair corrupt or damaged dbx files and recover objects like email, contacts, notes, tasks and journals.