title: Lotus Notes Agent - extract attachments from DB to separate folders for each document
body: <p>Have an assignment to do - it's to extract data from Lotus Notes DB including documents and their attachments. The purpose of this is to put it and store on the Sharepoint as a library.</p>
  
  <p>So far I have managed to create a view and export the data for it to structure in Excel.</p>
  
  <p>Also I have looked up some Agents examples for extracting the attachments. With implementation of below script I managed to export the attachments:</p>
  
  <pre><code>Dim sDir As String
  Dim s As NotesSession
  Dim w As NotesUIWorkspace
  Dim db As NotesDatabase
  Dim dc As NotesDocumentCollection
  Dim doc As NotesDocument
  
  Sub Initialize
  
   Set s = New NotesSession
   Set w = New NotesUIWorkspace 
   Set db = s.CurrentDatabase
   Set dc = db.UnprocessedDocuments
   Set doc = dc.GetFirstDocument
   Dim rtItem As NotesRichTextItem
   Dim RTNames List As String
   Dim DOCNames List As String
   Dim itemCount As Integer
   Dim sDefaultFolder As String
   Dim x As Integer
   Dim vtDir As Variant
   Dim iCount As Integer
   Dim j As Integer
   Dim lngExportedCount As Long
   Dim attachmentObject As Variant
  
  
   x = MsgBox("This action will extract all attachments From the " &amp; CStr(dc.Count) &amp; _
   " document(s) you have selected, And place them into the folder of your choice." &amp; _
   Chr(10) &amp; Chr(10) &amp; "Would you like To continue?", 32 + 4, "Export Attachments")
   If x &lt;&gt; 6 Then Exit Sub 
  
   sDefaultFolder = s.GetEnvironmentString("LPP_ExportAttachments_DefaultFolder")
   If sDefaultFolder = "" Then sDefaultFolder = "F:"
   vtDir = w.SaveFileDialog( False, "Export attachments To which folder?", "All   files|*.*", sDefaultFolder, "Choose Folder and Click Save")
   If IsEmpty(vtDir) Then Exit Sub
   sDir = StrLeftBack(vtDir(0), "\")
   Call s.SetEnvironmentVar("LPP_ExportAttachments_DefaultFolder", sDir) 
  
   While Not (doc Is Nothing)
  
    iCount = 0
    itemCount = 0
    lngExportedCount = 0
    Erase RTNames
    Erase DocNames
  
    'Scan all items in document
    ForAll i In doc.Items
  
     If i.Type = RICHTEXT Then
      Set rtItem = doc.GetfirstItem(i.Name)
      If Not IsEmpty(rtItem.EmbeddedObjects) Then
   RTNames(itemCount) = CStr(i.Name)
   itemCount = itemCount +1
      End If
     End If
  
    End ForAll  
  
    For j = 0 To itemCount-1 
     Set rtItem = Nothing
     Set rtItem = doc.GetfirstItem(RTNames(j))
     ForAll Obj In rtItem.EmbeddedObjects
      If ( Obj.Type = EMBED_ATTACHMENT ) Then
       Call ExportAttachment(Obj)
       Call doc.Save( False, True ) 
   'creates conflict doc if conflict exists
      End If 
     End ForAll 
    Next
  
    'Scan all items in document
    ForAll i In doc.Items
  
     If i.Type = ATTACHMENT Then
  
      DOCNames(lngExportedCount) = i.Values(0)
      lngExportedCount = lngExportedCount + 1
  
     End If
  
    End ForAll
  
    For j% = 0 To lngExportedCount-1 
     Set attachmentObject = Nothing
     Set attachmentObject = doc.GetAttachment(DOCNames(j%))
     Call ExportAttachment(attachmentObject)     
     Call doc.Save( False, True ) 
  'creates conflict doc if conflict exists
    Next  
  
    Set doc = dc.GetNextDocument(doc)
   Wend
  
   MsgBox "Export Complete.", 16, "Finished"
  
  End Sub
  
  Sub ExportAttachment(o As Variant)
  
  Dim sAttachmentName As String
  Dim sNum As String
  Dim sTemp As String
  
  sAttachmentName = sDir &amp; "\" &amp; o.Source
  While Not (Dir$(sAttachmentName, 0) = "")
      sNum = Right(StrLeftBack(sAttachmentName, "."), 2)
      If IsNumeric(sNum) Then
          sTemp = StrLeftBack(sAttachmentName, ".")
          sTemp = Left(sTemp, Len(sTemp) - 2)
          sAttachmentName = sTemp &amp; Format$(CInt(sNum) + 1, "##00") &amp; _
          "." &amp; StrRightBack(sAttachmentName, ".")
      Else
          sAttachmentName = StrLeftBack(sAttachmentName, ".") &amp; _
          "01." &amp; StrRightBack(sAttachmentName, ".")
      End If
  Wend
  
  Print "Exporting " &amp; sAttachmentName
  'Save the file
  Call o.ExtractFile( sAttachmentName ) 
  
      End Sub
  </code></pre>
  
  <p>So the issue I do have right now is that these attachments are being saved to the same folder which means that I would manually have to put them into right folders of library (several thousands). Could anyone help on how should I change the above code to have the attachments saved to separate folder for each document from DB?</p>
  
  <p>Also for some reason that I cant find out below line is causing error pop up with "Object Variable not set":</p>
  
  <pre><code>sAttachmentName = sDir &amp; "\" &amp; o.Source
  </code></pre>
  
  <p>Would anyone know why it causes failure and stops the whole process?</p>
  
  <p>Thanks,
  Rafal</p>