generateLicensesReportsToWordV2

Sub Initialize
    Dim ws As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim template As NotesDocument
    Dim rField1 As NotesRichTextItem
    Dim rField2 As NotesRichTextItem
    Dim embObject As NotesEmbeddedObject
    Dim path, fileName, templateName As String ' path to temp file, template for new files
    Dim countries List As String
    Dim pos, locNumber, locCount, rowCounter, entryCounter, generalCount As Integer

    'collecting data
    Dim collection As NotesView
    Dim CollectionDoc As NotesDocument
    Dim amailServer, aFullName As NotesName
    Dim view As NotesView
    Dim countOfReports As Integer

    ' New document, which will be created somewhere
    Dim attSession As New NotesSession
    Dim attDb As NotesDatabase
    Dim attDoc As NotesDocument
    Set attDb = attSession.CurrentDatabase
    Set attDoc = New NotesDocument ( attDb )
    attDoc.Form = "SaveAttachments"
    Dim rtItem As NotesRichTextItem ' for storing attachments
    ' end of init

    'Get report tamplate
    Set db = session.CurrentDatabase
    Set view = db.GetView("reportTemplate")
    Set template = view.getfirstdocument
    If template Is Nothing Then
        MessageBox "No configuration document",16
        Exit Sub
    End If
    Set rField1 = template.getfirstitem("template")
    If rField1 Is Nothing Then
        MessageBox "No confiuration document with template",16
        Exit Sub
    End If
    Set embObject = rField1.GetEmbeddedObject("reporttemplate.doc")
    If embObject Is Nothing Then
        MessageBox "Configuration file was now found in configuration document",16
        Exit Sub
    End If

    ' temporary path for storing tamplates
    path = "C:\Users\volodymyr.stoyko\Desktop\reports\"


    ' Initialize array of locations - those will be used as key to filter collected data
    countries("Marocco") = "AWSM"
    countries("Mexico") = "PUE"
    countries("Tunisia") = "JEN"
    countries("Germany") = "WOB|ING|BLN|CER|CHA|CHD|GRO|SHA|WUX|ZWI"
    countries("Poland") = "GOR"
    countries("Ukraine") = "TER|CHE"
    countries("Bolgaria") = "KAR|MEZ"
    countries("Slovakia") = "NIT|BRA"
    countries("Romania") = "CRN"

    ForAll location In countries
        pos = 0 'position in string with few locations

        'word variables
        Dim wordapp As Variant
        Dim worddoc As Variant
        Dim actDoc As Variant
        Dim table As Variant
        Dim selection As Variant

        templateName = Environ("TEMP") + "reporttemplate.doc"
        fileName = path + "SCLN_ServiceConsumptionEvidence_" + ListTag(location) + _
                            Format(Now, "_ddmmyyyy")+".doc"
        Call embObject.Extractfile(templateName)
        Set wordapp = CreateObject("word.application")
        ' one way - extrct document and opent it for editing.
        ' second way - store the dot template and add this template for new docs
        Set worddoc = wordapp.documents.open(templateName)
        wordapp.visible = False

        countOfReports = template.GetItemValue("countOfReports")(0)
        countOfReports = countOfReports + 1

        rowCounter = 1
        generalCount = 0

        Set table = wordapp.Selection.Document.tables(2)

        ' there insert location and count of user in the en

        If InStr(location, "|") <> 0 Then

            While pos < Len(location)

                ' count locations for
                'Start collect data
                Set collection = db.Getview("_Personen")

                'If collection.Count  > 0 Then
                'MsgBox CStr(collection.Count) + " |  " + location
                'Беремо перший док
                Set CollectionDoc = collection.GetFirstDocument

                table.Rows.Add ' add location string
                rowCounter = rowCounter + 1
                locNumber = rowCounter
                entryCounter = 0 ' count of docs for location

                Do While Not(CollectionDoc Is Nothing)
                    'if check if it is proper department
                    Set amailServer = New NotesName(CollectionDoc.GetItemValue("MailServer")(0))
                    Set aFullName = New NotesName(CollectionDoc.GetItemValue("FullName")(0))

                    If Right(aFullName.Canonical,Len(aFullName.Canonical) - _
                    InStr(aFullName.Canonical, "O=") - 1) = _
                        Left(Right(location,Len(location)-pos), 3)  Then
                            With table
                                .Rows.Add   ' add new row
                                rowCounter = rowCounter + 1
                                entryCounter = entryCounter + 1
                                ' NotesName abbreviated or this shit left-right-Instr
                                .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                                .Cell(rowcounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                                .Cell(rowCounter, 3).Range.InsertAfter(CollectionDoc.GetItemValue("InternetAddress")(0))
                                .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                                .Cell(rowCounter, 5).Range.InsertAfter(CollectionDoc.GetItemValue("MailFile")(0))

                            End With
                        End If

                    Set CollectionDoc  = collection.GetNextDocument(CollectionDoc)
                Loop
                'End If

                table.Rows(locNumber).Cells.Merge
                table.Cell(locNumber,1).Range.InsertAfter("Location: " + _
                Left(Right(location,Len(location)-pos), 3) + _
                ". Quantity of users: " + CStr(entryCounter))
                table.Rows(locNumber).Range.Font.Bold = True

                generalCount = generalCount + entryCounter
                pos = pos + 4
            Wend

        Else

            Set collection = db.GetView("_Personen")

            Set CollectionDoc  = collection.GetFirstDocument

            table.Rows.Add ' add location string
            locNumber = table.Rows.Count
            entryCounter = 0 ' count of docs for location
            rowCounter = rowCounter + 1
            'поки не закінчилися дані
            Do While Not(CollectionDoc Is Nothing)

                'if check if it is proper department
                Set amailServer = New NotesName(CollectionDoc.GetItemValue("MailServer")(0))
                Set aFullName = New NotesName(CollectionDoc.GetItemValue("FullName")(0))

                If Right(aFullName.Canonical,Len(aFullName.Canonical) - _
                InStr(aFullName.Canonical, "O=") - 1) = location Then
                    With table
                        .Rows.Add   ' add new row
                        rowCounter = rowCounter + 1
                        entryCounter = entryCounter + 1
                        ' NotesName abbreviated or this shit left-right-Instr
                        .Cell(rowCounter, 1).Range.InsertAfter(entryCounter)
                        .Cell(rowcounter, 2).Range.InsertAfter(aFullName.Abbreviated)
                        .Cell(rowCounter, 3).Range.InsertAfter(CollectionDoc.GetItemValue("InternetAddress")(0))
                        .Cell(rowCounter, 4).Range.InsertAfter(amailServer.Abbreviated)
                        .Cell(rowCounter, 5).Range.InsertAfter(CollectionDoc.GetItemValue("MailFile")(0))

                    End With
                End If

                Set CollectionDoc=collection.GetNextDocument(CollectionDoc)
            Loop

        generalCount = entryCounter

        table.Rows(locNumber).Cells.Merge
        table.Cell(locNumber,1).Range.InsertAfter("Location: " + location + _
        ". Quantity of users: " + CStr(generalCount))
        table.Rows(locNumber).Range.Font.Bold = True
        End If

        ' summary
        Set table = wordapp.Selection.Document.tables(1)
        table.Cell(2,1).Range.InsertAfter(" " + CStr(countOfReports))
        table.Cell(2, 2).Range.InsertAfter(" " + ListTag(location))
        table.Cell(2, 3).Range.InsertAfter(" " + Format(Now, "mmmm yyyy"))
        table.Cell(3, 1).Range.InsertAfter(" " + CStr(generalCount))


        Call worddoc.Saveas(fileName)
        worddoc.Close
        Set wordDoc = Nothing
        wordapp.Quit
        Set wordapp = Nothing


        'add files to attachment-doc and remove from disc
        Set rtitem =  New NotesRichTextItem( attDoc, "saveAttachment" )
        Call rtitem.EmbedObject ( EMBED_ATTACHMENT, "", fileName)
        Kill fileName ' deleted file after adding

    End ForAll

    'add new counter to template doc
    template.countOfReports = countOfReports
    Call template.Save(True, False)

    ' Save document with attachments
    Call attDoc.save(True, False)
End Sub