Identify specific email from Excel (by sender, date sent, and subject line)

[Dr. Demento]

If I need to create a loop that looks thru each item in my Outlook Sent folder and then evaluates it based on who the sender was and whether or not it's the original email (defined by no "RE:" or "FW:" in subject line and it was sent today), how would I go about this??

I tried something like this, but it fails (Error: RTE 287: Application-defined or object-defined error). I didn't include the date test in my example, so if you have some suggestions, I'd be thrilled.

Dim objOutlook As New Outlook.Application  ' Early binding
Dim OLNameSpace As Outlook.Namespace
  Set OLNameSpace = Outlook.GetNamespace("MAPI")
Dim OLFolder As Outlook.Folder, _
    OLFolder_Sent As Outlook.Folder
  Set OLFolder = OLNameSpace.GetDefaultFolder(olFolderInbox)
  Set OLFolder_Sent = OLNameSpace.GetDefaultFolder(olFolderSentMail)
Dim OLMail As Outlook.MailItem, _
    OLMail_items As Outlook.MailItem

For Each OLMail_items In OLFolder_Sent.Items
If InStr(OLMail_items.Subject, "RE:") = 0 And _
         InStr(OLMail_items.Subject, "FW:") = 0 And _
         [COLOR=#0000ff][B]OLMail_items.SenderEmailAddress[/B][/COLOR] = "[EMAIL="Dr_Demento@aol.com"]Dr_Demento@CompuServe.com[/EMAIL]" then

[I]do stuff
[/I]
Exit For
End If
Next OLMail_items

I realize this isn't a full loop, I just need some help with the bolded section. I get the feeling my issue is d/t which variable types I'm using but I'm too inexperienced to know what I should use.

Thanks y'all!

 

[Domenic]

Your code seems to work fine. The only problem I see is that you would get a type mismatch error if your folder contains items other than mail items, such as meeting items and appointment items. So your error must be caused by some other line in your code.

 

[Dr. Demento]

Thanks Domenic. I thought the Sent folder would only have email items. I'll clear out my folder tomorrow and give it a try.

Barring that, any suggestions on adapting the code to allow multiple item types?

Also, which property should I use to test when it was sent?

Thanks much.

 

[Domenic]

There's no need to clear out your folder. Simply declare your For Each control variable as a Variant, and test for a mail item using TypeName...

    Dim olApp As New Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFolder As Outlook.Folder
    Dim vItem As Variant
    
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderSentMail)
    
    For Each vItem In olFolder.Items
        If TypeName(vItem) = "MailItem" Then
            If vItem.SenderEmailAddress = "Dr_Demento@CompuServe.com" Then
                If InStr(vItem.Subject, "RE:") = 0 And InStr(vItem.Subject, "FW:") = 0 Then
                    'etc
                    '
                    '
                End If
            End If
        End If
    Next vItem

Actually, here's another way, which should be more efficient...

    Dim olApp As New Outlook.Application
    Dim olNS As Outlook.Namespace
    Dim olFolder As Outlook.Folder
    Dim olItems As Outlook.Items
    Dim vItem As Variant
    
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderSentMail)
    Set olItems = olFolder.Items.Restrict("[SenderEmailAddress] = 'Dr_Demento@CompuServe.com'")
    
    For Each vItem In olItems
        If TypeName(vItem) = "MailItem" Then
            If InStr(vItem.Subject, "RE:") = 0 And InStr(vItem.Subject, "FW:") = 0 Then
                'etc
                '
                '
            End If
        End If
    Next vItem

To test when it was sent on...

vItem.SentOn

Hope this helps!

 

[Dr. Demento]

Domenic,

I was having some issues until I structured the Restrict statement like an example done by The Scripting Guys. Don't know why that would make a difference using a separate assignment for olItems, but when I tried to use your example the loop was skipped entirely Ah well, I'll take it!! I appreciate your time/efforts.

And for anyone who has a similar question in the future, to ensure that I'm only looking at emails sent today, the vItem.SentOn includes a time component and therefore I couldn't get a true condition until I used DateValue:

DateValue(vItem.SentOn) = DateValue(Date)

Thanks again, Domenic! I love this forum!!!!

 

[Domenic]

Don't know why that would make a difference using a separate assignment for olItems...

It shouldn't make a difference. I did test my code before posting it. But you've got it working so that's great.

Thanks again, Domenic!

You're very welcome! Glad I could help!

Cheers!

 

[Dr. Demento]

One last question, Domenic.

How do I get a ReplyAll of the email once I've identified it? I have tried using With vItem.ReplyAll but all that does is create a new email with the same distro list and RE: in the subject line. I want to capture the text of the original email as well -- you know, like a real Reply!

Have a great weekend!

 

[Domenic]

Have a look at your options and make sure that the appropriate one is selected...

File > Options > Mail > Replies and forwards > When replying to a message > Include original message text

 

[Dr. Demento]

That's the option I have selected I tried as Dmitry suggested here but instead of my new text above the original text, I get the original text only.

I will open a new post given the new difficulties and will post the code that I'm using (rather than keep you guessing!)

Thanks much, Domenic!