Solved

Lotus Notes / Access - Processing Attachments.

Posted on 2004-09-13
9
804 Views
Last Modified: 2011-09-20
Hi folks,

I know that with Outlook you have the ability to handle attachments directly.  I'm looking to do this for my current project - however, we're using Notes 6.

What do I want?

I want to be able to run a VBA procedure from within Access that will check the contents of the current users' inbox for a message with a specific subject heading.  The attachment (an Excel worksheet) should then be detached to a predetermined location on the users machine, and then (still to be written), the information in this file will be imported directly into a database.

What do I have?

I've found some VB6 code on the net (http://the-big-o.port5.com/article66.html), and have adapted it as best I can to VBA.  However, it's not quite functioning as I needed.

The code I have is as follows:

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:\beaver.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
              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 ntViewEntry = ntNavigator.GETNEXTDOCUMENT(ntViewEntry)
    Loop
    ' Let me know everything is OK '
    'retval = Shell("cmd /c net send tom data file detached OK", vbHide)
   
EndSub:
    Exit Sub
   
   
ErrHandler:
    info = "A data file detach error has been detected.  Message is: " & Err.Description
    MsgBox info
    Resume EndSub

End Sub

--

The code fails on the line "pos = InStr(1, ntItem.VALUES(0), "Sample Subject")".  It's to do with ntItem.VALUES, I know that much.  But I don't know how to fix the issue!

The error message I get is: "Variable uses an Automation type not supported in Visual Basic"

Many thanks in advance,

Ad
0
Comment
Question by:adaytay
  • 3
  • 3
  • 3
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Have you checked to see what nItem.VALUES(0) evaluates to (if anything)?
0
 
LVL 2

Author Comment

by:adaytay
Comment Utility
I have, and it just returns the error "Object doesn't support this property or method".  I'm using Access 2000, in case that makes any difference.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Just to check that all the variables are correctly used, have you set the module to use Option Explicit (in case you've mistyped something, I don't think you have though)?
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
it may be BStr instead of String ... without seeing the lotus file spec I could not really say ...
perhaps pump it inot a variant and see if you can get it without barfing ...

var = ntItem.VALUES(0)

if this does not get you started then I think you may need some serious winAPI conversion code ... what was the original code in VB like for this?

Steve
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:stevbe
Comment Utility
hmmm ... maybe the original code had a typo ...

nItem(0)

have you tried looking at the lotus documentation?
does the email have anything in the subject line?

Steve
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
Comment Utility
I wouldn't have thought nItems was a collection, judging by the code - but I'm not really familiar with the Notes object model.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 250 total points
Comment Utility
I was thinking that if the "subject" had multiples of something then what would those somethings be ... the error sounds like .VALUES is not recognized so I was trying to end run it and grab a "default" whatever :-)
0
 
LVL 2

Author Comment

by:adaytay
Comment Utility
Ok....

shanesuebsahakarn:  Yeah I made sure I'd done that.  The code does compile, and it will run in up the line I specified.  Not knowing too much about the Notes model myself (especially Notes6, what we're using), I can't think where to go next.

Stevbe:  The original VB6 code was on the link I specified in my initial post.  I've tried doing the following at the Immed. Window - all through errors:

?ntItem.VALUES(0)
?ntItem.VALUES
?ntItem.VALUES(1)

I'm not at work at the moment so I'll sleep on it and see what I can come up with tomorrow.  Keep the suggestions coming in folks, this is great stuff!!

Ad
0
 
LVL 2

Author Comment

by:adaytay
Comment Utility
I've written a working solution that does just what I need, IE:

1)  It scans down the Inbox folder, and find the first email with a specific subject
2)  It will then detach the first email to a predetermined location
3)  The email is then copied to a specific notes folder, and removed from the Inbox.

The next part of the procedure is then to grab the file and import into the db, but that's straightforward.

Even though I've answered my own question (I have a habit of doing that!), I'll split the points between you if that's ok?  You were both very helpful!!

Here's the finished code, if you're interested.  It could probably be further refined but it works for me!

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

Ad
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now