Wednesday, 7 September 2016

Quick Tip: Read Text File - Compare View Values - Send Email Using LotusScript

Last week I was asked if it was possible using LotusScript to read a Text File, which was stored in a specific Folder on the system, subsequently perform a check, based on the Values in the Text File, or a Value in the Text File is present in a specific View in the NSF and finally place the found Values in a Folder in the NSF and send an email with the found Values in the Folder to a specific email address.
The above can be solved easily by using LotusScript.

To solve the above question I first created an Agent to read the Text File using LotusScript, compare the Values of the Text File with te Values in the specific View in the NSF and finally place the found values in a Folder in the NSF.

%REM
Agent CheckValues In View with Values in Text File
%END REM
Option Public
Option Declare

Use "General" (= Name Script Library with the Email Function)
Dim session As NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim vc As NotesViewEntryCollection
Dim doc As NotesDocument
Dim vw As NotesView
Dim fc As NotesView
Dim filenum As Integer
Dim filename As String
Dim conStatus As Variant
Dim strLineValue As String
Dim checkId As Variant

Sub Initialize
Set session = New NotesSession
Set db = session.CurrentDatabase

filenum% = FreeFile()
filename$ = "C:\Temp\values.txt"
Open filename$ For Input As filenum%
Set vw = db.GetView("($ViewName)") 'Lookup View with the Values in the NSF

'empty Folder with previous found values
Set fc = db.GetView("FolderName")
Set vc = fc.AllEntries
Call vc.RemoveAllFromFolder("FolderName")

While Not EOF(filenum%) ' Read Text File until End of File
Line Input #filenum%, strLineValue$
Set doc = vw.GetFirstDocument
Do Until doc Is Nothing
checkId = doc.getitemvalue("FieldName") 'Programmatic Name Column Value in NSF View = the first sorted column in the View = Field to Compare with Values in Text File
If strLineValue = CStr(LCase$(checkId(0))) Then
MsgBox "Found Value: " & strLineValue & +" SomeFieldName: " + doc.SomeFieldName(0)
Call doc.PutInFolder("FolderName")
End If
Set doc = vw.Getnextdocument(doc)
Loop
Wend
Close filenum%

Call sendEmail(doc)

End Sub

Next, based on the found Values in the Folder I created a LotusScript function, placed in a Script Library General, to send an Email with the found values to registered persons in a Field NotificationMembers on a Profile Form in the NSF.

Function sendEmail(doc As NotesDocument) As Boolean
Dim s As NotesSession
Dim db As NotesDatabase
Dim vdoc As NotesDocument
Dim pdoc As NotesDocument
Dim rtitem As NotesRichTextItem
Dim i As Integer
Dim view As NotesView

Set s = New NotesSession
Set db = s.CurrentDatabase
Set view = db.GetView("FolderName")
Set doc = New NotesDocument(db)
Set pdoc = db.Getprofiledocument("ProfileForm")
Dim datetim As New NotesDateTime( Now() )
i=0
'- prepare mail
doc.Form = "Memo"
Set vdoc = view.GetFirstDocument

If vdoc Is Nothing Then
Exit Function
Else

While Not(vdoc Is Nothing)

'- Set recipient
doc.Principal = "SomeName"
doc.~$AltPrincipal = "SomeName"
doc.From = "SomeName"
doc.SendTo = pdoc.NotificationMembers
doc.Subject = "Found Values in Text File on" + " " + CStr(Now())

Set rtitem = New NotesRichTextItem( doc, "Body" )
Call rtitem.AddNewline(1)
Call rtitem.AppendText(vdoc.FieldName(0)) 'Programmatic Name First Column in Folder
i = i + 1
Set vdoc = view.GetNextDocument(vdoc)
Wend
'- send mail
Call doc.Send(False)
End If
End Function

A nice and easy solution to read a Text File, compare the Values in the Text Field with the Values in a View, place the found Values in a Folder and Send an EMail to specific users.

No comments:

Post a Comment