search for emails in sent emails

[Marco:G]

Hi everyone, I'm here again. With another problem(at least for me).
I should check if I actually sent an email, and this works. But I noticed that if I have a lot of emails in the sent folder of outlook vs generate an error.
For me it would be enough to do a search in the emails sent during the day.

        Fldr = olNs.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        i = 1

        For Each oMsg In Fldr.Items()

            If oMsg.HTMLBody.Contains(ecnf) Then GoTo 003 Else GoTo 001

001:

        Next
        MsgBox(“Email non ok”)
        GoTo 002

003:

        i = i + 1
        If oMsg.HTMLBody.Contains(ecnf) = True Then
            inv = False
            MsgBox(“Email ok”)
            GoTo 002
            'Else
            'GoTo 001
        End If

002:
 
       'other irrelevant code

This is a error at line

For Each oMsg In Fldr.Items()

If I try to delete very old emails the code works perfectly. I need to delete last month's emails and older emails but I'd like to avoid that if possible.

[ChrisE]

I doubt if this Line would work

'...
   If oMsg.HTMLBody.Contains(ecnf) Then 
'....

should be more like

   If oMsg.HTMLBody.Contains("ecnf") Then

so what is ecnf ?

try and adapt this code for your needs

Option Strict On

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Outlook

Public Class Form1

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
        Dim objOutlook As Outlook.Application = New Outlook.Application()
        'NameSpace,Logon
        Dim objOutlookoNS As Outlook.NameSpace = objOutlook.GetNamespace("mapi")
        objOutlookoNS.Logon("Outlook", Reflection.Missing.Value, False, True)
        Dim outLookSentmails As Outlook.MAPIFolder = objOutlookoNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        Dim outLookItems As Outlook.Items = outLookSentmails.Items
        'Datatable
        Dim dtSentmails As New DataTable
        dtSentmails.Columns.Add("Sent To")
        dtSentmails.Columns.Add("Topic")
        dtSentmails.Columns.Add("Received")
        'search
        Dim drSentmails As DataRow
        For i As Integer = 1 To outLookSentmails.Items.Count
            Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
            'If myMails.To.Contains("Barbara") Then
            If myMails.UnRead Then
                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
            End If
        Next
        'add to DGV
        DataGridView1.DataSource = dtSentmails
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing
    End Sub

[Marco:G]

I doubt if this Line would work

'...
   If oMsg.HTMLBody.Contains(ecnf) Then 
'....

should be more like

   If oMsg.HTMLBody.Contains("ecnf") Then

so what is ecnf ?

try and adapt this code for your needs

Option Strict On

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Outlook

Public Class Form1

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
        Dim objOutlook As Outlook.Application = New Outlook.Application()
        'NameSpace,Logon
        Dim objOutlookoNS As Outlook.NameSpace = objOutlook.GetNamespace("mapi")
        objOutlookoNS.Logon("Outlook", Reflection.Missing.Value, False, True)
        Dim outLookSentmails As Outlook.MAPIFolder = objOutlookoNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        Dim outLookItems As Outlook.Items = outLookSentmails.Items
        'Datatable
        Dim dtSentmails As New DataTable
        dtSentmails.Columns.Add("Sent To")
        dtSentmails.Columns.Add("Topic")
        dtSentmails.Columns.Add("Received")
        'search
        Dim drSentmails As DataRow
        For i As Integer = 1 To outLookSentmails.Items.Count
            Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
            'If myMails.To.Contains("Barbara") Then
            If myMails.UnRead Then
                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
            End If
        Next
        'add to DGV
        DataGridView1.DataSource = dtSentmails
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing
    End Sub

Thank you for the answer, now I try to adapt your code.
Ecnf is a string type variable where I check if the ecnf text is contained in the sent emails, so I know that the email was sent with the ecnf date and time

[Marco:G]

Hello everyone, I couldn't solve my problem. If I have a lot of emails in sent email, it doesn't know how much to quantify, but if I move last month's emails to the less recent ones, then the cycle works perfectly, otherwise it produces this error

Attachment 187272

[Marco:G]

I think I understand but I don't know how to solve that the program crashes if there are also responses to meetings for example

[ChrisE]

you have to explain better what you want to filter in sentmails

this here would filter the emails 5 Days old

  Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
 Dim objOutlook As Outlook.Application = New Outlook.Application()
        'NameSpace,Logon
        Dim objOutlookoNS As Outlook.NameSpace = objOutlook.GetNamespace("mapi")
        objOutlookoNS.Logon("Outlook", Reflection.Missing.Value, False, True)
        Dim outLookSentmails As Outlook.MAPIFolder = objOutlookoNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        Dim outLookItems As Outlook.Items = outLookSentmails.Items
        'Datatable
        Dim dtSentmails As New DataTable
        dtSentmails.Columns.Add("Sent To")
        dtSentmails.Columns.Add("Topic")
        dtSentmails.Columns.Add("Received")
        'search
        Dim drSentmails As DataRow
        For i As Integer = 1 To outLookSentmails.Items.Count
            Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
            'If myMails.To.Contains("Barbara") Then
            'If myMails.UnRead Then
            If myMails.ReceivedTime.Date = Date.Today.AddDays(-5) Then 
                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
            End If
        Next
        'add to DGV
        DataGridView1.DataSource = dtSentmails
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing
    End Sub

[Marco:G]

you have to explain better what you want to filter in sentmails

this here would filter the emails 5 Days old

  Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
 Dim objOutlook As Outlook.Application = New Outlook.Application()
        'NameSpace,Logon
        Dim objOutlookoNS As Outlook.NameSpace = objOutlook.GetNamespace("mapi")
        objOutlookoNS.Logon("Outlook", Reflection.Missing.Value, False, True)
        Dim outLookSentmails As Outlook.MAPIFolder = objOutlookoNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        Dim outLookItems As Outlook.Items = outLookSentmails.Items
        'Datatable
        Dim dtSentmails As New DataTable
        dtSentmails.Columns.Add("Sent To")
        dtSentmails.Columns.Add("Topic")
        dtSentmails.Columns.Add("Received")
        'search
        Dim drSentmails As DataRow
        For i As Integer = 1 To outLookSentmails.Items.Count
            Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
            'If myMails.To.Contains("Barbara") Then
            'If myMails.UnRead Then
            If myMails.ReceivedTime.Date = Date.Today.AddDays(-5) Then 
                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
            End If
        Next
        'add to DGV
        DataGridView1.DataSource = dtSentmails
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing
    End Sub

Thank you very much for the answer.
I should only know if in the emails sent during the day the ecnf string is present in the body of the email. So I know that user sent the email.
Now with this code I'm at a good point for what I had to do, I only filter those sent today.
But it crashes when for example I reply to a meeting request or calendar event. Is there a way to exclude them or, if present, pass them on?
Thanks again so much.

 

strB.AppendLine = html table
oMsg.HTMLBody = strB.ToString

Dim olNS As Outlook.[NameSpace] = CType(oApp.GetNamespace("MAPI"), Outlook.[NameSpace])
Dim oMsg As Outlook.MailItem

Dim d = Date.Now.ToString("dddd dd/MM/yyyy" & " hours" & " HH:mm:ss")
Dim Fldr As MAPIFolder = olNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
Dim Mgok As Outlook.Items = Fldr.Items.Restrict("@SQL=%today(""urn:schemas:httpmail:datereceived"")%")
ecnf = Report.Item(0, 0).Value & " - " & Report.Item(1, 0).Value & " - " & d


Try
            
            For Each oMsg In Mgok
               
                If oMsg.HTMLBody.Contains(ecnf) Then GoTo send Else GoTo 001

001:

              next

[ChrisE]

just extend the filter...

ReceivedTime = 5 Days old
Body = search for Barbara

'....
            If myMails.ReceivedTime.Date >= Date.Today.AddDays(-5) AndAlso myMails.Body.Contains("Barbara") Then
'

[Marco:G]

just extend the filter...

ReceivedTime = 5 Days old
Body = search for Barbara

'....
            If myMails.ReceivedTime.Date >= Date.Today.AddDays(-5) AndAlso myMails.Body.Contains("Barbara") Then
'

the problem is that if in the emails of those 5 days I reply accept or not to a request that is sent to me, when the program browses all the emails, when I get to that one it stops and gives me an error. Is there a way to exclude them?

Attachment 187274

[ChrisE]

well the code you use in Post#7 looks strange
why would you want to use Goto in .NET

did you try my Code from Post#6 ? I mean without any changes

[Marco:G]

well the code you use in Post#7 looks strange
why would you want to use Goto in .NET

did you try my Code from Post#6 ? I mean without any changes

I make the program do different things depending on what the users are doing.

I'm trying to fit it.

what maybe i look for something like outlook rule maybe (restrict items whether it is an update or a meeting attendance)

[ChrisE]

did you try this Code with no changes

Option Strict On

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Outlook

Public Class Form1

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
        Dim objOutlook As Outlook.Application = New Outlook.Application()
        'NameSpace,Logon
        Dim objOutlookoNS As Outlook.NameSpace = objOutlook.GetNamespace("mapi")
        objOutlookoNS.Logon("Outlook", Reflection.Missing.Value, False, True)
        Dim outLookSentmails As Outlook.MAPIFolder = objOutlookoNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderSentMail)
        Dim outLookItems As Outlook.Items = outLookSentmails.Items
        'Datatable
        Dim dtSentmails As New DataTable
        dtSentmails.Columns.Add("Sent To")
        dtSentmails.Columns.Add("Topic")
        dtSentmails.Columns.Add("Received")
        'search
        Dim drSentmails As DataRow
        For i As Integer = 1 To outLookSentmails.Items.Count
            Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
            'If myMails.To.Contains("Barbara") Then
        
                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
           ' End If
        Next
        'add to DGV
        DataGridView1.DataSource = dtSentmails
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing
    End Sub

[Marco:G]

i am trying and i have an error here

......

 Dim dtSentmails As New DataTable

......

'New' cannot be used on an interface

[Marco:G]

now i'm ok whit your code but the error is.

Attachment 187275

[ChrisE]

sorry I can't see or read the Attachment

[Marco:G]

sorry I can't see or read the Attachment

......

Dim myMails = DirectCast(outLookItems.Item(i), MailItem)

.......

this error:
System.InvalidCastException: 'Impossibile eseguire il cast di oggetti COM di tipo 'System.__ComObject' in tipi di interfaccia 'Microsoft.Office.Interop.Outlook.MailItem'. L'operazione non è stata completata perché la chiamata QueryInterface sul componente COM per l'interfaccia con IID '{00063034-0000-0000-C000-000000000046}' non è riuscita a causa del seguente errore: Interfaccia non supportata. (Eccezione da HRESULT: 0x80004002 (E_NOINTERFACE)).'

[ChrisE]

sorry English or German

[Marco:G]

System.InvalidCastException: 'Unable to cast COM objects of type 'System.__ComObject' to interface types 'Microsoft.Office.Interop.Outlook.MailItem'. The operation failed because the QueryInterface call on the COM components for the interface with IID '{00063034-0000-0000-C000-000000000046}' failed with the following error: Interface not supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).'

[Marco:G]

sorry English or German

System.InvalidCastException: 'Unable to cast COM objects of type 'System.__ComObject' to interface types 'Microsoft.Office.Interop.Outlook.MailItem'. The operation failed because the QueryInterface call on the COM components for the interface with IID '{00063034-0000-0000-C000-000000000046}' failed with the following error: Interface not supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).'

[dbasnett]

System.InvalidCastException: 'Unable to cast COM objects of type 'System.__ComObject' to interface types 'Microsoft.Office.Interop.Outlook.MailItem'. The operation failed because the QueryInterface call on the COM components for the interface with IID '{00063034-0000-0000-C000-000000000046}' failed with the following error: Interface not supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).'

Try this small change

        For i As Integer = 1 To outLookSentmails.Items.Count
            If outLookItems.Item(i).MessageClass = "IPM.Note" Then '<<<<<<<<<<<<<<<<<<<<<<<<<
                Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
                'If myMails.To.Contains("Barbara") Then

                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
                ' End If
            End If '<<<<<<<<<<<<<<<<<<<<<<<<<
        Next

to make sure it is a mail item.

[ChrisE]

that is strange, which Outlook Version do you have ?

try like this

'Dim myMails = DirectCast(outLookItems.Item(i), Outlook.MailItem)
           'or
 Dim myMails = CType(outLookItems.Item(i), Outlook.MailItem)

[Marco:G]

that is strange, which Outlook Version do you have ?

try like this

'Dim myMails = DirectCast(outLookItems.Item(i), Outlook.MailItem)
           'or
 Dim myMails = CType(outLookItems.Item(i), Outlook.MailItem)

Office 365

[Marco:G]

Try this small change

        For i As Integer = 1 To outLookSentmails.Items.Count
            If outLookItems.Item(i).MessageClass = "IPM.Note" Then '<<<<<<<<<<<<<<<<<<<<<<<<<
                Dim myMails = DirectCast(outLookItems.Item(i), MailItem)
                'If myMails.To.Contains("Barbara") Then

                drSentmails = dtSentmails.NewRow
                drSentmails("Sent To") = myMails.To
                drSentmails("Topic") = myMails.ConversationTopic
                drSentmails("Received") = myMails.ReceivedTime
                dtSentmails.Rows.Add(drSentmails)
                ' End If
            End If '<<<<<<<<<<<<<<<<<<<<<<<<<
        Next

to make sure it is a mail item.

with this code it does something, xo it copies me random mails I don't know what calculation it does, and puts them in my data gridview

[Marco:G]

Office 365

with the two codes it gives me the same error

[Marco:G]

the code I put in function #7 is only when in the folder it finds a meeting response or I think similar it goes into error. with let's say normal reply emails it works perfectly

[ChrisE]

with this code it does something, xo it copies me random mails I don't know what calculation it does, and puts them in my data gridview

well count the Emails in your Sentbox and compare to the Datagridview! now there should be the same Count !

this is the first step before you go into Filtering the sentBox
add this line to the Code you have...

'....
 'add to DGV
        DataGridView1.DataSource = dtSentmails
        MessageBox.Show(CStr(dtSentmails.Rows.Count))
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing

check if you now have the same amount of Emails

[Marco:G]

well count the Emails in your Sentbox and compare to the Datagridview! now there should be the same Count !

this is the first step before you go into Filtering the sentBox
add this line to the Code you have...

'....
 'add to DGV
        DataGridView1.DataSource = dtSentmails
        MessageBox.Show(CStr(dtSentmails.Rows.Count))
        objOutlookoNS.Logoff()
        objOutlookoNS = Nothing
        outLookItems = Nothing
        objOutlook = Nothing

check if you now have the same amount of Emails

now i try to update your code.
In the meantime, thanks for all the help.

Trying this morning I changed my oMsg variable from outlook.mailitem to object, now it seems I get this error:

could not find body member in meetingitem.

Because I use this for looping

If oMsg.HTMLBody.contains(ecnf)

where oMsg.HTMLBody = strB.ToString
strB I use it in the email as an appendline
ecnf as string

[Marco:G]

Thank you all, that's how I fixed it:

For Each oMsg In Mgok

                If (TypeOf oMsg Is Outlook.MeetingItem) Then GoTo 001
                
                If oMsg.HTMLBody.contains(ecnf) Then GoTo invia Else GoTo 001

001:
            Next

I don't know if that's correct but it does what it's supposed to do for me