Read mail's Subject

[Maurizio]

Hi all,
is it possible read, with vba, all "Subject" from my received mails (Outlook Express 6.0) on 05/05/2003 and report them on a sheets?

Tia.

 

[Juan Pablo González]

I think you can just use

Cells(i, 1) = MItem.Subject
Cells(i, 2) = MItem.From
Cells(i, 3) = MItem.ReceivedTime

 

[Legacy 15162]

still only pulling subject?

 

[Juan Pablo González]

Can you post your current code ?

 

[Legacy 15162]

Sub ReadFromOutlook()
Dim OutApp As Object 'Outlook.Application
Dim NmSpace As Object 'Outlook.NameSpace
Dim Inbox As Object 'Outlook.MAPIFolder
Dim MItem As Object 'Outlook.MailItem
Dim MySubFolder As Object
Dim i As Long

Set OutApp = CreateObject("Outlook.Application")
Set NmSpace = OutApp.GetNamespace("MAPI")
Set Inbox = NmSpace.GetDefaultFolder(6) 'olFolderInbox
Set MySubFolder = Inbox.Folders("Bellsouth") ' Note Case Sensitive!
i = 1

'// In case of UNDELIVERABLES
On Error Resume Next
'For Each MItem In Inbox.Items
For Each MItem In MySubFolder.Items
If Int(MItem.ReceivedTime) <= month() Then
If Err Then Err.Clear: GoTo N
i = i + 1
Cells(i, 0) = MItem.Subject
Cells(i, 1) = MItem.From
Cells(i, 2) = MItem.ReceivedTime
End If
N: Next MItem

Set MItem = Nothing
Set Inbox = Nothing
Set NmSpace = Nothing
Set OutApp = Nothing
Set MySubFolder = Nothing

End Sub

 

[Ivan F Moala]

You need to have a look @ Debugging code as you would have found a number of Errors in your set-up
By removing the On Error and Stepping through the code you would have found some objects weren't set........

F1 = Help does help sometimes

any way try this.....

<font face=Courier New>Sub OlEmailDetails()

<SPAN style="color:darkblue">Dim</SPAN> OutApp <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>                        <SPAN style="color:green">'Outlook.Application</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> NmSpace <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>                       <SPAN style="color:green">'Outlook.NameSpace</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> Inbox <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>                         <SPAN style="color:green">'Outlook.MAPIFolder</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> MItem <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>                         <SPAN style="color:green">'Outlook.MailItem</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> MySubFolder <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>

<SPAN style="color:darkblue">Set</SPAN> OutApp = CreateObject("Outlook.Application")
<SPAN style="color:darkblue">Set</SPAN> NmSpace = OutApp.GetNamespace("MAPI")
<SPAN style="color:darkblue">Set</SPAN> Inbox = NmSpace.GetDefaultFolder(6)      <SPAN style="color:green">'olFolderInbox</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MySubFolder = Inbox.Folders("Bellsouth") <SPAN style="color:green">' Note Case Sensitive!</SPAN>

i = 1

<SPAN style="color:green">'// In case of UNDELIVERABLES</SPAN>
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> MItem <SPAN style="color:darkblue">In</SPAN> MySubFolder.Items
    <SPAN style="color:darkblue">If</SPAN> Month(MItem.ReceivedTime) <= Month(Now) <SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">If</SPAN> Err <SPAN style="color:darkblue">Then</SPAN> Err.Clear: <SPAN style="color:darkblue">GoTo</SPAN> N
        i = i + 1
        Cells(i, 1) = MItem.Subject
        Cells(i, 2) = MItem.SenderName
        Cells(i, 3) = MItem.ReceivedTime
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
N: <SPAN style="color:darkblue">Next</SPAN> MItem

<SPAN style="color:darkblue">Set</SPAN> MItem = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> Inbox = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> NmSpace = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> OutApp = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MySubFolder = <SPAN style="color:darkblue">Nothing</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>


</FONT>

 

[Juan Pablo González]

Ivan beat me to it, but I had the reply ready, so here it is:

Well, you changed the code a lot (Well, not a lot :wink: )... for example:

If Int(MItem.ReceivedTime) <= Month() Then

This line is producing an error. What are you trying to do here ?

and this one

            Cells(i, 0) = MItem.Subject

That's also producing an error. I didn't use a , 0 in the code I gave... should start with a 1 (As in the first column)

Anyway, I think that what you're trying to do is get the mails from the subfolder Bellsouth, that have arrived this month, right ?

Oh, I had an error too , there is no From property... I should have checked that before... sorry.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> ReadFromOutlook()
    <SPAN style="color:#00007F">Dim</SPAN> OutApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN> <SPAN style="color:#007F00">'Outlook.Application</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> NmSpace <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>   <SPAN style="color:#007F00">'Outlook.NameSpace</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Inbox <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN> <SPAN style="color:#007F00">'Outlook.MAPIFolder</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> MItem <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN> <SPAN style="color:#007F00">'Outlook.MailItem</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> MySubFolder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> OutApp = CreateObject("Outlook.Application")
    <SPAN style="color:#00007F">Set</SPAN> NmSpace = OutApp.GetNamespace("MAPI")
    <SPAN style="color:#00007F">Set</SPAN> Inbox = NmSpace.GetDefaultFolder(6)    <SPAN style="color:#007F00">'olFolderInbox</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> MySubFolder = Inbox.Folders("Bellsouth")    <SPAN style="color:#007F00">' Note Case Sensitive!</SPAN>
    i = 1

    <SPAN style="color:#007F00">'// In case of UNDELIVERABLES</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#007F00">'For Each MItem In Inbox.Items</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> MItem <SPAN style="color:#00007F">In</SPAN> MySubFolder.Items
        <SPAN style="color:#00007F">If</SPAN> Month(MItem.ReceivedTime) >= Month(Date) And _
           Year(MItem.ReceivedTime) >= Year(Date) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Err <SPAN style="color:#00007F">Then</SPAN> Err.Clear: <SPAN style="color:#00007F">GoTo</SPAN> N
            i = i + 1
            Cells(i, 1) = MItem.Subject
            <SPAN style="color:#007F00">'Cells(i, 2) = MItem.From</SPAN>
            Cells(i, 2) = MItem.SenderName
            Cells(i, 3) = MItem.ReceivedTime
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
N:         <SPAN style="color:#00007F">Next</SPAN> MItem

    <SPAN style="color:#00007F">Set</SPAN> MItem = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Inbox = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> NmSpace = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> OutApp = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> MySubFolder = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[Legacy 15162]

works like a charm! now, how can i route this to public folders not located in my inbox....I promise you, once I get this completed I will be able to do this on my own from now on and chime in when others have similar issues!

 

[Legacy 15162]

can anyone help out with routing to a personal outlook folder on my H: or routing to a public folder located in outlook?

 

[Craig92604]

Wow! This code is great! Anyway to look at the entire inbox and report From, To, Date, subject, and category?

Thank you for this, I have been thinking on how to do this for months but have not been able come up with a good way of doing it.

Thanks,

Craig

 

[Legacy 15162]

for me, it might be the greatest thing since sliced bread!