Friday, March 30, 2007

Access Lotus Notes using Perl via Win32::OLE

Are you a Lotus Notes user? Did you ever wonder how you can extract text out of Lotus Notes using Perl? Here's a script that I wrote to extract notification emails:

use Win32::OLE;

my $server;
my $database;
my $folder;

my $file = "temp_file.csv";

my $ini = "get_emails.ini";
if (-e $ini)
{
    open (INI,$ini) || die "Not able to open $ini: $!\n";
    chomp ($server = );
    chomp ($database = );
    chomp ($folder = );
}
else
{
    print "\nEnter Notes Server: ";
    chomp ($server=);
    print "\nEnter Notes Database: ";
    chomp ($database=);
    print "\nEnter Folder you want to access: ";
    chomp ($folder=);
}

#connect to the Notes database
my $Notes = Win32::OLE->new('Notes.NotesSession') || warn "Cannot start Lotus Notes Session object: $!\n";
my $Database = $Notes->GetDatabase($server, $database);

#Fetch contents of the folder
my $Response = $Database->GetView($folder);
my $Count = $Response->TopLevelEntryCount;
my $Index = $Count;

open (OUT, ">$file");

#loop through all emails
for (1..$Count)
{
    my $Document = $Response->GetNthDocument($Index--);
    my $subject = $Document->GetFirstItem('Subject')->{Text};
    my $body = $Document->GetFirstItem('Body')->{Text};
    print OUT "Subject: $subject\n",
              "Body: $body\n";
}

`start excel.exe $file`;

This works if you have Lotus Notes Client installed on your machine. As you can see the script uses OLE to access your mail database via the client. There is a way to connect to any Lotus Notes database without having to install Lotus Notes. It can be accessed using the ever popular Perl DBI module. The latter is the one that I use to synchronize Lotus Notes databases to Oracle, MySQL and SQL Server. Will discuss more about that... stay tuned.

3 comments:

Patrick Kaiser said...

Hi,

very nice stuff. seems to be helpfull, but I do not really know
which values i have to paste for $server, $database and $folder.
I have just my notes client running on windows. Can you tell me where to fetch these information?

Thanks, Pat

samdc said...

Hi Pat,



One way to find out those info is by going to the Workspace, right click on your Mail icon,
then select Database, then Properties, it then shows the following:



Server could be: SERV001/UNIT/SERVER/ORGNAME

Filename could be: mail123\samdelacruz.nsf



So based on the above, here are the values:

my $server = 'SERV001';

my $database = 'mail12\samdelacruz.nsf';

my $folder= 'AnyFolder'; #folder you setup within Lotus Notes


Chris said...

do you have the code to connect without using the lotus notes client?