jonathanwong
asked on
How to export the user lists of mail database to excel format?
There a about 600 mail user databases in the mail\ director of domino v5.0.13, how can i export the information (title) of the database to excel format file?
there should be catalogue.ntf
Please check that
Please check that
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As mentioed, I would simply add a private view in the Domino directory ( addresss book ), and use the export to Lotus 123. This can be imported into Excel.
I hope this helps !
I hope this helps !
ASKER
Hi, rrabie
How can do this? Could you please explain furter. Thanks!
How can do this? Could you please explain furter. Thanks!
Did you try to acsess the database called "catalog" on any Domino Server ?
It has lots of views and info on all databases that can easily be exported.
I hope this helps !
It has lots of views and info on all databases that can easily be exported.
I hope this helps !
The catalog.nsf does contain a list of databases, but traditionally, mail databases are not listed in the catalog. The mail template defaults to "do not list". You can check the box on the template and the server will update the mail designs to be listed, and then update the catalog so you can export the view to excel.
Or you can create an agent on a server database that does the collection and mails you the results:)
I have a test database where I ran this scheduled, with full administration permissions, on the server. It sends you a "comma delimited file" that will open in Excel. Be sure to add your email address, server name, and the correct path where indicated. This will collect *.nsf files.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
'ENTER YOUR SERVER NAME HERE---------------------- ---------
Const str_SERVER = "YOUR SERVER NAME"
'ENTER THE FULL PATH OF THE MAIL DIRECTORY HERE ---------
Const str_STARTDIR="E:\Domino\Da ta\mail\"
'THIS IS THE FILE THAT WILL BE MAILED TO YOU ---------------
Const str_FILENAME="databasedir. csv"
'YOUR EMAIL ADDRESS HERE---------------------- ---------- ------
Const str_SENDTO ="YOUR SEND TO ADDRESS"
If db.Server = "" Then
Print "Sorry, this agent needs to run on a server"
End If
Dim chkdb As NotesDatabase
Dim processed As Long, failed As Long
Dim sendLog As Boolean
sendLog = False
Dim msg As String
Dim tb As String
tb = Chr(9)
If str_SENDTO <>"" Then
sendLog = True
End If
msg= "Starting the Db Collection agent on " + str_STARTDIR
Print msg
If sendLog Then
Dim maildoc As New NotesDocument(db)
With maildoc
.SendTo = str_SENDTO
.From = session.UserName
.Principal = session.UserName
.subject = "Database Directory File: " + str_FILENAME
End With
Dim rtItem As New NotesRichTextItem(maildoc, "Body")
With rtItem
.AddNewline 1
.appendText msg
End With
End If
Dim fileNum As Integer
Dim cdr As String
cdr = Curdir() + "\" + str_FILENAME
fileNum = Freefile
Open cdr For Output As fileNum
Write #filenum, "Title","Filename","Filepa th", "ReplicaID","Server", "TemplateName" ,"FileFormat", "DesignTemplateName"
Dim tmp As String
tmp = Curdir()
On Error Goto DBDirectoryError
Dim fileName As String
fileName = Dir$(str_STARTDIR + "*.nsf", 0)
Do While fileName <> ""
Set chkdb = New NotesDatabase(str_SERVER,s tr_STARTDI R + fileName)
If Not (chkdb Is Nothing) Then
If Not chkdb.IsOpen Then Call chkdb.Open("","")
If Not chkdb.IsOpen Then
msg= "Unable to open " + chkdb.Title
Print msg
If sendLog Then
rtitem.addnewLine 1
rtitem.appendText tb + msg
End If
failed = failed +1
Goto Next_Database
End If
'Collect your information here.............
With chkdb
Write #filenum, .title , .filename , .Filepath, .ReplicaID , .Server ,.TemplateName , Cstr(.FileFormat ), .DesignTemplateName
processed = processed +1
End With
If sendLog Then
rtItem.addnewLine 1
rtItem.appendText tb + "Collected: " + chkdb.title
End If
Set chkdb = Nothing
End If
Next_Database:
fileName = Dir$()
Loop
Close #filenum
msg = "Finished processing " + Cstr(processed) + " databases. Failed to process: " + Cstr(failed)
Print msg
ExitHere:
If sendLog Then
Dim object As NotesEmbeddedObject
With rtItem
.addnewline 2
.appendText msg
.addnewLine 2
.appendText "Below find CSV tab-delimited Excel File: " + cdr
Set object = rtItem.embedObject( EMBED_ATTACHMENT, "",cdr)
End With
maildoc.send False
End If
If Not chkdb Is Nothing Then Set chkdb = Nothing
Exit Sub
DBDirectoryError:
On Error Goto 0
msg = "Error processing databases: " + Error + "-" + Str(Err) + " Line: " + Str(Erl)
Print msg
If sendLog Then
rtItem.addnewline 2
rtItem.appendText msg
End If
Err=0
Resume Next
End Sub
Or you can create an agent on a server database that does the collection and mails you the results:)
I have a test database where I ran this scheduled, with full administration permissions, on the server. It sends you a "comma delimited file" that will open in Excel. Be sure to add your email address, server name, and the correct path where indicated. This will collect *.nsf files.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
'ENTER YOUR SERVER NAME HERE----------------------
Const str_SERVER = "YOUR SERVER NAME"
'ENTER THE FULL PATH OF THE MAIL DIRECTORY HERE ---------
Const str_STARTDIR="E:\Domino\Da
'THIS IS THE FILE THAT WILL BE MAILED TO YOU ---------------
Const str_FILENAME="databasedir.
'YOUR EMAIL ADDRESS HERE----------------------
Const str_SENDTO ="YOUR SEND TO ADDRESS"
If db.Server = "" Then
Print "Sorry, this agent needs to run on a server"
End If
Dim chkdb As NotesDatabase
Dim processed As Long, failed As Long
Dim sendLog As Boolean
sendLog = False
Dim msg As String
Dim tb As String
tb = Chr(9)
If str_SENDTO <>"" Then
sendLog = True
End If
msg= "Starting the Db Collection agent on " + str_STARTDIR
Print msg
If sendLog Then
Dim maildoc As New NotesDocument(db)
With maildoc
.SendTo = str_SENDTO
.From = session.UserName
.Principal = session.UserName
.subject = "Database Directory File: " + str_FILENAME
End With
Dim rtItem As New NotesRichTextItem(maildoc,
With rtItem
.AddNewline 1
.appendText msg
End With
End If
Dim fileNum As Integer
Dim cdr As String
cdr = Curdir() + "\" + str_FILENAME
fileNum = Freefile
Open cdr For Output As fileNum
Write #filenum, "Title","Filename","Filepa
Dim tmp As String
tmp = Curdir()
On Error Goto DBDirectoryError
Dim fileName As String
fileName = Dir$(str_STARTDIR + "*.nsf", 0)
Do While fileName <> ""
Set chkdb = New NotesDatabase(str_SERVER,s
If Not (chkdb Is Nothing) Then
If Not chkdb.IsOpen Then Call chkdb.Open("","")
If Not chkdb.IsOpen Then
msg= "Unable to open " + chkdb.Title
Print msg
If sendLog Then
rtitem.addnewLine 1
rtitem.appendText tb + msg
End If
failed = failed +1
Goto Next_Database
End If
'Collect your information here.............
With chkdb
Write #filenum, .title , .filename , .Filepath, .ReplicaID , .Server ,.TemplateName , Cstr(.FileFormat ), .DesignTemplateName
processed = processed +1
End With
If sendLog Then
rtItem.addnewLine 1
rtItem.appendText tb + "Collected: " + chkdb.title
End If
Set chkdb = Nothing
End If
Next_Database:
fileName = Dir$()
Loop
Close #filenum
msg = "Finished processing " + Cstr(processed) + " databases. Failed to process: " + Cstr(failed)
Print msg
ExitHere:
If sendLog Then
Dim object As NotesEmbeddedObject
With rtItem
.addnewline 2
.appendText msg
.addnewLine 2
.appendText "Below find CSV tab-delimited Excel File: " + cdr
Set object = rtItem.embedObject( EMBED_ATTACHMENT, "",cdr)
End With
maildoc.send False
End If
If Not chkdb Is Nothing Then Set chkdb = Nothing
Exit Sub
DBDirectoryError:
On Error Goto 0
msg = "Error processing databases: " + Error + "-" + Str(Err) + " Line: " + Str(Erl)
Print msg
If sendLog Then
rtItem.addnewline 2
rtItem.appendText msg
End If
Err=0
Resume Next
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have some designing skill you would be able to import it into a db view and then export to excel or print.
otherwise if you just need the filenames and not the title you can add a column to a new view with the mailfile field specified, and this will then be exportable.
Hope this helps