PDA

View Full Version : Open an email Lotus notes and copy the content to excel



srvnn1981
12-24-2013, 01:35 PM
I am very new and totally blank about VBA,I was wondering whether is it possible to copy content of an email from lotus notes and paste it to a worksheet. I searched a lot but every where I can see a code to send email.Below are my requirements,1. Open an email from a folder (I have Main folder with name "Timesheet" under which I have many sub folders "resource 1", "resource 2" etc)2. The content of the email is simple (3 columns , 8 rows for each day of the week and one row which has total of hours)3. I just want to copy Name from the subject, dates which is also in subject, total hoursKindly help me...

westconn1
12-25-2013, 04:18 PM
i have no experieance with notes, but here is an example i found in some other forum

Public Sub Get_Notes_Email_Text()

Dim NSession As Object 'NotesSession
Dim NMailDb As Object 'NotesDatabase
Dim NDocs As Object 'NotesDocumentCollection
Dim NDoc As Object 'NotesDocument
Dim NNextDoc As Object 'NotesDocument
Dim NItem As Object 'NotesItem
Dim view As String
Dim filterText As String

view = "$All" 'Name of view or folder to retrieve documents from
filterText = "" 'Optional text string to filter the view

Set NSession = CreateObject("Notes.NotesSession")
Set NMailDb = NSession.GETDATABASE("", "") 'Default server and database
If Not NMailDb.IsOpen Then
NMailDb.OPENMAIL
End If

Set NDocs = NMailDb.GETVIEW(view)
NDocs.Clear

'Apply optional filter

If filterText <> "" Then
NDocs.FTSEARCH filterText, 0
End If

Set NDoc = NDocs.GETFIRSTDOCUMENT
Do Until NDoc Is Nothing
Set NNextDoc = NDocs.GETNEXTDOCUMENT(NDoc)
Set NItem = NDoc.GETFIRSTITEM("Body")
If Not NItem Is Nothing Then
MsgBox prompt:=NItem.Text, Title:=NDoc.GETITEMVALUE("Subject")(0)
End If
Set NDoc = NNextDoc
Loop

End Sublooks like it should at least get you started, for more information the original post is
it seems, i am unable to post the url, code posted originally by john_w

srvnn1981
12-26-2013, 01:45 PM
I get run time error "91" Object variable or run time variable not defined :(( with above code near Set NDocs = NMailDb.GETVIEW(view) NDocs.Clear

ukyank
12-26-2013, 03:34 PM
srvnn do you have devloper access to your notes email account?

srvnn1981
12-27-2013, 08:21 AM
I am not sure... what is that... How to check it? But using this forum I was able to use one code and send email using VBA without even opening lotus notes.

westconn1
12-27-2013, 10:43 PM
But using this forum I was able to use one code and send email using VBA without even opening lotus notes.there are many ways to send emails, did the code actually use lotus notes at all?

if the code gets as far as Set NDocs = NMailDb.GETVIEW(view), before error, then i would assume that you either have developer access or it is not relevant

see http://www.vbaexpress.com/forum/showthread.php?37541-Move-Lotus-Notes-mail-to-a-folder-in-Lotus-Notes-through-VBA
which i found on a google search, along with several hundred results lotus notes vba read email

srvnn1981
01-06-2014, 02:00 PM
Thanks Westconn1, for the reply!The code which I used to send email did not use LN, instead it sent out email using COM. (this is what the forum said)Coming back to my requirement, Using other code from this forum I was able to connect to LN (I hope so..)The code does retrive something, but not sure what. I don't see the results,

Sub GetFiles()
Dim Session As Object
'NotesSession'
Dim Session As New NotesSession
Dim db As Object 'NotesDatabase
Dim dc As Object 'NotesDocumentCollection
Dim doc As Object
'NotesDocument
Dim NextDoc As Object
'NotesDocument
Dim i As Long
Dim AttachEmbed As Boolean
Dim MyArray, Item
Dim FName As String
Dim Values As New Collection
Set Session = CreateObject("Notes.NotesSession")
Set db = Session.GetDatabase("DWA77512EN/VGI", "mail\SANANDA.nsf")
If Not db.IsOpen Then
MsgBox "Invalid Path"
Exit Sub
End If
Set dc = db.AllDocuments
Set doc = dc.GetFirstDocument
Do Until doc Is Nothing
y = y + 1
Set NextDoc = dc.GetNextDocument(doc)
'cache the next one in case we lose track of position due to a delete
If doc.HasEmbedded = True And doc.GetItemValue("Subject")(0) = "Win 7" Then
x = x + 1
Sheet1.Cells(x, 1) = doc.Created
Sheet1.Cells(x, 2) = doc.HasEmbedded
Sheet1.Cells(x, 3) = doc.GetItemValue("Subject")(0)
End If
Set doc = NextDoc
'retrieve from cache
Loop
End Sub

westconn1
01-07-2014, 02:31 AM
as i can not in any way test the code, i can only guess as to what may work

you can try a msgbox to find how many documents in the database

msgbox dc.count ' i would assume that a collection will have a count property
if the count is 0 or very low i would guess you have connected to an incorrect database, and be looking as to what other database may be available, comparing size of database or last modified file date
otherwise i would have to look at the criteria used for selecting the excel results

if you can set a reference to lotus notes, intellisense should work and may help with properties and methods for dimensioned lotus variables
though if you do not have a reference to notes i would expect Dim Session As New NotesSession to give an error, that being the case you can use the notes defined types for your variables

Dim db As Object 'NotesDatabase
Dim dc As Object 'NotesDocumentCollection
Dim doc As Object 'NotesDocument
Dim NextDoc As Object can all be defined as Notes types, most of which are already there for you to use