Solved

Automate downloading Lotus Notes Attachment to folder on daily basis

Posted on 2010-08-24
22
5,669 Views
Last Modified: 2013-12-18
I will be getting on a daily basis an e-mail in Lotus Notes from a vendor. I want to automate on a schedule the to download the attachment from the e-mail to a specific folder. My Microsoft Access Database will import this file using VBA. Any help here?
0
Comment
Question by:flyguy80
  • 9
  • 7
  • 6
22 Comments
 
LVL 10

Expert Comment

by:doninja
Comment Utility
You should be able to access the attachment in the document using either the lotusscript class notesdocument.getattachment(filename) and then use object.extractfile method to save to any folder you want.

If you don't know the exact name of the file or if it potentially changes then use notesdocument.embeddedobjects to look for every attachment or embedded file in the document.

If you look at designer helpfile at EmbeddedObjects property it has a reasonable example of how to get the objects array, then just add in the .extractfile
0
 

Author Comment

by:flyguy80
Comment Utility
What program or script do I write in order to do this? This isn't helpful without a start on how to do this. I am a beginner and need to know at least if I am supposed to use vbscript or what and if so, I need a syntax example...
0
 
LVL 10

Expert Comment

by:doninja
Comment Utility
To acomplish this you can use the Lotus Domino Designer client and write this in LotusScript.
Sorry thought you where a developer or had some dev experience so would know some of the basics from your Access VBA programming.
You may need to talk to your IT Admin to get the Designer client installed and to ensure it is the same version as your Notes Installation.

Once installed it includes a number of good designer focused help files which will give you example code on the above LotusScript classes.
Lotusscript is very similar to vb just a few syntax and different class names.
0
 

Author Comment

by:flyguy80
Comment Utility
1) Is there a website I can just download this Domino Designer program? I googled Lotus Domino Desiggner client 7 and no results, just documentation.

2) Also, can I have it run on a schedule once I figure out the code?

3) Does my PC have to be on in order for this to run?
0
 

Author Comment

by:flyguy80
Comment Utility
Also, is there no way I can do this straight from Access VBA?
0
 

Author Comment

by:flyguy80
Comment Utility
I saw this code on another question but got an error when trying to run it in my module:

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim Attachment          As Object

Dim FileName            As Variant

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CurrentDatabase

'Access my Inbox
Set NotesView = NotesDatabase.GetView("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GetLastDocument

'Get the attachment
'Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")

FileName = NotesDocument.GetItemValue("$FILE")
Set Attachment = NotesDocument.GetAttachment(FileName)

Call Attachment.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\l" & FileName)
   
   
MsgBox "4 Week Summary has been extracted successfully", vbInformation


End Sub
0
 

Author Comment

by:flyguy80
Comment Utility
Attached is the error when I try to run this code from Access VBA
Access-Error.bmp
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
Why are you doing the whole thing from VBA?
Split it in two parts:
    1. create scheduled agent in Domino Designer that would act on new mails from your sender
    2. from Access, you simply check the folder periodically and remove the files after you import them

Here's the solution for no 1:
http://www.experts-exchange.com/Q_21768578.html
0
 
LVL 10

Expert Comment

by:doninja
Comment Utility
Not tried VBA to Notes but the code may not be getting the right view name.
Try "($Inbox)" including the brackets or "Inbox" without the $ as the first is the proiper name and second the alias
On your shot I see the code and highlighted line but not any error number etc ?

For domino designer you can get version 8.5 direct from the Lotus site but this may not be compatible with your main lotus notes client so would advise asking who supplied your clinet software initally.

In Lotus Script agent yes you can set agent up to run on a schedule even if on a local machine.
If not on a server then of course the client software will have to be running all of the time.
There is also a setting in the client preferences to enable local schedule agents.

0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
Since this is a mail database, there's really no point in doing this on a local replica, is there?
0
 
LVL 10

Expert Comment

by:doninja
Comment Utility
if person does not have server rights or if the VB script that requires the attachments cannot run on the server or disk access to server restricted etc

But yes you are right, would be ideal to have as a server based agent that saves locally on the mail server and then VBaccess pulls files across the network.

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:flyguy80
Comment Utility
So I create a scheduled agent on Domino Designer to run this script, correct? The creation of the agent should be done by the server team in IT, correct? I can't have this locally and then run unattended? Also, I don't want to get t from sent mail, rather but from inbox.

Thanks

Sub OpenEmail()

Dim NotesSession        As Object
Dim NotesDatabase       As Object
Dim NotesView           As Object
Dim NotesDocument       As Object
Dim RichTextItem        As Object
Dim Attachment          As Object

Dim FileName            As Variant

'Initialise Session
Set NotesSession = CreateObject("notes.notesSession")
Set NotesDatabase = NotesSession.CurrentDatabase

'Access my Inbox
Set NotesView = NotesDatabase.GetView("$Inbox")

'Get the sent email
Set NotesDocument = NotesView.GetLastDocument

'Get the attachment
'Set RichTextItem = NotesDocument.GETFIRSTITEM("Body")

FileName = NotesDocument.GetItemValue("$FILE")
Set Attachment = NotesDocument.GetAttachment(FileName)

Call Attachment.EXTRACTFILE("\\filwlg02\InternetChan\IOLB\Fraud Reporting\Suspect Sessions\l" & FileName)
   
   
MsgBox "4 Week Summary has been extracted successfully", vbInformation


End Sub
0
 

Author Comment

by:flyguy80
Comment Utility
In case I wasn't clean on the previous comment:

Can I setup on Domino Designer that any e-mail that comes in to automatically download the attachment to a network folder?

or do I need the script above to do this? Sorry if i'm confusing everyone, i am a beginner with Lotus Notes
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
In Lotus, there is a type of agent that acts on all newly received e-mail messages.
That's set in agent properties, when you create (or edit) an agent in Domino Designer.

That agent runs every now and then (5 or 10 minutes, which can be changed) and checks whether any new e-mails have arrived since the last time it ran. If yes - it runs the code you entered in agent's Initialize event (you have to choose LotusScript for an agent in order to see its events).

Since the code you posted is the VB(A) type of code, that uses late-binding (vars are declared as Object instead of actual Lotus class), use the link I provided to get the LotusScript code that's written for Lotus.

Ask when you get stuck...
0
 
LVL 10

Expert Comment

by:doninja
Comment Utility
As for running on the server, yes you can get the IT team to put the agent onto the server to run using "Allow Restricted Operations" Rights.
Depending on how the server is configured most server based agents will only save to a folder on the same machine. Unless the service has been configured to run as a network account which is not a standard config in most environments.

As for you comment as to getting mail from inbox not sent mail, then using the code above (mbonaci link), if you put in an agent that is on event, "after new mail has arrived", then it will only run on messages that are received not sent.
If as a scheduled agent then you will have to change the code to look in specific folder and mark documents once they have been processed.


0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
But, if I understood right, flyguy wants to run only on incoming messages (from specific vendor), not on the outgoing ones.

Start working on it and we'll help you along...
0
 

Author Comment

by:flyguy80
Comment Utility
fellow programmers,

There might be a possibility that I can't go the route with the Domino Designer since I work in a corporation and that limits me of working with that technology (Lotus Notes team). I work in just databases.

Is there an alternative using solely Microsoft Access VBA? should I change the zone to something that more closely is towards Access VBA to go to the mailbox on my Lotus Notes?
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
Here's how you can read your e-mails and extract attachments using VBA:

    http://www.experts-exchange.com/Q_20760728.html

You'll just have to schedule that in some way, if you want it to be a periodic instead of an on-demand process...
0
 

Author Comment

by:flyguy80
Comment Utility
Thank you mbonaci.

Ok, so I went to the link you posted and I started to read from the top, looks good so far, copied the code and pasted it into the VBA Editor in Access. I started the debug mode and went line by line. Now I was prompted at the line for my password, as you can see in the first screenshot:

"Set dbNotes = appNotes.GetDatabase("Enter Server Name Here", "Enter Path\MailDatabase name here")"

So I entered in my password and then on the line I got an error message as you can see on the 2nd screenshot:

Set vwInbox = dbNotes.GetView("$Inbox")

1) It looks like I am supposed to put in the Server Name and path to my email. How would I find out what the server name is? and is the path C:\notes\data\t94hgey.id?

2) Once you explain to me the answer to #1, how can I have it NOT prompt me for password. I want this to be automated.

Thanks
Screenshot1.bmp
Screenshot2.bmp
0
 
LVL 10

Accepted Solution

by:
doninja earned 500 total points
Comment Utility
1. the path C:\notes\data\t94hgey.id is your NotesID which contains your certificate toa ccess the Domino Environment.

To find your Domino server and path using a Lotus notes Client.
Open your mail using Lotus Notes. In version 8.5+ select File..Application..Properties 8 or below it is File..Database..Properties
In window that pops up the first tab should have a value Server and Filename which you can put in your code.

0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
To avoid password prompt you can call Initialize session like this:

    Set sess = CreateObject( "Lotus.NotesSession" )
    Call sess.Initialize( password )

See accepted solution here for complete code that collects current user's mail file location and server from Notes.ini config file, opens mail file, looks into Inbox folder and iterates through documents:

    http://www.experts-exchange.com/Q_24346997.html#24223342
0
 
LVL 22

Expert Comment

by:mbonaci
Comment Utility
Hmm, not even a split?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Are you having trouble connecting or getting your iPhone / Samsung device(s) to sync with Microsoft Exchange Server?   What have you tried?   What haven't you tried?
This article is essential to make secure Yahoo Mail connection without facing any issue. It is providing simple steps to configure your Yahoo Mailbox to Hard drive using Microsoft Outlook.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now