Extending an application for importing mails from MSOutlook to Excel

I have this code which I use to list outlook emails from the Inbox to excel.

Every time I run this code it adds the same data below the already imported data.

Can someone modify this code so that it searches for already listed data and only add new data?

Also, if any of the listed data is no longer available in Outlook then that row be deleted.
Sub Listmails()
Dim ol As Outlook.Application
Set ol = GetObject(, "outlook.application")
processFolder ol.ActiveExplorer.CurrentFolder
End Sub



Private Sub processFolder(ByVal oParent As Outlook.MAPIFolder)

        Dim oFolder As Outlook.MAPIFolder
        Dim oMail As Outlook.MailItem

        For Each oMail In oParent.Items
        If Not oMail.UnRead Then
        rn = Range("A" & Rows.Count).End(xlUp).Row + 1
        Cells(rn, 1) = oMail.Sender.Name
        Cells(rn, 2) = oMail.SentOn
        Cells(rn, 4) = oMail.Subject
        Cells(rn, 4).AddComment oMail.Body
        End If
        Next

End Sub

Open in new window

LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
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.

Rgonzo1971Commented:
Hi,

Since you only want actual data why not clear the data
Private Sub processFolder(ByVal oParent As Outlook.MAPIFolder)

        Dim oFolder As Outlook.MAPIFolder
        Dim oMail As Outlook.MailItem
        Range(Range("A2"), Range("D" & Cells.Rows.Count).End(xlUp)).ClearContents
        For Each oMail In oParent.Items
        If Not oMail.UnRead Then
        rn = Range("A" & Rows.Count).End(xlUp).Row + 1
        Cells(rn, 1) = oMail.Sender.Name
        Cells(rn, 2) = oMail.SentOn
        Cells(rn, 4) = oMail.Subject
        Cells(rn, 4).AddComment oMail.Body
        End If
        Next

End Sub

Open in new window

Regards
0
Saqib Husain, SyedEngineerAuthor Commented:
No, because I shall be manually adding info to the individual rows at Cells(rn, 3). So I must work row by row.
0
Saurabh Singh TeotiaCommented:
Saqib,

If you ask me i will play with this part of logic here to get my data...

  If Not oMail.UnRead Then
        rn = Range("A" & Rows.Count).End(xlUp).Row + 1
        Cells(rn, 1) = oMail.Sender.Name
        Cells(rn, 2) = oMail.SentOn
        Cells(rn, 4) = oMail.Subject
        Cells(rn, 4).AddComment oMail.Body
        End If
        Next

Open in new window


Presently because of this line If Not oMail.UnRead Then it gets all the items which they are their in your inbox which are not read..Now i will change this line to If  oMail.UnRead assuming the new items which i will get are unread items which i'm considering news ones which i'm assuming you want to get the new values only...

Saurabh...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KimputerCommented:
It requires a total overhaul of the code, and includes some helper columns in your Excel file.
That's because you have to do two full loops now, comparing Outlook folders with existing rows (add if doesn't exist in the Excel file, flag if already exist). The second loop will check the flags, and if no flags, remove the row (clean flags for next run).
To have this work properly, you have to use an EntryID column and a flag column. Since without the EntryID column, you have no way for certain how to match an email in the folder, with a row in your excel (unless you think the sent date is accurate enough).
It will significantly slow down the whole operation (depending on size of outlook folder and excel file).
0
Saqib Husain, SyedEngineerAuthor Commented:
Saurabh

If you ask me i will play with this part of logic here to get my data...
give it a go

Presently because of this line If Not oMail.UnRead Then it gets all the items which they are their in your inbox which are not read..Now i will change this line to If  oMail.UnRead assuming the new items which i will get are unread items which i'm considering news ones which i'm assuming you want to get the new values only...
No, only read emails shall be listed. All unread shall be ignored. There would be new read emails which I need to list.
0
[ fanpages ]IT Services ConsultantCommented:
Kimputer:
"It requires a total overhaul of the code, and includes some helper columns in your Excel file.
That's because you have to do two full loops now, comparing Outlook folders with existing rows (add if doesn't exist in the Excel file, flag if already exist). The second loop will check the flags, and if no flags, remove the row (clean flags for next run).
To have this work properly, you have to use an EntryID column and a flag column. Since without the EntryID column, you have no way for certain how to match an email in the folder, with a row in your excel (unless you think the sent date is accurate enough).
It will significantly slow down the whole operation (depending on size of outlook folder and excel file)."

Alternatively, use two worksheets; the existing (&, potentially, manually-maintained) data, & an "incoming" worksheet that retrieves all the mail (as is currently the case).

At the end of the retrieval process, the data in one worksheet is compared to the data within the other, & only missing rows are transferred accordingly.

Saurabh:
Do you manually delete rows (mail items) from the existing worksheet?

Should these be reinstated during the next extraction of data from MS-Outlook, or should they remain "deleted" (absent) in the MS-Excel worksheet (only)?
0
Saqib Husain, SyedEngineerAuthor Commented:
Komputer
It requires a total overhaul of the code,
Ok
and includes some helper columns in your Excel file.
I don't think that should be necessary
That's because you have to do two full loops now, comparing Outlook folders with existing rows (add if doesn't exist in the Excel file, flag if already exist). The second loop will check the flags, and if no flags, remove the row (clean flags for next run).
Maybe, but I would not like to use excel columns as I would be having other data all over the place.
To have this work properly, you have to use an EntryID column and a flag column. Since without the EntryID column, you have no way for certain how to match an email in the folder, with a row in your excel (unless you think the sent date is accurate enough).
I think the sent date is accurate enough coupled with the Sender name
It will significantly slow down the whole operation (depending on size of outlook folder and excel file).
I am not worried about the speed as the files are not expected to exceed 50.
0
[ fanpages ]IT Services ConsultantCommented:
"I think the sent date is accurate enough coupled with the Sender name"

Why not use the internal unique identifier, oMail.EntryId?

[ https://msdn.microsoft.com/EN-US/library/office/ff866458.aspx ]
0
Saqib Husain, SyedEngineerAuthor Commented:
Fanpages,
There would be new mails since the last extraction. These should be appended to the existing list in excel.
Outdated mails would be deleted from outlook. The macro should do the same to excel.
Sent date coupled with the sender name should be good enough
I am not sure if the internal ID remains unchanged throughout the life of the message. If not then it would be a mess if emails are deleted

EDITED
0
[ fanpages ]IT Services ConsultantCommented:
"There would be new mails since the last extraction. These should be appended to the existing list in excel.
Outdated mails would be deleted from outlook. The macro should do the same to excel."

I am not sure if you missed what I was asking, but I will reply to your most recent point:

What do you mean by "outdated"?  Those that have been removed from the folder in MS-Outlook, but still exist within the MS-Excel worksheet?


What I was asking was, in light of the fact that you manually maintain entries within the MS-Excel worksheet after extraction, does this maintenance include the removal of one or more rows (messages), or just the editing of some of the contents of a row (updating a subject, or a body of an e-mail, for instance)?

If you delete a row, the corresponding e-mail message will still exist within the MS-Outlook folder.

When you re-extract (re-run the Visual Basic for Applications code), the same e-mails (previously extracted, but then deleted in MS-Excel) will be re-transferred.
0
Saqib Husain, SyedEngineerAuthor Commented:
Fanpages

What do you mean by "outdated"?  Those that have been removed from the folder in MS-Outlook, but still exist within the MS-Excel worksheet?
Yes

What I was asking was, in light of the fact that you manually maintain entries within the MS-Excel worksheet after extraction, does this maintenance include the removal of one or more rows (messages), or just the editing of some of the contents of a row (updating a subject, or a body of an e-mail, for instance)?
Deleting rows manually: Normally no, but could happen.

If you delete a row, the corresponding e-mail message will still exist within the MS-Outlook folder.
Usually this would not happen.

When you re-extract (re-run the Visual Basic for Applications code), the same e-mails (previously extracted, but then deleted in MS-Excel) will be re-transferred.
If such a scenario occurs then yes
0
Saurabh Singh TeotiaCommented:
Saqib,

You can do something like this to do what you are looking for..This will get only fresh emails from your inbox to excel..comparing basis of sender name and time to earlier entries what you got..

Sub Listmails()
    Dim ol As Outlook.Application
    Set ol = GetObject(, "outlook.application")
    processFolder ol.ActiveExplorer.CurrentFolder
End Sub



Private Sub processFolder(ByVal oParent As Outlook.MAPIFolder)

    Dim oFolder As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem
    Dim  k As Long
    Dim lrow As Long
    For Each oMail In oParent.Items


        If Not oMail.UnRead Then

            lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        

            k = Application.Evaluate("=SUMPRODUCT((A1:A" & lrow &"=""" & oMail.Sender.Name & """)*(B1:B" & lrow &"=VALUE(""" & oMail.SentOn & """)))")
            If k = 0 Then
                rn = Range("A" & Rows.Count).End(xlUp).Row + 1
                Cells(rn, 1) = oMail.Sender.Name
                Cells(rn, 2) = oMail.SentOn
                Cells(rn, 4) = oMail.Subject
                Cells(rn, 4).AddComment oMail.Body
            End If

        End If
    Next

End Sub

Open in new window


Saurabh...
0
Saqib Husain, SyedEngineerAuthor Commented:
Thanks Saurabh, this looks good although I have not tested it yet.

What about the part where it is supposed to delete the unwanted rows?
0
Saurabh Singh TeotiaCommented:
From delete rows prospective help me understand what you want to delete??

Saurabh...
0
Saqib Husain, SyedEngineerAuthor Commented:
The rows which are no longer in the outlook folder
0
Saurabh Singh TeotiaCommented:
Saqib,

For that if you ask me i will write a separate loop which just act as a checking of emails from outlook folder as for that the logic will be complete different and i won't mix this one with that..as for that you need to start with excel emails compare it with outlook which is complete different process then what you are doing right now..

Saurabh...
0
Saqib Husain, SyedEngineerAuthor Commented:
Whatever it takes.

I was going to do it myself but then I thought I could let someone else do it for me.
0
Saurabh Singh TeotiaCommented:
Saqib,

Like i said that will be a different code then this or will require extensive re-write of the code so will request you to open a part-2 of the question as that code need to be written from scratch..

Saurabh...
0
Saqib Husain, SyedEngineerAuthor Commented:
It was not a different code. I did it myself using the same code. I had to modify your formula to be able to use it. Worked ok on the first test. Will see how it behaves in the long term.

Thanks

Private Sub processFolder(ByVal oParent As Outlook.MAPIFolder)
        Dim oFolder As Outlook.MAPIFolder
        Dim oMail As Outlook.MailItem
        Dim flag(10000) As Boolean
        For Each oMail In oParent.Items
            If Not oMail.UnRead Then
               lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
                k = Application.Evaluate("=SUMPRODUCT((A1:A" & lrow & "=""" & oMail.Sender.Name & """)*(B1:B" & lrow & "=" & 1 * CVDate(oMail.SentOn) & ")*row(B1:B" & lrow & "))")
                If k = 0 Then
                    flag(k) = False
                    rn = Range("A" & Rows.Count).End(xlUp).Row + 1
                    Cells(rn, 1) = oMail.Sender.Name
                    Cells(rn, 2) = oMail.SentOn
                    Cells(rn, 4) = oMail.Subject
                    Cells(rn, 4).AddComment oMail.Body
                    flag(rn) = True
                Else
                    flag(k) = True
                End If
            End If
        Next
        For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
            If Not flag(i) Then Cells(i, 1).EntireRow.Delete
        Next i
        'If (oParent.Folders.Count > 0) Then
        '    For Each oFolder In oParent.Folders
        '        processFolder (oFolder)
        '    Next
        'End If
End Sub

Open in new window

0

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
Saqib Husain, SyedEngineerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Saqib Husain, Syed's comment #a40916048
Assisted answer: 500 points for Saurabh Singh Teotia's comment #a40914170

for the following reason:

Did the complete solution myself
0
[ fanpages ]IT Services ConsultantCommented:
"Did the complete solution myself"... after asking for advice on an approach to take, or did you use a different method than any of those described here?
0
Saqib Husain, SyedEngineerAuthor Commented:
fp
I did not ask for advice. I asked for modified code.
I agree that the solution is in line with advices given.
I also mentioned that although I could do it myself I chose to let someone else do it for me.

In the end I did produce the code which should be the "Accepted answer".

How do you suggest I close the question?
0
Saurabh Singh TeotiaCommented:
Saqib,

Their is couple of things i wanted to clarify here..that vision of EE always have been helping people to learn to do the code not do the work completely..

Thats why i started giving you suggesting what should you do and in the end did wrote the logic which does your first critical piece which you were looking for which is comparison..Now the flag logic which you wrote right now as per my understanding of the code it's not full proof because it's checking for the entries which is new..However if an old entry which get's removed i dont think the flag logic will work correctly in their and thats why i said that one needs to be written in a separate code or logic..

Now from closing the question stand point of view from answers one i guess i was the one who was closest and actually solves your problem the first one 100% and the other one i dont think so it's solved right now and you need to ask a different question for it.... Now i leave that to you how you to close it..because originally i think the way you closed it by allocating points to me..i think that was right but i will be open to if fan pages have a different view point here...

Saurabh...
1
[ fanpages ]IT Services ConsultantCommented:
Sorry...

When I reviewed the closure request I did not see the allocation (as now recorded within the "objection" text):
---
The question was going to be closed as follows:
Accepted answer: 0 points for Saqib Husain, Syed's comment #a40916048
Assisted answer: 500 points for Saurabh Singh Teotia's comment #a40914170
---

I thought you had simply requested closure without acknowledging the input from Saurabh.

If the information was there, & I missed it, I do sincerely apologise.

I was not looking for any credit, I just wanted to make sure that Saurabh received the recognition deserved.

It seems you had already accounted for that.

I will respectfully add to the "request for attention" to suggest your original intention is satisfactory.
0
Saurabh Singh TeotiaCommented:
Hey Fp..Happens :-) ..and thanks for pitching and support.. :-)

Saurabh..
1
[ fanpages ]IT Services ConsultantCommented:
Thanks... and apologies again.
0
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.