Email Body value and

[eilob]

Hi,
How can I specify the value of an Outlook field compared to a value on a column in an access DB

I am coding a module in Access and I want to say that if the body in the email in Outlook is different to each record in the column body then save to the table, but is not working. As the module will be run every few sec I am trying to eliminate duplicates.

If OlMail.Body <> Rst!Body Then ....

Thanks!!!!

eilob

 

[PHV]

I am trying to eliminate duplicates
Why not storing the OlMail.EntryID in an indexed field not allowing duplicates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya eilob . . .

Although [blue]PHV's[/blue] method is the most direct you'll have to answer an error prompt each time a duplicate is detected. The following [blue]example[/blue] routine will run unscathed saving proper. Copy/paste the routine in a module in the modules window (Note: the routine will probably need modification of the SQLs):

[blue]Public Sub SaveBody([b][i]olBody[/i][/b] As String)
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, flgSave As Boolean, DQ As String
   
   Set db = CurrentDb
   DQ = """"
   SQL = "SELECT EntryID, Body " & _
         "FROM [purple][b][i]TableName[/i][/b][/purple]"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      flgSave = True [green]'no records, ok to save.[/green]
   Else
      rst.FindFirst "[Body] = " & DQ & [b][i]olBody[/i][/b] & DQ
      If rst.NoMatch Then flgSave = True [green]'body not found, ok to save.[/green]
   End If
   
   If flgSave Then
      DoCmd.RunSQL "INSERT INTO [purple][b][i]TableName[/i][/b][/purple] ( Body ) " & _
                   "VALUES (" & DQ & [b][i]olBody[/i][/b] & DQ & ");"
   Else
      [green]'MsgBox "Duplicate Found! . . . Can't save Email Body!"[/green]
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

To call the routine:

[blue]   Call SaveBody("[purple][b][i]OutlookEmailBody[/i][/b][/purple]")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[eilob]

Thanks TheAceMan1!, I will try that code, and see what happens!



Eilob

 

[eilob]

I used the customized the code above to my requirements, there is a line I cant get right, to specify the olBody, I set it up equal to the OlMail.Body which represents the body in the email, seems there is something wrong with the syntax in that line.. any ideas??

Public Sub ImportOutlookItems()
Dim Olapp As Outlook.Application
Dim Olmapi As Outlook.NameSpace
Dim Olfolder As Outlook.MAPIFolder
Dim OlMail As Object
Dim OlMessage As Outlook.MailItem
Dim OlItems As Outlook.Items
Dim OlRecips As Outlook.Recipients
Dim OlRecip As Outlook.Recipient
Dim flgSave As Boolean
Dim DQ As String


'Dim SubFolder As MAPIFolder
Dim Rst As Recordset
Set Rst = CurrentDb.OpenRecordset("tbl_Mail") 'Open table tblMail

'Create a connection to outlook
Set Olapp = CreateObject("Outlook.Application")
Set Olmapi = Olapp.GetNamespace("MAPI")

'Open the PSC-EMEA inbox

'Set Olfolder = Olmapi.Folders.Item("Mailbox - PSC-EMEA").Folders.Item("Inbox")
Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
Set OlItems = Olfolder.Items

'On Error Resume Next

For Each OlMail In OlItems

olBody = OlMail.Body (HERE)

i = 1

If Rst.BOF Then
flgSave = True 'no records ok to save
Else
(AND HERE) Rst.FindFirst "[Body] = " & DQ & olBody
If Rst.NoMatch Then
flgSave = True 'body not found, ok to save
End If
End If

If flgSave Then
Rst.AddNew
Rst!Date = OlMail.ReceivedTime
Rst!Time = OlMail.ReceivedTime
Rst!From = OlMail.SenderName
Rst!Subject = OlMail.Subject
Rst!Body = OlMail.Body
Rst!CreationTime = OlMail.CreationTime
Rst!LastModificationTime = OlMail.LastModificationTime
Rst!Last_Checked = Now
Rst.Update
End If

i = i + 1



Next OlMail


MsgBox "New mails have been updated. Please check the tbl_Mail details", vbOKOnly


'Release memory
Set Olapp = Nothing
Set Olmapi = Nothing
Set Olfolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlMessage = Nothing

End Sub

 

[kjv1611]

What is the exact line where you're getting the error, and what is the exact error message?

--

"If to err is human, then I must be some kind of human!" -Me

 

[TomCologne]

See thread705-1047885

TomCologne

 

[eilob]

The error says operation is not supported by this type of object

This is the line
Rst.FindFirst "[Body] = " & DQ & olBody

I deleted olBody and still gives me the error, so this bit must be the problem "[Body] = " but I dont know why...

Thankss...

 

[kjv1611]

From TheAceMan1's example, you're missing the last "DQ"..

You have:

Rst.FindFirst "[Body] = " & DQ & olBody

Should be:

Rst.FindFirst "[Body] = " & DQ & olBody & DQ

Let us know if that fixes it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[eilob]

Thanks but no... didtn fix it

I have now
Rst.FindFirst "Body= " & DQ & olBody & DQ

The error says operation is not supported for this type of object..

 

[kjv1611]

I'm thinking that the FindFirst context may be incorrect.

Try taking a look at the Help File example for FindFirst, and see if you can make it line up...

--

"If to err is human, then I must be some kind of human!" -Me

 

[eilob]

Any other ideass...

 

[TheAceMan1]

eilob . . .

Sorry to get back so late. You must've rushed this.

What you forgot:

[blue]    Dim [purple][b]db As DAO.Database[/b][/purple], rst As [purple][b]DAO[/b][/purple].Recordset
    Dim olBody As String, i As Integer
    Set db = CurrentDb
    DQ = """"

   Set Rst = CurrentDb.OpenRecordset("tbl_Mail")
[purple][b]should be:[/b][/purple]
   Set Rst = db.OpenRecordset("tbl_Mail", dbOpenDynaset)[/blue]

I simulated what you have (table & all) and the code ran with no problem. So make the changes and give it another shot!

BTW: your biggest problem was not assigning the value for DQ:

[blue]   DQ = """"[/blue]

See Ya! . . . . . .

 

[eilob]

Thanks a million for that, but still doesnt work, I dont understand if works for you, why doesnt work for me, here is the code I have now....

Public Sub ImportOutlookItems()
Dim Olapp As Outlook.Application
Dim Olmapi As Outlook.NameSpace
Dim Olfolder As Outlook.MAPIFolder
Dim OlMail As Object
Dim OlMessage As Outlook.MailItem
Dim OlItems As Outlook.Items
Dim OlRecips As Outlook.Recipients
Dim OlRecip As Outlook.Recipient
Dim db As DAO.Database, rst As DAO.Recordset
Dim flgSave As Boolean
Dim DQ As String
Dim olBody As String

'Dim SubFolder As MAPIFolder
Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Mail", dbOpenDynaset) 'Open table tblMail


'Create a connection to outlook
Set Olapp = CreateObject("Outlook.Application")
Set Olmapi = Olapp.GetNamespace("MAPI")

'Open the PSC-EMEA inbox

Set Olfolder = Olmapi.Folders.Item("Mailbox - PSC-EMEA").Folders.Item("Inbox")
'Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
Set OlItems = Olfolder.Items

'On Error Resume Next

For Each OlMail In OlItems

olBody = OlMail.Body

i = 1

If rst.BOF Then
flgSave = True 'no records ok to save
Else
rst.FindFirst "[Body] = " & DQ & olBody & DQ
If rst.NoMatch Then
flgSave = True 'body not found, ok to save
End If
End If

If flgSave Then
rst.AddNew
rst!Date = OlMail.ReceivedTime
rst!Time = OlMail.ReceivedTime
rst!From = OlMail.SenderName
rst!Subject = OlMail.Subject
rst!Body = OlMail.Body
rst!CreationTime = OlMail.CreationTime
rst!LastModificationTime = OlMail.LastModificationTime
rst!Last_Checked = Now
rst.Update
End If

i = i + 1



Next OlMail



MsgBox "New mails have been updated. Please check the tbl_Mail details", vbOKOnly


'If mail = Read save Last Modification time finding the email in the table
'If OlMail.UnRead = False And (OlMail.Body = Rst!Body) Then
' Rst!LastModificationTime = OlMail.LastModificationTime
'End If

'Release memory
Set Olapp = Nothing
Set Olmapi = Nothing
Set Olfolder = Nothing
Set OlItems = Nothing
Set OlMail = Nothing
Set OlMessage = Nothing
Set rst = Nothing
Set db = Nothing

End Sub

 

[eilob]

Sorry forgot to say, the error still points this line
rst.FindFirst "[Body] = " & DQ & olBody & DQ
and says "Syntax error missing operator in expression"

 

[PHV]

What happens if you replace this:
rst.FindFirst "[Body] = " & DQ & olBody & DQ
with this ?

rst.FindFirst "[Body]='" & Replace(olBody, "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

eilob,

If that most recent post of yours was your current code, then you still have not "Dim'ed" the db and rst variables. You "Set" them, but did not "Dim" them. Make sure that part has been added in.

And I don't see where you set this line in your code:

DQ = """"

So, if the DQ variable is not set, you're just adding an empty string with that variable in the FindFirst statement.

--

"If to err is human, then I must be some kind of human!" -Me

 

[eilob]

Sorry I think I pasted the wrong code, will work again on it tonight and I will let you know how I get on

Thankss a lot!!

 

[TheAceMan1]

eilob . . .

Two additional things:
[ol][li]In the table tbl_Mail [blue]Body[/blue] should be [blue]Memo[/blue] data type.[/li]
[li]Taking a good look its possible for the [blue]body text[/blue] to contain [blue]quotation marks![/blue] If it does this will raise errors. To circumvent this:

[blue]replace:
   olBody = OlMail.Body
with:
   olBody = Replace(OlMail.Body, """", "'")[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[eilob]

THANK YOU, will try that