Ron de Bruin
Excel Automation

Lotus Notes: Save and Remove attachments

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

The code examples on this page are created by Excel MVP Dennis Wallentin but are not on his site anymore. But Dennis allow me to publish it on my site for all the Excel/Lotus Notes users in the world.

The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 5.0 and later.

Task:
Iterate through all the e-mails in the Inbox, save the attachments into a folder and remove them from the e-mails. (The example does not take into consideration that it may exist duplicates of one or more files.)

VBA code :

Option Explicit
 
Const stPath As String = "c:\Attachments\"

Const EMBED_ATTACHMENT As Long = 1454

Const RICHTEXT As Long = 1
 
Sub Save_Remove_Attachments()
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noView As Object
  Dim noDocument As Object
  Dim noNextDocument As Object
 
  'Embedded objects are of the datatype Variant.
  Dim vaItem As Variant
  Dim vaAttachment As Variant
 
  'Instantiate the Notes session.
  Set noSession = CreateObject("Notes.NotesSession")
 
   'Instantiate the actual Notes database.
  '(Here is the personal e-mail database used and since it's a
  'local database no reference is made to any server.)
  Set noDatabase = noSession.GETDATABASE("", "mail\xldennis.nsf")
 
  'Folders are views in Lotus Notes and in this example the Inbox is used.
  Set noView = noDatabase.GetView("($Inbox)")
 
  'Get the first document in the defined view.
  Set noDocument = noView.GetFirstDocument
 
  'Iterate through all the e-mails in the view Inbox.
  Do Until noDocument Is Nothing

    'Although the following approach is not necessary for this
    'kind of operations it may be a good approach to use in general.
    Set noNextDocument = noView.GetNextDocument(noDocument)
   
    'Check if the document has an attachment or not.
    If noDocument.HasEmbedded Then
      Set vaItem = noDocument.GetFirstItem("Body")
      If vaItem.Type = RICHTEXT Then
        For Each vaAttachment In vaItem.EmbeddedObjects
          If vaAttachment.Type = EMBED_ATTACHMENT Then
            'Save the attached file into the new folder and remove it from the e-mail.
            With vaAttachment
              .ExtractFile stPath & vaAttachment.Name
              .Remove
            End With
            'Save the e-mail in order to reflect the deleting of the attached file.
            '(A more sophisticated approach may be considered if several e-mails have
             'several attachments in order to avoid a repeately saving of one e-mail.)
            noDocument.Save True, False
          End If
        Next vaAttachment
      End If
    End If
    Set noDocument = noNextDocument
  Loop
 
  'Release objects from memory.
  Set noNextDocument = Nothing
  Set noDocument = Nothing
  Set noView = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  MsgBox "All the attachments in the Inbox have successfully been saved and removed." _
  , vbInformation
 
End Sub

Comments:
Although it's difficult to work with Lotus Notes object model the above example may give a better
understanding of it when it comes to how to work with attachments.

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs