Solved

Lotus Notes / Access - Processing Attachments.

Posted on 2004-09-13
9
810 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 250 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 250 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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