[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Opening an email in Lotus Notes and saving the attachment

Posted on 2007-08-06
20
Medium Priority
?
6,420 Views
Last Modified: 2013-12-18
O.k.

So I don't know if this is actullly possible but I generate a file format via SSH which gets emailed to my inbox.  From there I need to save the file to a specific location on my PC then run a macro on the saved file.

My question is, is it possible to open a particular email in lotus notes and then save the attachment to my required location using VBA?

I'm guessing you have to do this by calling the UI classes, but I'm very new to using automation with lotus so I'm not that proficient with the objects yet.

I know how to initialise a notes session both back end and using the UI class but not to the point in looking in indiidual folders (inbox)  etc

A point in the right direction would be great
0
Comment
Question by:ngatirauks
  • 8
  • 7
  • 4
  • +1
20 Comments
 
LVL 14

Expert Comment

by:cezarF
ID: 19642927
You can use the EmbeddedObjects property of the NotesDocument or NotesRichTextItem classes and call the Extract method for each EmbeddedObject. Sample programs can be found in the domino designer help.
0
 

Author Comment

by:ngatirauks
ID: 19643271
O.k. please excuse my ignorance as i've only been playing around with COM in notes for about 2 weeks but where do I find domino designer help?  I'm using Excel to write my VBA?

This is what i've come up with so far from looking through Lotus' Object Browser.  I'm uncertain how to set the NotesDocument Object correctly as the way I'm doing it below doesn't look right? and my system won't allow early biniding.  I can't find any reference to the extract method you mentioned are you able to supply an example of this code or a link to an example?

Cheers

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim NotesAttachment     As Object


'Initialise Session
Set NotesSession = CreateObject("Notes.notesSession")
Set NotesDatabase = NotesSession.GETDATABASE("", "")

Call NotesDatabase.OPENMAIL

' Find the email
'I'm not so sure about this next line of code for creating the Notes Document Object??
Set NotesDocument = NotesDatabase.CREATEDOCUMENT

'Search email for an attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
    If (RichTextItem.Type = RICHTEXT) Then
    ' check for attachment in Body item
    Set NotesAttachment = RichTextItem.GETEMBEDDEDOBJECT("EmailedFile")
        If (NotesAttachment Is Nothing) Then
        ' check for attachment in rest of document
        Set NotesAttachment = NotesDocument.GETATTACHMENT("EmailedFile")
        End If
    End If


Set NotesDocument = View.GETFIRSTDOCUMENT

End Sub
0
 
LVL 14

Expert Comment

by:cezarF
ID: 19643308
domino designer should be in your local "data\Help\" folder. look for  help<LN version>_designer.nsf.

you can also access from online help: http://www-12.lotus.com/ldd/doc/domino_notes/7.0/help7_designer.nsf/Main?OpenFrameSet

open the link below and click the Example link. scroll down to example 8 to see how to access notes document.  http://www-12.lotus.com/ldd/doc/domino_notes/7.0/help7_designer.nsf/855dc7fcfd5fec9a85256b870069c0ab/d18432a5a923de118525704a00408110?OpenDocument&Highlight=0,COM


0
Independent Software Vendors: 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!

 
LVL 31

Expert Comment

by:qwaletee
ID: 19650546
If you don't have Domino Designer installed, you probably don't have the help files.  Your admin may have placed them on your Domino server in the help folder, accessible via the Notes Client's File->Database->Open dialog box.

You can also access Designer Help at http://www-128.ibm.com/developerworks/lotus/documentation/dominodesigner/

You would use Set NotesDocument = NotesDatabase.CREATEDOCUMENT to create a new message or calendar entry, etc. Instead, you need to first find the document in the database.  This can be done in any of the following ways:

NotesDatabase.Search
NotesDatabase.FTSearch
NotesDatabase.AllDocuments (use getFirstDocument and getNextDocument menthods)
NotesDatabase.GetView (ditto on methods for t he returned NotesView object; NotesView represents folders and views; you can also use FTSearch against a folder or view)

There are other ways as well, but that will do.  Notes's basic container hierarchy is database - view/folder - document - item - embedded object. Alternate hierarchies replace view/folder with the AllDocuments collection, or with a search/ftsearch result, and folders and views can also be FTSearched. In all cases, you end up with a collection of documents which you must loop through using getFirstDocument and getNextDocument.

0
 

Author Comment

by:ngatirauks
ID: 19651010
Cool thanks guys

This is what i've come up with so far, not much I know, but still getting my head round the hierachies etc.

Couple questions, when etracting the file, does the directory have to exist? or can I just specify the dir name when calling the extract?
Secondly I'm getting "object variable or with block variable not set" error when setting the notesdocument object, any ideas why this is?

Cheers


Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim NotesItem           As Object

'Initialise Session
Set NotesSession = CreateObject("Notes.notesSession")
Set NotesDatabase = NotesSession.GETDATABASE("", "")

Call NotesDatabase.OPENMAIL

Set NotesView = NotesDatabase.GETVIEW("Inbox")
'getting error object variable or with block variable not set???
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Set value of doc??
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
Call RichTextItem.EXTRACTFILE("Path")


End Sub
0
 
LVL 14

Expert Comment

by:cezarF
ID: 19651341
try this

Option Explicit

Sub OpenEmail()

      Dim ses as NotesSession
      Dim dir As NotesDbDirectory
      Dim db as NotesDatabase
      Dim view as NotesView
      Dim doc as NotesDocument
      Dim RTitem as NotesRichTextItem

      'Initialise Session
      'Set NotesSession = CreateObject("Notes.notesSession")
      'Set NotesDatabase = NotesSession.GETDATABASE("", "")
      'Call NotesDatabase.OPENMAIL 'cannot be used in COM

      'Set NotesView = NotesDatabase.GETVIEW("Inbox")
      'getting error object variable or with block variable not set???
      'Set NotesDocument = NotesView.GETLASTDOCUMENT

      'Set value of doc??
      'Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
      'Call RichTextItem.EXTRACTFILE("Path")


      Call ses.Initialize
      Set dir = s.GetDbDirectory("")
      Set db = dir.OpenMailDatabase
      Set view = db.GetView("Inbox")
      Set doc = view.GetLastDocument
      Set RTitem = doc.GetFirstItem("Body")
      Forall o In RTitem.EmbeddedObjects
            Call o.EXTRACTFILE("c:\" & o.name)
      End Forall

End Sub
0
 

Author Comment

by:ngatirauks
ID: 19659523
Hi

Tried using your supplied code

Call ses.Initialize
      Set dir = s.GetDbDirectory("")
      Set db = dir.OpenMailDatabase
      Set view = db.GetView("Inbox")

But when set view is executed I get "object variable or with block variable not set" error

If I use this code

Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim EmbeddedObject      As Object


'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("Inbox")

'Get the sent email
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Get the attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
     
      Call EmbeddedObject.EXTRACTFILE("Path")
     
     

End Sub

I get to Set Document but then I get the "object variable or with block variable not set" error

Any Ideas?
0
 
LVL 14

Expert Comment

by:cezarF
ID: 19667053
@ngatirauks: try

Set view = db.GetView("($Inbox)") instead of
Set view = db.GetView("Inbox")
0
 

Author Comment

by:ngatirauks
ID: 19667436
Cool that works!!  Can you explain to me the syntax behind $?

So I can get through the code all the way to the extractfile command but it falls over here, now I'm guessing it's because I've set the EmbeddedObject (object) incorrectly?  But can you extract a file to a directory that doesn't exist?  As this may be the problem also??

Another question I have is, I'm using the EMBEDDEDOBJECT property to find the emails attachment which is fine and WORKS!  But it's not suitable as the name of the attachment is dynamic depending on which user creates the email in the first place.  Is there a property that will get around having to specify the attachement name as I can't seem to see any in designers help??

Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim EmbeddedObject      As Object


'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("($Inbox)")

'Get the sent email
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Get the attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
Set EmbeddedObject = RichTextItem.GETEMBEDDEDOBJECT("M130107")
     
      Call EmbeddedObject.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\") '& Fraud File.xls")
     
     
End Sub

0
 
LVL 14

Expert Comment

by:cezarF
ID: 19667490
1. in designing views/folders in domino, enclosing the name in parentheses "()" means hiding it from the folder pane.

2. I haven't tried extracting to a folder that does not exist.It is best to check if the folder  exist before extracting. Use the DIR$ function to check.

3. try...
Call EmbeddedObject.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\" & EmbeddedObject.name)

or..

  Forall o In RichTextItem.EmbeddedObjects
            Call o.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\" & o.name)
  End Forall

 
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 400 total points
ID: 19673607
($Inbox) is the name of a "special" folder that Notes recognizes shold receive all new messages by default.  It displays in the user interface as just being Inbox, not ($Inbox)

Your attachment is sure named funny -- no extension?  The way to get a file by name is noy NotesRichTextItem.GetEmbeddedObject(fileName$) bur instead is NotesDocument.GetAttachment(filename$)

If you don't know the file and just want to get the first attachment, then use the following code:

Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
If IsArray(RichTextItem .Embeddedobjects) Then
   Set EmbeddedObject = RichTextItem.EmbeddedObjects(0)
   Call EmbeddedObject.ExtractFile(....)
End If
 

Note has had problems in the past with extarcing to UNCs.  You shoudl map a drive instead.  I don't recall if it errors if the folder does not exist.  WHy don't you try a simple version locally on your workstation, and use C:\ as the path?
0
 

Author Comment

by:ngatirauks
ID: 19697183
O.k. Cracked it thanks heaps guys!!!  Lotus didn't seem to have any trouble extracting to UNC but it did error if the Dir had not been created bummer.

But, haha always a but, I've realised I have gone off on completely the wrong tangent!!!

Basically there are 3 different users that will produce the particular email with the html attachment (sorry qwaletee, should of mentioned that lol) and the attachment is named depending on which user creates it.  When I try and use the wildcard as a precursor to .html it doesn't work, can this "*.html" wildcard be used?

Also the users inboxes are not categorised the same i.e. emails are viewed with latest date at the bottom for one but at the top for the other.  So I will obviously need to use the FT search function.  This is where I get stuck??  I don't know how to use this function just to search for an html attachment, or how to do it depending on the last email received regardless of how a users inbox is catergorised/sorted?

This is the code that works a charm

Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim EmbeddedObject      As Object

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Get the attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
Set EmbeddedObject = RichTextItem.GETEMBEDDEDOBJECT("wade.html")
     
'Extract the file
Call EmbeddedObject.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\Wade\Wade.html")

MsgBox ("File has been extracted successfully")


End Sub

This is my ammended code that I can't seem to get to work, because I don't know what I'm doing with the FTSearch function.  Just as a reference the email is produced by "BAS IOLB support" and the subject it is given is "Job Output".  Now this is where I think my code falls over as I don't think it's actually finding the email with the search parameters i've given it.  

As a side note I have a green radio button in my Notes interface that says indexed next to it if that helps?

Any suggestions most welcome

Cheers


Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim EmbeddedObject      As Object

Dim Items               As Integer

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("$Inbox")

'Search for the email with attachment
Items = NotesView.FTSEARCH("BAS IOLB Support AND Job Output", 0)

'Get the sent email
Set NotesDocument = NotesView.GETFIRSTDOCUMENT

'Get the attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")
Set EmbeddedObject = RichTextItem.GETEMBEDDEDOBJECT("*.html")
     
Call EmbeddedObject.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\*.html")

MsgBox ("File has been extracted successfully")
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19714235
DOn't bother with FTSearch.  You can figure out whether to go to the bottom or top easily -- just go to the bttom, get the date, go to the top, get the date, and choose whicever one is later!

As to the file name, no, you can't use a wildcard.  However, you can cycle through all the attachments as follows:

fileName = NotesDocument.GetItemValue("$FILE")
Set EMbeddedObject = NotesDocument.GetAttachment(fileName)
0
 
LVL 14

Expert Comment

by:cezarF
ID: 19714445
i'm afraid that GetItemValue will return just one filename if you have more that one attached files. I would suggest RichTextItem.EmbeddedObjects or notesDocument.EmbeddedObjects property.
0
 

Author Comment

by:ngatirauks
ID: 19727856
Sorry qwaletee I just can't figure this out??!!??

I've added the two lines of code you supplied but when it comes to running the "set attachment" line it keeps failing.  The error I get is "Error 13 type mismatch".  Now I thought this was occurring due to how I had Declared my Filename Variable.  But it didn't matter what datatype I selected it kept failing.  I then thought it was due to Document not being accessed correctly so I added the line

Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")

But that didn't seem to help either so I commented it back out.  Is it perhaps the parameter ($FILE) in

FileName = NotesDocument.GETITEMVALUE("$FILE")

Thats incorrect??  I could't find this porperty in notes designer for NotesDocument but obviously you know more than me, and also I can't specify the filename as it is dynamic.  I'm terribly stuck...

Im sure it's going to be a real easy fix, I just can't seem to see what I'm missing??

Any help would be much appreciated

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim Attachment          As Object

Dim FileName            As Variant

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Get the attachment
'Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")

FileName = NotesDocument.GETITEMVALUE("$FILE")
Set Attachment = NotesDocument.GETATTACHMENT(FileName)

Call Attachment.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\l" & Filename)
   
   
MsgBox "4 Week Summary has been extracted successfully", vbInformation


End Sub
0
 
LVL 14

Accepted Solution

by:
cezarF earned 1600 total points
ID: 19727991
@ngatirauks:

have you tried using EmbeddedObjects  property of notesrichtext or notesdocument classes? see my comments on 08.09.2007.
0
 

Author Comment

by:ngatirauks
ID: 19728089
So simple!!! haha, works a charm!!!!

Sorry I don't know where I went wrong??    I think I was getting caught up with the "o" property.  I didn't realise you could just call that method as is, I though you had to set "o" as an object first??

What is "o" anyway?

But none the less thanks for the solution!!!

Option Explicit

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CURRENTDATABASE

'Access my Inbox
Set NotesView = NotesDatabase.GETVIEW("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GETLASTDOCUMENT

'Get the attachment
Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")

For Each o In RichTextItem.EMBEDDEDOBJECTS
    Call o.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\" & CustomerName)
Next
   
MsgBox "4 Week Summary has been extracted successfully", vbInformation

End Sub
0
 
LVL 14

Expert Comment

by:cezarF
ID: 19728180
glad it worked.

o is just a variable representing each embedded object in the EmbeddedObjects (array of EmbeddedObject)  property.  You can name it anything you want as long as you follow proper variable naming syntax.

Thanks for the points.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19735073
BTW, the reason for the type mismatch is that GetItemValue returns an array, so it should have been filename(0). And I see you noticed my earlier comment on use of EmbeddedObjects :)
0
 

Expert Comment

by:flyguy80
ID: 33521387
Hey guys, this article seems like exactly what I need. I want to run this from Microsoft Access VBA so I put in this code and when I run it in debug mode, I get an error "Run-time error 424 Object Required" when it reaches the line For Each o In RichTextItem.EmbeddedObjects. I would REALLY appreciate someone's help!

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
'Dim Attachment          As Object
Dim o As Object

Dim FileName            As Variant

'Initialise Session
Set NotesSession = CreateObject("Notes.notesSession")
Set NotesDatabase = NotesSession.CurrentDatabase

'Access my Inbox
Set NotesView = NotesDatabase.GetView("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GetLastDocument

'Get the attachment
Set RichTextItem = NotesDocument.GetFirstItem("Body")

For Each o In RichTextItem.EmbeddedObjects

'FileName = NotesDocument.GetItemValue("$FILE")
'Set Attachment = NotesDocument.GetAttachment(FileName)
Call o.ExtractFile("c:\")
Next

'Call Attachment.ExtractFile("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\l" & FileName)
   
   
MsgBox "4 Week Summary has been extracted successfully", vbInformation
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month18 days, 13 hours left to enroll

834 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