Solved

Automate downloading Lotus Notes Attachment to folder on daily basis

Posted on 2010-08-24
22
6,675 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 6
22 Comments
 
LVL 10

Expert Comment

by:doninja
ID: 33512749
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
ID: 33512918
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
ID: 33513412
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:flyguy80
ID: 33514777
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
ID: 33514790
Also, is there no way I can do this straight from Access VBA?
0
 

Author Comment

by:flyguy80
ID: 33515073
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
ID: 33515198
Attached is the error when I try to run this code from Access VBA
Access-Error.bmp
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 33518953
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
ID: 33519213
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
ID: 33519230
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
ID: 33519318
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
 

Author Comment

by:flyguy80
ID: 33525659
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
ID: 33525700
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
ID: 33529066
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
ID: 33529754
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
ID: 33529933
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
ID: 33532339
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
ID: 33532409
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
ID: 33532796
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
ID: 33533010
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
ID: 33539944
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
ID: 33876489
Hmm, not even a split?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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