script to open a message and extract some info, then move to folder 1

[NathanBMiller]

Hi,
I am having trouble finding a solution on how to do this. I have auto generated messages that I put in a folder using a mail rule. I want to open those messages, extract some information and then move the message to another folder using vbscript. I need help with the commands I need to open the message, copy the info, and move the message.

Thanks In Advance,
Nathan

 

[pmonett]

You say you want to copy the info ; into what ? Are you creating another copy of the information ?

There are two major hurdles in what you want to do :
1) getting only the documents you need to work on from the folder
2) extracting the information (if it's in the Body field)

The first problem is due to the fact that there are very probably many messages in your folder, and most of them have already been dealt with. That means that you're going to have to set a flag on all docs that have been dealt with, and preferably change the folder design to display the flag in the first column (to sort the folder and allow easier retrieval).
Once you have sorted out the folder and set the flag, you can direct the agent that will do the job to make a collection of all docs that do not have the flag (they will be first in the list).

The second issue is thornier if you are looking through the Body field. Indeed, sorting through a Rich Text field in code is a very tedious affair. Hopefully, the fact that the messages are auto-generated means that the Body field contains standard text that can be easily recognized.
What would be even better would be that the messages generated included other fields with data readily available. That way, you could extract the data immediately and do whatever it is you need to with it.

Could you be a bit more explicit about the content of the documents and what you mean by copying ?

Pascal.

 

[NathanBMiller]

Sure,
I can try to clarify a bit. What I was hoping to do with the messages is once the data is extracted either move them to a different folder or just delete them. That way the only messages in the folder are ones that need dealt with. Secondly, yes the info is in the message body, and yes the messages are standard, i.e. the info I need to get will always be in the same spot. I need to put the info into an excel spreadsheet. I have had experience doing it the other way, excel to notes, but never notes to excel.

 

[NathanBMiller]

I don't know the exact content I will need from the emails, but it will be something like order#s, account#s, maybe dollar amounts. That type of thing. The boss hasn't sent me one of the messages yet, he just showed me one on his computer and asked me if I could do it, and I said I would try

 

[pmonett]

How is the message created ? Would it be possible to review the creation process to include pertinent data in specific fields ?
That would make your job a lot easier.

Okay, now for some problem-solving. I have a library to export data into an Excel spreadsheet - I just have to find it. As soon as I do, I'll post it. So that will take care of the Excel side.
Next : choosing the documents. You say that you plan to move all docs that are anayzed - which should mean all docs in the folder. I will therefor assume that everything in the folder is to be processed, then moved out of the folder (or deleted). That means the choice of docs to process is a simple iteration through the folder - simple enough.
Finally, the Body field. You say the information will always be in the same spot. I suppose that that means that the info will always be preceeded by the same info (or palceholder info), and we can have a standard way of retrieving it (ie iterate through the lines until Order shows up).

With all this as a given, we can outline a procedure for the code to follow :

1) open the Excel file and prepare the sheet
2) open the folder and start iterating
3) with each document, get the Body item
4) call procedure to extract parameters needed, and write in Excel
5) once processed, the document is moved/discarded

Pascal.

 

[pmonett]

Point #2 can be envisioned like this :

dim session as new notessession
dim db as notesdatabase
dim view as notesview
dim doc as notesdocument
dim olddoc as notesdocument

set db = session.currentdatabase
set view = db.getview("foldername")
set doc = view.getfirstdocument
do while not(doc is nothing)
   'process step 3 here
   'step to next doc in folder
   set olddoc = doc
   set doc = view.getnextdocument(olddoc)
   'in order to move the processed doc to another folder
   call olddoc.putinfolder("storagefoldername")
   call olddoc.removefromfolder("foldername")
   delete olddoc
   'do this to delete the processed document
   call olddoc.remove(true)
loop

That will take care of iteration.

Pascal.

 

[NathanBMiller]

that good is great, it will work perfectly. Now, how can I get that info I need from the body? The number I need will always be preceded by the same info. Something like

ACCOUNT#: 1548754
AMOUNT#: 15.84
DATE: 10/07/04

If I can get the line into a string variable, I can get it from there. I just don't know how to do that. I am just really new to scripting with Notes. So that last bit of code is a BIG help. Thanks

 

[NathanBMiller]

here is a sample message, this is how the body will always be.

*** AUTOMATIC MESSAGE *** (DO NOT ANSWER OR REPLY TO THIS MESSAGE)
TRANSFER ORDER 7607953 ON PACKING SLIP 7607953001
PEGGED TO ORDER 7607950 LINE 000500001 FOR CUSTOMER 53105 0070
ALLOWED FREIGHT AMOUNT: 43.05
EXTENDED SALES AMOUNT: 95.00
EXTENDED COST: 56.63
GROSS MARGIN % AFTER FREIGHT: 4.9-%

THE TRANSFER ORDER FOR YOUR SALES ORDER WAS SHIPPED VIA UPS NEXT DAY
AIR AND THE FREIGHT WAS ALLOWED ON YOUR ORDER. IF THE FREIGHT CAN BE
BILLED TO THE CUSTOMER AND THE SHIPMENT HAS NOT BEEN INVOICED
PLEASE ADD A MISCELLANEOUS CHARGE TO THE ORDER FOR THE FREIGHT COST
ABOVE. IF THE FREIGHT CAN BE BILLED TO THE CUSTOMER AND THE SHIPMENT
HAS BEEN INVOICED, PLEASE CREATE A BILL ONLY ORDER TO BILL THE
CUSTOMER FOR THE FREIGHT CHARGE.

 

[NathanBMiller]

I am having trouble getting the code you gave me to work. I have Notes Version 5.0.11. I can get a Notes Session with different syntax.

Set session = CreateObject("Notes.NotesSession")
Set db = session.GETDATABASE("chssrvnotes2/McJunkin", "names.NSF")

so would that change the syntax on those functions?

 

[NathanBMiller]

forget what I said about the syntax. now I am having trouble at runtime. Set db = session.CURRENTDATABASE gives me error 91 "object variable or With block variable not set". Also when I tried to Dim Session As New Notes session I got an error saying it wasn't a valid use of the New keyword

Dim session As NotesSession
Dim db As NotesDataBase
Dim view As NotesView
Dim doc As NotesDocument
Dim olddoc As NotesDocument

Sub Nathan()

Set db = session.CURRENTDATABASE
Set view = db.getview("Fun Stuff")
Set doc = view.getfirstdocument
Do While Not (doc Is Nothing)
   'process step 3 here
   'step to next doc in folder
   Set olddoc = doc
   Set doc = view.getnextdocument(olddoc)
   'in order to move the processed doc to another folder
   Call olddoc.putinfolder("Just For Fun")
   Call olddoc.removefromfolder("Fun Stuff")
   'Delete olddoc
   'do this to delete the processed document
   'Call olddoc.Remove(True)
Loop

End Sub

 

[pmonett]

Right, my bad.
you need to declare the session like this :

Dim session as new notessession

That should solve the rest of the declarative issues.

I'm still looking into getting that Excel export stuff. I'll take a look at your default message shortly.

Pascal.

 

[NathanBMiller]

That is what I did, I just typed it wrong above, when I try to compile it using Dim session as new notessession I get an error saying "Invalid use of New keyword". I'm using Excel, and I have the Lotus Notes Automation Classes reference installed.

 

[pmonett]

I finally retrieved my Excel export library.
Copy/paste this into a text file, create a LS library and Import the file :

*******************************************************
'LSExcelExport:

Option Public



Const boldface_on = True
Const boldface_off = False
Const underline_on = True
Const underline_off = False

Const justify_left = 0
Const justify_center = 1
Const justify_right = 2
Const justify_full = 3
Sub NewExcelSheet(msexcel As Variant, xlSheet As Variant, IsVisible As Variant)
'msexcel is for passing back control of the instance of word an the new word document respectively
'IsVisible is for determining whether on not to make the instance of Word visible

'Create an instance of Excel
Set msexcel = CreateObject("Excel.Application")
msexcel.DisplayAlerts = False
msexcel.visible = True
msexcel.Workbooks.Add '// Open a new Excel file
'Set xlsheet = msexcel.ActiveWorkbook.ActiveSheet

End Sub
Sub setcellfont(msexcel As Variant, cellrange As Variant, fontname As String,fontsize As Integer,boldface As Variant,underline As Variant)

With msexcel.activeworkbook.activesheet.range(cellrange)
.Font.Name = fontname
.Font.Size = fontsize
.Font.Bold = boldface
.Font.underline = underline
.Font.Italic = False
%REM
.Font.StrikeThrough = False
.Font.DoubleStrikeThrough = False
.Font.Outline = False
.Font.Emboss = False
.Font.Shadow = False
.Font.Hidden = False
.Font.SmallCaps = False
.Font.AllCaps = False
.Font.ColorIndex = 2 ' Blue - value of wdBlue MS Word Constant
.Font.Engrave = False
.Font.Superscript = False
.Font.Subscript = False
.Font.Spacing = 0
.Font.Scaling = 100
.Font.Position = 0
.Font.Kerning = 0
.Font.Animation = 6 ' Same as constant: wdAnimationShimmer
.TypeText Chr$(13)
%ENDREM
End With
End Sub


Sub fillcell(msexcel As Variant, posy As Variant, posx As Variant, cellvalue As Variant)

msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy) = cellvalue
End Sub


Sub setcellwidth(msexcel As Variant, posy As Integer, cellwidth As Variant)
' activesheet.Columns("A:A").ColumnWidth = 11.57
msexcel.activeworkbook.activesheet.Columns(posy).ColumnWidth = cellwidth
End Sub

Sub setstdcellwidth(msexcel As Variant, cellwidth As Variant)
msexcel.activeworkbook.activesheet.standardwidth=cellwidth
End Sub

Sub namecellrange(msexcel As Variant, cellrange As String,rangename As String)
msexcel.activeworkbook.activesheet.Range(cellrange).Value = rangename
End Sub
Sub setCellFormat(msexcel As Variant, posy As Variant, posx As Variant, cellformat As Variant)
' .Cells(r,c).NumberFormat = "#,###" (or .Selection.NumberFormat = "#,###")
msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).numberformat = cellformat
End Sub
Sub fillcellformula(msexcel As Variant,posy As Variant,posx As Variant,cellformula As String)

msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).formula = cellformula
End Sub
Sub setcellfontbold(msexcel As Variant,posy As Variant,posx As Variant)

msexcel.activeworkbook.activesheet.cells(posx,posy).font.bold = True
End Sub

Sub setcellfontunderline(msexcel As Variant,posy As Variant,posx As Variant)

msexcel.activeworkbook.activesheet.cells(posx,posy).font.underline = True
End Sub

Sub setcellfontitalic(msexcel As Variant,posy As Variant,posx As Variant)

msexcel.activeworkbook.activesheet.cells(posx,posy).font.italic = True
End Sub

Sub setcellalignment(msexcel As Variant,posy As Variant,posx As Variant,justifytype As Integer)

msexcel.activeworkbook.activesheet.cells(posx,posy).HorizontalAlignment = justifytype
End Sub