Reading Outlook subject and date 2

[petermeachem]

   Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.Folders("Mailbox - Applications").Folders("Inbox")
    Set olMail = olFolder.Items.GetFirst
    jNoEmails = olFolder.Items.Count
        For J = 1 To jNoEmails
         Set olMail = olFolder.Items(J)
         If Format(olMail.CreationTime, "dd mmm yyyy") = Format(Now(), "dd mmm yyyy") Then
             A = olMail.SenderName
            c = olMail.body
            jCount = jCount + 1
         Else
          Exit For
         End If
    Next J
    
      Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing

I've not tried to do this before. Every thing I've googled says something like this should work, in my case not. It stops on the Set olMail = olFolder.Items(J) line, currently consistently for j = 7 but it has varied throughout the day. The Applications mailbox has 1400 records and it is a mailbox added to my outlook. the error is 'Type mismatch'. olFolder.Items(J) contains the subject line, same as on the previous records.
What it is doing is stopping where the from address is not Applications. Most of the emails are from Applications, unfortunately i'm trying to read bounces which won't be.
Can anyone come up with an explanation? Exchange is 2008, my outlook is 2003 and this mailbox is an additional mailbox.

 

[ClulessChris]

How about this

Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.Folders("Mailbox - Applications").Folders("Inbox")
    For Each oMail olFolder.Items.Count
        If Format(olMail.CreationTime, "dd mmm yyyy") = Format(Now(), "dd mmm yyyy") Then
            A = olMail.SenderName
            c = olMail.body
         Else
             Exit For
         End If
    Next 

    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ClulessChris]

Or perhaps more efficent:

Set olApp = new Outlook.Application
Set olNSpace = olApp.Session
Set olFolder = olNSpace.GetDefaultFolder(olFolderInbox)
dteToday = Date
sFilter = "[SentOn] > '" & dteToday & " 00:01' AND [SentOn] < '" dteToday & " 23:59'"
For Each olMail in olFolder.Items.Restrict(sFilter)
    A = olMail.SenderName
    b = olMail.Body
Next

If not olFolder is Nothing then set olFolder = Nothing
If not olNSpace is Nothing then set olNSpace = Nothing
If not olApp is Nothing then set olApp = Nothing

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[petermeachem]

in your first example if changed the For line to 'For Each olMail In olFolder.Items'
This does the same thing, it stops at the 5th email which is the first one not from Applications@. if i move it on to the next email it will work until i get to the next one not from Applications@. Problem is that the only emails it doesn't work on are the ones i am trying to read.

 

[ClulessChris]

Ah sorry I didn't get that from your original post. How have you declared your variables?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[petermeachem]

    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem

 

[ClulessChris]

Try

    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail 'As Outlook.MailItem

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[petermeachem]

then it would have no idea what olMail.SenderName meant

 

[ClulessChris]

did you try it?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[petermeachem]

yes. it is effectively a structure, supposed to be. if you don't tell it what sort of structure it is how does it know what the members are

 

[ClulessChris]

Defining a variable with no specified datatype defaults the datatype as Nothing (allowing that Option Strict isn't applied, if it is the code will fail).

The variable is then free to assume the datatype appropriate to the item in the .items collection.
This happens at the start of the For Loop.
As the variable doesn't have a set datatype your VBA intellisence will not auto complete your code for you, so you need to understand your object model. I've always found this to work as the variable can now cope with mail items of differing types.
I'm sorry that this solution hasn't worked for you and wish good luck if finding a solution. Please when (as I'm sure you will) find a solution please post the answer back here.
Regards

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[PHV]

Defining a variable with no specified datatype defaults the datatype as Nothing
In fact the variable is implicitely defined as Variant.

 

[PHV]

Furthermore, don't use olMail as variable name as it is an enumerated constant of OlObjectClass.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[petermeachem]

I don't suppose anyone has any idea why this doesn't work? I'm stumped

 

[PHV]

What about this ?

Dim OA As Outlook.Application
Dim NS As Outlook.NameSpace
Dim MF As Outlook.MAPIFolder
Dim MI As Variant    'Outlook.MailItem
Set OA = New Outlook.Application
Set NS = OA.GetNamespace("MAPI")
Set MF = NS.Folders("Mailbox - Applications").Folders("Inbox")
For Each MI In MF.Items
    If MI.Class = olMail Then
        If Format(MI.CreationTime, "dd mmm yyyy") = Format(Now(), "dd mmm yyyy") Then
            A = MI.SenderName
            c = MI.Body
        End If
    End If
Next
Set MF = Nothing
Set NS = Nothing
Set OA = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[petermeachem]

sadly not. If MI.Class = olMail Then, olMail isn't set to anything. if i take out that line and it's endif, then the first record gets read. it trips on A = MI.SenderName saying Object doesn't support this property or method

 

[PHV]

And this ?
If MI.Class = 43 Then ' 43=olMail

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[petermeachem]

Ah ha! Progress

     Dim OA As Outlook.Application
Dim NS As Outlook.NameSpace
Dim MF As Outlook.MAPIFolder
Dim MI As Variant   'Outlook.MailItem
Set OA = New Outlook.Application
Set NS = OA.GetNamespace("MAPI")
Set MF = NS.Folders("Mailbox - Applications").Folders("Inbox")
For Each MI In MF.Items

     jCount = jCount + 1
'   If MI.Class = 43 Then
    
    
    '    If Format(MI.CreationTime, "dd mmm yyyy") = Format(Now(), "dd mmm yyyy") Then
 '           A = MI.SenderName
  '          c = MI.body
                                  rs.AddNew
  '          A = MI.SenderName
            rs("subject") = MI.Class & " " & MI.Subject
            rs.Update
     '   End If
 '   End If
Next
Set MF = Nothing
Set NS = Nothing
Set OA = Nothing

EmailId Subject
1595 43 DO NOT REPLY:contract number 123- Reg Number somereg
1596 46 Undeliverable: DO NOT REPLY:contract number 123- Reg Number somereg
1597 43 DO NOT REPLY:contract number 123- Reg Number somereg
1598 46 Undeliverable: DO NOT REPLY:contract number 123- Reg Number somereg
1599 46 Delivered: FW: contract number 123- Reg number somereg
1600 46 Delivered: FW: contract number 123- Reg number somereg
1601 46 Undeliverable: DO NOT REPLY:contract number 123- Reg Number somereg

and if i comment back in the date selection i get todays only.

That's brilliant and a little odd. Thankyou so much. I've not been on tek-tips for ages, i remember you though