?
Solved

lotus notes agent using formula language to populate an excel spreadsheet

Posted on 2006-04-26
10
Medium Priority
?
1,547 Views
Last Modified: 2013-12-18
Please can anyone help with the following: -

I am being sent email from all users in the department which is filtered into a folder, "FOLDER_A".
All documents in this folder contain a file "doc.txt".
This is field data from a form which looks like this: -

NAME=John Smith
AREA=ITDEPT
JOB REF=123456
TAXI REASON=Client Job
COMMENTS=Arrived at 06:00hrs

I am trying to create an agent in My mail client that will do the following: -

1.  Search a designated folder (FOLDER_A) for 'new mail'
2.  Take the data (as above) from the document
2a - I want this to take all data 'after' the = sign
3.  Copy this data into an excel spreadsheet

The spreadsheet already exists (MAILSPREADSHEET) on my workstation locally.
The column headings are what is to the left of the = sign shown on the data above.

I know what I want to do but not to go about it.

I am not very good with Lotusscript and would therefore like to tackle it using @Formula language.

I have just started the agent and created a simple action for the 'Document Selection' which is "in folder 'FOLDER A'."


I would like to do all the steps above, and when the agent has copied the data from the 'new mail' within the folder, then auto archive the mail.  

Any advice would be appreciated

Thanks

0
Comment
Question by:gray180
10 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 16542386
First Soln:
This is based off of code found on this forum, but has been modified to work with any view...

Sub Click(Source As Button)
Dim Session As New NotesSession
Dim db As NotesDatabase
Dim dataview As NotesView
Dim dc As NotesDocumentCollection
Dim datadoc As NotesDocument
Dim maxcols As Integer
Dim WS As New Notesuiworkspace
Dim UiView As notesuiview
Dim ViewString As String
Set UiView=WS.currentview

ViewString=UiView.viewname

Set db = session.CurrentDatabase
Set dc = db.unprocesseddocuments
Set dataview = db.getview(ViewString)

Dim xlApp As Variant
Dim xlsheet As Variant
Dim rows As Integer
Dim cols As Integer

rows = 1
cols = 1
maxcols= dataview.columncount

Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Name = "Export From Notes"'ViewString

xlApp.StatusBar = "Creating Column Heading. Please be patient..."

For x=1 To maxcols
xlsheet.Cells(rows,cols).Value = dataview.columns(x-1).title
cols = cols + 1
Next

Set datadoc = dc.getfirstdocument
Dim fitem As NotesItem
cols=1
rows=2
Do While Not (datadoc Is Nothing)
For x=1 To maxcols
With dataview.Columns(x-1)
If .isField Then
xlsheet.Cells(rows,cols).Value = datadoc.GetItemValue(.itemname)
Elseif .isFormula Then
If .formula="@IsExpandable" Then
xlsheet.Cells(rows,cols).Value = ""
Else
xlsheet.Cells(rows,cols).Value = Evaluate(.formula,datadoc)
End If
End If
End With
cols=cols+1
Next
xlApp.StatusBar = "Importing Notes Data - Document " & rows-1 & " of " & dc.count & "."
rows=rows+1
cols=1
Set datadoc = dc.getnextdocument(datadoc)
Loop

xlApp.Rows("1:1").Select
xlApp.Selection.Font.Bold = True
xlApp.Selection.Font.Underline = True
xlApp.Range(xlsheet.Cells(1,1), xlsheet.Cells(rows,maxcols)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
With xlApp.Worksheets(1)
.PageSetup.Orientation = 2
.PageSetup.centerheader = "Report - Confidential"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16542400
Second Option:
Sub Initialize
'EXPORT TO EXCEL FOR ALL DOCUMENTS IN THE CURRENT VIEW:      
      response1 = Messagebox ( "If it is not a CLA Client Click ""OK"" to export all the documents, or Click ""Cancel"" if it is not." ,(1+48 + 0 +0) ,"CLA Client ""Yes/No""")
      'Click "OK" to export all the documents, if it is a CLA Client or Click "Cancel""
'If the User Clicks OK, Send MAil Else Continue.
      'OK means 1
      Dim session As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim doc As notesdocument
      Dim dc As notesdocumentcollection
      Dim vcols As Variant
      Dim ucols As Variant
      Dim column As NotesviewColumn
      
      Set db=session.currentdatabase
      Dim viewnmae As String
      
      On Error Goto ErrorHandler
      
      viewname="AddressofClients"  'Should change to UIVIEW
      Set view=db.getview(viewname)
      Set dc=db.unprocesseddocuments
      
      Set doc=dc.getfirstdocument
      'uvcols=Ubound(view.columns)
      'Msgbox uvcols
      'End
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
      'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      xlsheet.name="Contacts"  
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
      'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
      'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Professional Group"
      xlsheet.application.activesheet.cells(1,2).value="Company Name"
      xlsheet.application.activesheet.cells(1,3).value="Contact Person"
      xlsheet.application.activesheet.cells(1,4).value="Key Person"
      xlsheet.application.activesheet.cells(1,5).value="Designation"
      xlsheet.application.activesheet.cells(1,6).value="Email"
      xlsheet.application.activesheet.cells(1,7).value="Address1"
      xlsheet.application.activesheet.cells(1,8).value="Address2"
      xlsheet.application.activesheet.cells(1,9).value="Address3"
      'xlsheet.application.activesheet.cells(1,7).value="Postal Code"
      xlsheet.application.activesheet.cells(1,10).value="DID"
      xlsheet.application.activesheet.cells(1,11).value="Fax"
      xlsheet.application.activesheet.cells(1,12).value="Mobile No"
      xlsheet.application.activesheet.cells(1,13).value="Country"
      xlsheet.application.activesheet.cells(1,14).value="CLA Client"
      
      i=2
      'response1 = Messagebox ( "Click ""OK"" to export all the documents, if it is not a CLA Client or Click ""Cancel"" if it is not." ,(1+48 + 0 +0) ,"Mail Notice for Assign Person")
      'Click "OK" to export all the documents, if it is a CLA Client or Click "Cancel""
'If the User Clicks OK, Send MAil Else Continue.
      'OK means 1
      If response1<>1 Then
            Continue=True
      Else
            'xlapp.visible=True
            While Not doc Is Nothing
                  If doc.claclient_k(0)="No" Then
                        xlsheet.range("A"&Trim(Str(i))).value=doc.group_k(0)
                        xlsheet.range("B"&Trim(Str(i))).value=doc.company_x(0)
                        xlsheet.range("C"&Trim(Str(i))).value=doc.name_x(0)
                        xlsheet.range("D"&Trim(Str(i))).value=doc.createdfor_x(0)
                        xlsheet.range("E"&Trim(Str(i))).value=doc.jobtitle_x(0)
                        xlsheet.range("F"&Trim(Str(i))).value=doc.email_x(0)
                        xlsheet.range("G"&Trim(Str(i))).value=doc.address1_x(0)
                        xlsheet.range("H"&Trim(Str(i))).value=doc.address2_x(0)
                        xlsheet.range("I"&Trim(Str(i))).value=doc.address3_x(0)
                  '      xlsheet.range("G"&(i))=Str(045678) 'doc.postalcode_n(0)
                        xlsheet.range("J"&Trim(Str(i))).value=doc.did_n(0)
                        xlsheet.range("K"&Trim(Str(i))).value=doc.fax_n(0)
                        xlsheet.range("L"&Trim(Str(i))).value=doc.mobileno_n(0)
                        xlsheet.range("M"&Trim(Str(i))).value=doc.country_x(0)+"   "+doc.postalcode_n(0)
                        xlsheet.range("N"&Trim(Str(i))).value=doc.claclient_k(0)
                        
                        i=i+1
                        
                        Call xlsheet.Range("A1:N500").Columns.AutoFit
                        Call xlsheet.Range("A1:N500").Rows.AutoFit
                        
                  End If
                  Set doc=dc.getnextdocument(doc)
                  xlapp.statusbar="Processing ..."
            Wend
      End If
%REM
      xlsheet.Columns("A:A").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("B:B").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("C:C").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("D:D").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("E:E").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("F:F").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("G:G").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("H:H").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("I:I").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("J:J").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("K:K").select
      xlapp.selection.columns.autofit
      
      xlsheet.Columns("L:L").select
      xlapp.selection.columns.autofit
%END REM
      xlapp.statusbar="Completed ..."
      
ErrorHandler:
      'Exit Sub
      Resume Next
      
End Sub
0
 

Author Comment

by:gray180
ID: 16542486
madheeswar,

Thanks for both of these.

As I said I am not very good at looking at and understanding Lotusscript.

Can I do this using formula language or @commands?

If not, where do i put this code.

How can I use the code to open the spreadsheet i already have and apend the data to this spreadsheet?

How is the agent triggered? - ie only on new mail documents in the folder

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:Rickhal
ID: 16544589
Greetings, the part of the code provided by Madheeswar that you will need to edit is this: "xlsheet.name="Contacts"  
You will want to change "Contacts" to "MAILSPREADSHEET" and (maybe, but I am not sure) this part: "Set xlapp=createobject("Excel.Application")" may need to be changed to: "Set xlapp=getobject ("Excel_Application"). I am not a Lotus Script expert by any means, but I do know that if you use the code as provided it will create a new worksheet named: "Contacts". As for your question as to whether this can be done using formula language (@commands), I don't think you would be able to do this kind of a more complicated action with formula lanquage which does not have the ability to call objects such as your already created spread sheet and then manipulate data within that. Formula lanquage is mostly for working within the Notes API. Whereas script language can work with the Windows API and call Windows objects such as a Excel worksheet and manipulate data within it as well. To create this you would need to go to "Create" on the toolbar and then "agent". If you have the designer client installed, that action will open the design client. If not, you will see the create agent desgin element open up. You can have the agent run when new mail arrives, or as a menu item that would be placed in the "Actions" toolbar tab. Perhaps Madheeswar willl provide you with an edited script that will make use of the worksheet you already have setup. Or, you can go to the IBM/Lotus sandbox and see if there is some script there that you can just edit to call for the spreadsheet you already have in the location that it resides in on the hard drive. As for the first example: this part (xlsheet.Name = "Export From Notes"'ViewString) will name the worksheet "Export From Notes". My guess would be that a path would need to be provided to the worksheet you already have so that the code can call that worksheet rather than create a new one. But, this would require a piece of code to be added to call out to that location, and then use that worksheet ("MAILSPREADSHEET"). Sorry I can't provide the actual code to accomplish that, but I'm sure someone here at Experts Exchange will be able to help with that. Good Luck!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16545283
Just for anybody's understanding, Gray180's previous question on a similar subject:
    http:Q_21817609.html "Local Database where a form is sent to several notes users - how to get data reports from the email data"
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 2000 total points
ID: 16550760
Do you need comments of the code I used?

From formulae language, its not possible.
The above (2nd soln.,) is very simple to use. U need to modify titles and field names. and use as it is. It works.

Here is the modified code:
'EXPORT TO EXCEL FOR ALL DOCUMENTS IN THE CURRENT VIEW:    
      Dim session As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim doc As notesdocument
      Dim dc As notesdocumentcollection
      Dim vcols As Variant
      Dim ucols As Variant
      Dim column As NotesviewColumn
      
      Set db=session.currentdatabase
      Dim viewnmae As String
      
      On Error Goto ErrorHandler
      
      viewname="FOLDER_A"  'This is the Folder Name
      Set view=db.getview(viewname)
      Set dc=db.unprocesseddocuments ' Run on Selected documents.
      
      Set doc=dc.getfirstdocument
      If Not (dc.count>0) Then
            Exit Sub
      End If
     'uvcols=Ubound(view.columns)
     'Msgbox uvcols
     'End
      Dim xlapp As Variant
      Dim xlsheet As Variant
      
      Set xlapp=createobject("Excel.Application")
      xlapp.statusbar="Creating Worksheet. Please be patient ..."
      xlapp.visible=True
     'xlapp.visible=False
      xlapp.workbooks.add
      xlapp.referencestyle=2
      Set xlsheet=xlapp.workbooks(1).worksheets(1)
      
      xlsheet.name="New Mails"   'Worksheet Name
      
      Dim rows As Integer
      Dim cols As Integer
      Dim maxcols As Integer
      
     'Header Formation;
      xlapp.Rows("1:1").select
      xlapp.selection.font.name="Arial"
      xlapp.selection.font.size=11
      xlapp.selection.font.bold=True
      xlapp.selection.font.italic=True
      
     'Header Information(Titles):
      xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
      xlsheet.application.activesheet.cells(1,1).value="Name"
      xlsheet.application.activesheet.cells(1,2).value="Area"
      xlsheet.application.activesheet.cells(1,3).value="Job Ref"
      xlsheet.application.activesheet.cells(1,4).value="Taxi Reason"
      xlsheet.application.activesheet.cells(1,5).value="Comments"
      
      i=2
      
          'xlapp.visible=True
      While Not doc Is Nothing
                  'Export data to Cells in Excel.
            xlsheet.range("A"&Trim(Str(i))).value=doc.name(0) ' Field names after doc. I assume, these are field names.. u can change field names later.
            xlsheet.range("B"&Trim(Str(i))).value=doc.area(0)
            xlsheet.range("C"&Trim(Str(i))).value=doc.jobref(0)
            xlsheet.range("D"&Trim(Str(i))).value=doc.taxireason(0)
            xlsheet.range("E"&Trim(Str(i))).value=doc.comments(0)
            
            i=i+1
            
            Call xlsheet.Range("A1:E500").Columns.AutoFit
            Call xlsheet.Range("A1:E500").Rows.AutoFit
            
            Set doc=dc.getnextdocument(doc) 'Get next selected document
            xlapp.statusbar="Processing ..."
      Wend
      
      
      xlapp.statusbar="Completed ..."
      
ErrorHandler:
     'Exit Sub
      Resume Next

Place the code in an View action button and select Lotus Script.
0
 

Author Comment

by:gray180
ID: 16552435
Hi Madheeswar,

Firstly - Wow!  Brilliant piece of code to do near enough what I want to do.
I even learnt how to add an action button which was useful too.

Is it possible to add to this code so that I can 'open' an existing spreadsheet?
If possible I would like to : -

1)  Open an existing spreadsheet.
2) Copy all new mail inside this folder to the spreadsheet.
3) Archive the mail automatically from the function.

Therefore, the folder will only contain new mail to be inserted into the saved spreadsheet.
Also, I will have just the one saved spreadsheet with up to date data that i can work on.

If this is too difficult to do, I suppose that I could always set lotus notes up to automatically 'archive' mail that goes to this folder.  Then I could use the script you sent inside the 'archive' folder and every time I need to work on the spreadsheet I can re-run the script after selecting all documents in the folder in order to create an up to date copy.

What do you think?  I hope this makes sense!!

If it's difficult to do, then no problem as this does everything I was looking for, and as i say i can get round this as above.

Also, thankyou to Sjef for previously answering a question that has helped in doing this, and also to Rickhal for how to change certain things.
Without all of your help I wouldnt have been able to do this or understand the code.
The comments in the code are really useful and at last I can finally see how it all works so thankyou very very much!!!

Regards
Graham,
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16552504
Yes... we can do waht u r asking for...to do that we have change code.. which is unnecessary I think.

Second option which you suggested is good.
0
 

Author Comment

by:gray180
ID: 16552695
Yes, I agree.

What I didn't realise was that the archive folder has the view action button that I added earlier which is useful.

Therefore all I need to do is set up the notes client to automatically archive the mail once I get it and run the script to populate a spreadsheet as and when I need the data.
I think I will just add a command to the script that selects all the documents in the archive folder and its done.

Thankyou so much for all your help.

Graham.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 16552911
So... its like taking all the documents... then use this code:
'EXPORT TO EXCEL FOR ALL DOCUMENTS IN THE CURRENT VIEW:    
     Dim session As New notessession
     Dim db As notesdatabase
     Dim view As notesview
     Dim doc As notesdocument
    ' Dim dc As notesdocumentcollection
     Dim vcols As Variant
     Dim ucols As Variant
     Dim column As NotesviewColumn
     
     Set db=session.currentdatabase
     Dim viewnmae As String
     
     On Error Goto ErrorHandler
     
     viewname="FOLDER_A"  'This is the Folder Name
     Set view=db.getview(viewname)
'     Set dc=db.unprocesseddocuments ' Run on Selected documents.
     
 '    Set doc=dc.getfirstdocument
    Set doc=view.getfirstdocument

     If (view is nothing) Then
          Exit Sub
     End If
     'uvcols=Ubound(view.columns)
     'Msgbox uvcols
     'End
     Dim xlapp As Variant
     Dim xlsheet As Variant
     
     Set xlapp=createobject("Excel.Application")
     xlapp.statusbar="Creating Worksheet. Please be patient ..."
     xlapp.visible=True
     'xlapp.visible=False
     xlapp.workbooks.add
     xlapp.referencestyle=2
     Set xlsheet=xlapp.workbooks(1).worksheets(1)
     
     xlsheet.name="New Mails"   'Worksheet Name
     
     Dim rows As Integer
     Dim cols As Integer
     Dim maxcols As Integer
     
     'Header Formation;
     xlapp.Rows("1:1").select
     xlapp.selection.font.name="Arial"
     xlapp.selection.font.size=11
     xlapp.selection.font.bold=True
     xlapp.selection.font.italic=True
     
     'Header Information(Titles):
     xlapp.statusbar="Creating Cells and Creating Cell Headings. Please be patient ..."
     xlsheet.application.activesheet.cells(1,1).value="Name"
     xlsheet.application.activesheet.cells(1,2).value="Area"
     xlsheet.application.activesheet.cells(1,3).value="Job Ref"
     xlsheet.application.activesheet.cells(1,4).value="Taxi Reason"
     xlsheet.application.activesheet.cells(1,5).value="Comments"
     
     i=2
     
          'xlapp.visible=True
     While Not doc Is Nothing
               'Export data to Cells in Excel.
          xlsheet.range("A"&Trim(Str(i))).value=doc.name(0) ' Field names after doc. I assume, these are field names.. u can change field names later.
          xlsheet.range("B"&Trim(Str(i))).value=doc.area(0)
          xlsheet.range("C"&Trim(Str(i))).value=doc.jobref(0)
          xlsheet.range("D"&Trim(Str(i))).value=doc.taxireason(0)
          xlsheet.range("E"&Trim(Str(i))).value=doc.comments(0)
         
          i=i+1
         
          Call xlsheet.Range("A1:E500").Columns.AutoFit
          Call xlsheet.Range("A1:E500").Rows.AutoFit
         
          'Set doc=dc.getnextdocument(doc) 'Get next selected document
 Set doc=view.getnextdocument(doc)
          xlapp.statusbar="Processing ..."
     Wend
     
     
     xlapp.statusbar="Completed ..."
     
ErrorHandler:
     'Exit Sub
     Resume Next
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question