?
Solved

Lotus Notes / Access - Processing Attachments.

Posted on 2004-09-13
9
Medium Priority
?
815 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 41

Expert Comment

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

Author Comment

by:adaytay
ID: 12045229
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
ID: 12045629
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Expert Comment

by:stevbe
ID: 12045646
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12045745
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 1000 total points
ID: 12045759
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 1000 total points
ID: 12045793
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
ID: 12047831
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
ID: 12053050
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

770 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