Hi
I have windows 7 and Notes 8.5.3
I have a scheduled agent that exports to excel and attaches the excel file to an email and sends it out. It has always been running fine. What was discovered in the last run was the excel file from the email, when opened shows gibberish. When i run this agent manually, it seems fine but with the scheduled, it sometimes exports with gibberish. Any explanation?
Here’s the code :
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim doc As NOtesDocument
Dim view As notesview
Dim Collection As NotesDocumentCollection
Dim rtitem As NotesRichTextItem
Dim Object As NotesEmbeddedObject
Dim sTo(0) As String
Dim CC(5) As String
sTo(0) = "Sonal Patel"
On Error Goto ErrorExit
Set db = s.currentdatabase
'check for admin server
dbserver = Ucase(db.server)
Set acl = db.ACL
Set acle = acl.GetFirstEntry
Do While Not(acle Is Nothing)
If acle.IsAdminServer And Ucase(acle.name) = dbserver Then
Goto RunTHISAgent
End If
Set acle = acl.GetNextEntry(acle)
Loop
Exit Sub
RunTHISAgent:
Set maildoc = db.createdocument
maildoc.Form = "Memo"
maildoc.Subject = "Data Preservation Management Holds List"
maildoc.SendTo = "Sonal Patel"
maildoc.BlindCopyTo = "Sonal Patel"
Set rtitem = New NotesRichTextItem( maildoc , "Body" )
Call rtitem.AppendText( "Attached below is a list of Holds currently in place." )
Call rtitem.AddNewLine(2)
datestring = Cstr(Year(Date$)) + "-" + Right("00" + Cstr(Month(Date$)),2) + "-" + Right("00" + Cstr(Day(Date$)),2)
fileN = "Holds List - " + datestring + ".XlS"
‘’- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
HomeDir$ = Environ("Temp")
Set wsh = CreateObject("Scripting.FileSystemObject")
theFile = HomeDir$ & "\" + fileN
If wsh.FileExists(theFile) Then
Kill theFile
End If
fileNum = Freefile()
Open theFile For Append As fileNum
'----------------------------------------------------------------------------------------------------------------------
Print #fileNum, "Client Matter" + Chr(9) + "Hold Name" + Chr(9) + "Date Opened" + Chr(9) + "Date Lifted" + Chr(9) + "Requestor" + Chr(9) + "ContactType" + Chr(9) + "Requestor Type" + Chr(9) "Relevant Period"
Set view = db.GetView("By Client Matter")
Set doc = view.GetFirstDocument
While not doc is nothing
'start exporting docs in to Excel - Columns A to H
xlTable$ = Join(doc.ClientMatterNo,",") + Chr(9) + doc.HoldName(0) + Chr(9) + Cstr(doc.DateOpened(0)) + Chr(9) + Cstr(doc.DateClosed(0)) + Chr(9) + doc.Requestor(0)+ Chr(9) + Join(doc.Contact,",") + Chr(9) + doc.RequestType(0) + Chr(9) + Join(doc.RelevantPeriod,",")
Print #fileNum, xlTable$
Set doc = view.GetNextDocument(doc)
Wend
'Attach the exported file to an email
Set object =rtitem.EmbedObject (EMBED_ATTACHMENT,"", theFile)
Call rtitem.AddNewLine(2)
Call rtitem.AppendText("Please contact Sonal Patel if you have any questions." )
Call rtitem.AddNewLine(2)
Call rtitem.AppendText("Thanks." )
Call maildoc.Send(False)
Close fileNum
If wsh.FileExists(theFile) Then Kill theFile
Exit Sub
ErrorExit:
Dim agent As NotesAgent
Dim n As String
Set agent = s.CurrentAgent
n = agent.Name
Print "Error executing " & n & " agent in database: " & db.Title
Print "Error in Script line: " & Erl & ". Error text: " & Error$()
'send error email
Set errormail= New NotesDocument( db )
Set rtitem = New NotesRichTextItem(errormail, "Body")
errormail.Form = "Memo"
errormail.SendTo = "Sonal patel"
errormail.Subject = "Error in ""Weekly - Send Holds List"" agent"
' compose body of memo
Call rtitem.AppendText( "Agent running: " & s.CurrentAgent.Name )
Call rtitem.AddNewLine(1)
Call rtitem.AppendText( "Database: " & db.Title)
Call rtitem.AddNewLine(1)
Call rtitem.AppendText( "Script line: " & Erl )
Call rtitem.AddNewLine(1)
Call rtitem.AppendText( "Error number: " & Err )
Call rtitem.AddNewLine(1)
Call rtitem.AppendText( "Error text: " & Error$() )
errormail.Send( False)
’ Msgbox "Error in Script line: " & Erl & ". Error text: " & Error$()
Exit Sub
End Sub