Need help with MapiTable and EntryID/StoreID

K

Ken Slovak - [MVP - Outlook]

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
 
P

Peter Marchert

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
 
K

Ken Slovak - [MVP - Outlook]

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
 
D

Dmitry Streblechenko

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
 
P

Peter Marchert

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
 
K

Ken Slovak - [MVP - Outlook]

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
 
D

Dmitry Streblechenko

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
 
P

Peter Marchert

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
 
D

Dmitry Streblechenko

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
 
P

Peter Marchert

Thanks for the new version Dmitry.

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

Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with MapiTable and OL2000 4
MapiTable in OL 2000 slow 11
Closing programs on remote PC - help please 1
New topic from discussion "Align cells with same value - vba almostworking" in this group 0
2 Dim Array Sort, on any columns, ascend or descend. Guidance des 0
MultiLine Code Builder, Need Help to Clean Code: 0
VBA for data validation in 3rd column does not run. I need help. 1
Outlook Security Warnings 7

Top