Automate downloading Lotus Notes Attachment to folder on daily basis

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?
flyguy80Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

doninjaCommented:
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
flyguy80Author Commented:
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...
doninjaCommented:
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

flyguy80Author Commented:
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?
flyguy80Author Commented:
Also, is there no way I can do this straight from Access VBA?
flyguy80Author Commented:
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
flyguy80Author Commented:
Attached is the error when I try to run this code from Access VBA
Access-Error.bmp
mbonaciCommented:
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
doninjaCommented:
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.

mbonaciCommented:
Since this is a mail database, there's really no point in doing this on a local replica, is there?
doninjaCommented:
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.

flyguy80Author Commented:
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
flyguy80Author Commented:
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
mbonaciCommented:
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...
doninjaCommented:
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.


mbonaciCommented:
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...
flyguy80Author Commented:
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?
mbonaciCommented:
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...
flyguy80Author Commented:
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
doninjaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbonaciCommented:
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
mbonaciCommented:
Hmm, not even a split?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.