• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • Last Modified:

Lotus Notes / Access - Processing Attachments.

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
adaytay
Asked:
adaytay
  • 3
  • 3
  • 3
2 Solutions
 
shanesuebsahakarnCommented:
Have you checked to see what nItem.VALUES(0) evaluates to (if anything)?
0
 
adaytayAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
stevbeCommented:
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
 
stevbeCommented:
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
 
shanesuebsahakarnCommented:
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
 
stevbeCommented:
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
 
adaytayAuthor Commented:
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
 
adaytayAuthor Commented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now