Hi All,

I have a problem now for some time which I really can't break.
There is nothing to pin it down on, sometimes it's there sometimes not.

I use two different email account to which I have access.
Emails come in two both accounts. The emails are the same (so send to
both accounts).

Now I created a function that will compare two email folders (one in my
own account and one in my collegaes account). First I read all the
messages in both accounts into two access tables.
I use From, To, Subject, Date Send. When the tables are filled I run a
compare query which looks at the Subject and the Day and the Month in
the Date Send. If these three are the same we have a double email.

MyQuery = "SELECT DISTINCT tblInbox.From, tblInbox.Subject,
tblInbox.Received, " & _
"tblInbox.SentDTG From tblDeletedItems, tblInbox " & _
"Where (((tblInbox.From) Is Not Null) And ((tblInbox.Subject)
" & _
"Is Not Null And (tblInbox.Subject) =
[tblDeletedItems].[Subject]) " & _
"And ((Month([tblInbox].[SentDTG])) =
Month([tblDeletedItems].[SentDTG])) " & _
"And ((Day([tblInbox].[SentDTG])) =
Day([tblDeletedItems].[SentDTG]))) ORDER BY tblInbox.Received DESC"

The query is displayed in a DBgrid and always shows the right amount of
double emails.
Now I want to check all the emails in my own email folder and when they
exist in the Query I flag them.

Finally all the flagged emails are removed from my emailfolder and
those which do not exist in the other accounts folder remain in my own
folder.

The problem is that not all the double emails get flagged, even they
exist in the Query.
This is my code:

Private Sub cmdDelSel_Click()

Dim olMailItem As Outlook.MailItem
Dim iNumItems, i As Long
Dim rst As Recordset
Dim qdf As QueryDef

Set qdf = dbSettings.QueryDefs("Query1")
Set rst = qdf.OpenRecordset

iNumItems = olOwnDeleteFolder.Items.Count

olOwnDeleteFolder.Items.Sort ("[Received]"), True

If iNumItems 0 Then
For i = 1 To iNumItems
Do While rst.EOF = False
Set olMailItem = olOwnDeleteFolder.Items.Item(i)
If olMailItem.Class = olMail Then
If rst("Subject") = Left(olMailItem.Subject, 255) Then
olMailItem.FlagStatus = olFlagMarked
olMailItem.Save
Exit Do
End If
End If 'olMailItem.Class = olMail
rst.MoveNext
Loop

If rst.RecordCount 0 Then rst.MoveFirst
Next i
End If

End Sub

Like I said sometimes it works fine but sometimes it don't.

I have been thinking about characters in the Subject strings or
something with the counter but I don't think that's the problem.

Hope anyone can help me out for I have tried everthing within my range.

Regards
Marco

Am 2 Feb 2006 10:51:15 -0800 schrieb vonClausowitz:

Marco, are always the same mails not being flagged? I´d set a breakpoint
into the code and run it step by step, and watch if it´s a subject issue.

I suppose you´ve limited the "subject" field in the database to 255
characters?

If your folder could contain other object types than MailItems then you need
to check the Item´s object type *before* trying to store the ref into the
olMailItem variable, else an error would occur.

BTW: Why do you compare the day and month only, and not the complete date?
In your way two mails, sent at the 1/1/2000 and 1/1/2001 would be recognized
as equal but they aren´t.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --


Hi All,

I have a problem now for some time which I really can't break.
There is nothing to pin it down on, sometimes it's there sometimes not.

I use two different email account to which I have access.
Emails come in two both accounts. The emails are the same (so send to
both accounts).

Now I created a function that will compare two email folders (one in my
own account and one in my collegaes account). First I read all the
messages in both accounts into two access tables.
I use From, To, Subject, Date Send. When the tables are filled I run a
compare query which looks at the Subject and the Day and the Month in
the Date Send. If these three are the same we have a double email.

MyQuery = "SELECT DISTINCT tblInbox.From, tblInbox.Subject,
tblInbox.Received, " & _
"tblInbox.SentDTG From tblDeletedItems, tblInbox " & _
"Where (((tblInbox.From) Is Not Null) And ((tblInbox.Subject)
" & _
"Is Not Null And (tblInbox.Subject) =
[tblDeletedItems].[Subject]) " & _
"And ((Month([tblInbox].[SentDTG])) =
Month([tblDeletedItems].[SentDTG])) " & _
"And ((Day([tblInbox].[SentDTG])) =
Day([tblDeletedItems].[SentDTG]))) ORDER BY tblInbox.Received DESC"

The query is displayed in a DBgrid and always shows the right amount of
double emails.
Now I want to check all the emails in my own email folder and when they
exist in the Query I flag them.

Finally all the flagged emails are removed from my emailfolder and
those which do not exist in the other accounts folder remain in my own
folder.

The problem is that not all the double emails get flagged, even they
exist in the Query.
This is my code:

Private Sub cmdDelSel_Click()

Dim olMailItem As Outlook.MailItem
Dim iNumItems, i As Long
Dim rst As Recordset
Dim qdf As QueryDef

Set qdf = dbSettings.QueryDefs("Query1")
Set rst = qdf.OpenRecordset

iNumItems = olOwnDeleteFolder.Items.Count

olOwnDeleteFolder.Items.Sort ("[Received]"), True

If iNumItems 0 Then
For i = 1 To iNumItems
Do While rst.EOF = False
Set olMailItem = olOwnDeleteFolder.Items.Item(i)
If olMailItem.Class = olMail Then
If rst("Subject") = Left(olMailItem.Subject, 255) Then
olMailItem.FlagStatus = olFlagMarked
olMailItem.Save
Exit Do
End If
End If 'olMailItem.Class = olMail
rst.MoveNext
Loop

If rst.RecordCount 0 Then rst.MoveFirst
Next i
End If

End Sub

Like I said sometimes it works fine but sometimes it don't.

I have been thinking about characters in the Subject strings or
something with the counter but I don't think that's the problem.

Hope anyone can help me out for I have tried everthing within my range.

Regards
Marco

Am 2 Feb 2006 10:51:15 -0800 schrieb vonClausowitz:

Marco, are always the same mails not being flagged? I´d set a
breakpoint
into the code and run it step by step, and watch if it´s a subject
issue.

marco: No that's the problem, you can't pin it down to that.

I suppose you´ve limited the "subject" field in the database to 255
characters?

marco: Yes I have.

If your folder could contain other object types than MailItems then you
need
to check the Item´s object type *before* trying to store the ref into
the
olMailItem variable, else an error would occur.

marco: I do that as well.

BTW: Why do you compare the day and month only, and not the complete
date?
In your way two mails, sent at the 1/1/2000 and 1/1/2001 would be
recognized
as equal but they aren´t.

marco: The same emails get send via different systems, thus having a
slidely different time of sending but normally within an hour of each
other.
The emails will never be more off than a month.
--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook

Regards
Marco

Am 3 Feb 2006 14:17:55 -0800 schrieb vonClausowitz:

If your folder could contain other object types than MailItems then you
need
to check the Item´s object type *before* trying to store the ref into
the
olMailItem variable, else an error would occur.

marco: I do that as well.


In the code, you´re showing us, you don´t.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --

I thought this was enough:

If olMailItem.Class = olMail Then

Marco

Am 4 Feb 2006 04:28:32 -0800 schrieb vonClausowitz:

That´s ok, but as I´ve mentioned before, you must check the type *before*
assigning the object to the variable, so both commands are ok but you need
to mix the order.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --


I thought this was enough:

If olMailItem.Class = olMail Then

Marco

Michael,

You mean like this?
For i = 1 To iNumItems
Do While rst.EOF = False
If olMailItem.Class = olMail Then
Set olMailItem = olOwnDeleteFolder.Items.Item(i)
If rst("Subject") = Left(olMailItem.Subject, 255) Then
olMailItem.FlagStatus = olFlagMarked
olMailItem.Save
Exit Do
End If
End If 'olMailItem.Class = olMail
rst.MoveNext
Loop

Marco

Am 5 Feb 2006 00:34:20 -0800 schrieb vonClausowitz:


Dim obj as object
....
Do While rst.EOF = False
Set obj=olOwnDeleteFolder.Items.Item(i)
If obj.Class = olMail Then
Set olMailItem = obj
.....


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.vbOffice.net --


Michael,

You mean like this?
For i = 1 To iNumItems
Do While rst.EOF = False
If olMailItem.Class = olMail Then
Set olMailItem = olOwnDeleteFolder.Items.Item(i)
If rst("Subject") = Left(olMailItem.Subject, 255) Then
olMailItem.FlagStatus = olFlagMarked
olMailItem.Save
Exit Do
End If
End If 'olMailItem.Class = olMail
rst.MoveNext
Loop

Marco