Need help with MapiTable and EntryID/StoreID

  • Thread starter Thread starter Peter Marchert
  • Start date Start date
Try something like this:

' previous existing code

Dim strSubject As String
Set objItem = Outlook.ActiveExplorer.Selection(1)
strSubject = Replace(objItem.Subject, " '", "''")

' other existing code

' this is all on one line
strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder WHERE
(""urn:schemas:httpmail:subject"" = '" & strSubject & " ')"





Here is some code for testing:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table As Redemption.MAPITable
Dim Recordset
Dim strSQL As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

Set Table = CreateObject("Redemption.MAPITable")
'Set Table = CreateObject("SafeOutlook.SecureMAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder
WHERE (""urn:schemas:httpmail:subject""='" & Replace(objItem.Subject,
"'", "''") & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If an email is marked with a prefix no records will be returnd.

Emails without prefix will be returned.

Peter
 
Thank you for your answer, Ken.

I tried this, but it makes no difference. Does the code works on your
machine? If so, it could be a local problem.

Peter
 
Yes, it worked here. I played with it on an Outlook 2003 setup running on
WinXP (all US English language).




Thank you for your answer, Ken.

I tried this, but it makes no difference. Does the code works on your
machine? If so, it could be a local problem.

Peter
 
I am not sure I understand - since you are requesting an exact match on
PR_SUBJECT, you will only get exact matches, if an e-mail has a prefix, it
won't match, and hence won't be returned.
If you request PR_CONVERSATION_TOPIC
(http://schemas.microsoft.com/mapi/proptag/0x0070001E), you will get matches
on e-mails both with and without prefixes RE, FW, etc.

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

Here is some code for testing:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table As Redemption.MAPITable
Dim Recordset
Dim strSQL As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

Set Table = CreateObject("Redemption.MAPITable")
'Set Table = CreateObject("SafeOutlook.SecureMAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder
WHERE (""urn:schemas:httpmail:subject""='" & Replace(objItem.Subject,
"'", "''") & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If an email is marked with a prefix no records will be returnd.

Emails without prefix will be returned.

Peter
 
Perhaps we are talking about different things :-)

My English is not the best so it could be a missunderstanding. I try
to describe my problem again:

I try to get all subjects of emails. For testing I have one email
selected and then run this code:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table
Dim Recordset
Dim strSQL As String
Dim strSubject As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

strSubject = Replace(objItem.Subject, "'", "''")

Set Table = CreateObject("Redemption.MAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""http://schemas.microsoft.com/mapi/proptag/
0x0070001E"" FROM Folder WHERE (""http://schemas.microsoft.com/mapi/
proptag/0x0070001E""='" & strSubject & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If the subject of the selected email is "Test" the subject will be
returned. If the subject of the selected email is "FW: Test" the
subject will not be returned (both times I mean the subject will not
be printed by the debug.print command).

After Kens answer I booted an English Virtual PC with an English
Office 2003, but the result is the same. I used for this test the
original Redemption with version 4.4.0.714.

I have no idea why this will not work on my machines.

Peter
 
Peter,

I think you need to check where you add your spaces and single quotes.
Here's the exact test code I used to get the code to work, see if it works
for you:

Sub MapiTableSQLTest()
Dim objItem As Outlook.MailItem
Dim Table As Redemption.MAPITable
Dim Recordset
Dim strSQL As String
Dim strSubject As String

Set objItem = Outlook.ActiveExplorer.Selection(1)
strSubject = Replace(objItem.Subject, "'", "''")

Set Table = CreateObject("Redemption.MAPITable")
'Set Table = CreateObject("SafeOutlook.SecureMAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

'all on one line
strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM Folder WHERE
(""urn:schemas:httpmail:subject"" = '" & strSubject & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend
End Sub

Also, when I select a message that has "RE: " or "FWD: " (like "AW: ") I
strip out the prefix:
strSubject = Replace(strSubject, "RE: ", "")

That returns not only messages without the prefix but messages that have the
prefix.




Perhaps we are talking about different things :-)

My English is not the best so it could be a missunderstanding. I try
to describe my problem again:

I try to get all subjects of emails. For testing I have one email
selected and then run this code:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table
Dim Recordset
Dim strSQL As String
Dim strSubject As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

strSubject = Replace(objItem.Subject, "'", "''")

Set Table = CreateObject("Redemption.MAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""http://schemas.microsoft.com/mapi/proptag/
0x0070001E"" FROM Folder WHERE (""http://schemas.microsoft.com/mapi/
proptag/0x0070001E""='" & strSubject & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If the subject of the selected email is "Test" the subject will be
returned. If the subject of the selected email is "FW: Test" the
subject will not be returned (both times I mean the subject will not
be printed by the debug.print command).

After Kens answer I booted an English Virtual PC with an English
Office 2003, but the result is the same. I used for this test the
original Redemption with version 4.4.0.714.

I have no idea why this will not work on my machines.

Peter
 
This is to be expected: when it does not work (Subject = "FW: Test"),
PR_SUBJECT is "FW: Test" and PR_CONVERSATION_TOPIC = "Test". You script
specifies PR_CONVERSATION_TOPIC = "FW: Test", which no message will match.
You need PR_SUBJECT = "FW: Test":

strSQL = "SELECT ""http://schemas.microsoft.com/mapi/proptag/
0x0037001E"" FROM Folder WHERE (""http://schemas.microsoft.com/mapi/
proptag/0037001E""='" & strSubject & "')"


Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool

Perhaps we are talking about different things :-)

My English is not the best so it could be a missunderstanding. I try
to describe my problem again:

I try to get all subjects of emails. For testing I have one email
selected and then run this code:

Sub MapiTableSQLTest()

Dim objItem As Outlook.MailItem
Dim Table
Dim Recordset
Dim strSQL As String
Dim strSubject As String

Set objItem = Outlook.ActiveExplorer.Selection(1)

strSubject = Replace(objItem.Subject, "'", "''")

Set Table = CreateObject("Redemption.MAPITable")

Table.Item = Outlook.ActiveExplorer.CurrentFolder.Items

strSQL = "SELECT ""http://schemas.microsoft.com/mapi/proptag/
0x0070001E"" FROM Folder WHERE (""http://schemas.microsoft.com/mapi/
proptag/0x0070001E""='" & strSubject & "')"

Set Recordset = Table.ExecSQL(strSQL)

While Not Recordset.EOF
Debug.Print Recordset.Fields(0).Value
Recordset.MoveNext
Wend

End Sub

If the subject of the selected email is "Test" the subject will be
returned. If the subject of the selected email is "FW: Test" the
subject will not be returned (both times I mean the subject will not
be printed by the debug.print command).

After Kens answer I booted an English Virtual PC with an English
Office 2003, but the result is the same. I used for this test the
original Redemption with version 4.4.0.714.

I have no idea why this will not work on my machines.

Peter
 
Now I have two mails with "Test" and "FW: Test". If "Test" is selected
"urn:schemas:httpmail:subject" returns both items. But I only want the
subject which is complete the same.

I now give up. May be I spend to a later moment time with this problem.

Thank you all for your time you spended to this thread.

Peter
 
Ah! I see what the problem is: ExecSQL was always using
PR_NORMALIZED_SUBJECT even if PR_SUBJECT was specified in the DASL format in
the WHERE clause.
Send a message to my e-mail address if you want an updated version.

Thanks!

Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool
 
Thanks for the new version Dmitry.

With "urn:schemas:httpmail:subject" it works as I need it.

Peter
 
Back
Top