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

 

[TheAceMan1]

eilob . . .

Found another error:

[blue]   rst!Body = OlMail.Body
[purple][b]should be:[/b][/purple]
   rst!Body = OlBody[/blue]

OlMail.Body causes saving of some duplicates as its not corrected with the [blue]Replace[/blue] function and will fail compare with same body corrected in the table.

While I'm at it, here's the code I have working for access 2000, 2002, 2003:

[blue]Public Sub ImportOutlookItems()
   Dim Olapp As Outlook.Application, _
       OlBody As String, _
       OlItems As Outlook.Items, _
       Olfolder As Outlook.MAPIFolder, _
       OlMail As Object, _
       Olmapi As Outlook.NameSpace, _
       flgSave As Boolean, flgPrompt As Boolean, DQ As String, _
       db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tbl_Mail", dbOpenDynaset)
   DQ = """"
   
   [green]'Create a connection to outlook[/green]
   Set Olapp = CreateObject("Outlook.Application")
   Set Olmapi = Olapp.GetNamespace("MAPI")
   
   [green]'Open the PSC-EMEA inbox[/green]
   Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
   Set OlItems = Olfolder.Items
   
   For Each OlMail In OlItems
      OlBody = [purple][b]Replace(OlMail.Body, DQ, "'")[/b][/purple]
      
      If rst.RecordCount = 0 Then
         flgSave = True 'no records ok to save
      Else
         rst.FindFirst "[Body] = " & DQ & OlBody & DQ
      
         If rst.NoMatch Then
            flgSave = True [green]'body not found, ok to save[/green]
         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
         [purple][b]rst!Body = OlBody[/b][/purple] [green]'Not OlMail.Body![/green]
         rst!CreationTime = OlMail.CreationTime
         rst!LastModificationTime = OlMail.LastModificationTime
         rst!Last_Checked = Now
         rst.Update
         [purple][b]rst.Requery[/b][/purple] [green]'Update rst to currently saved item![/green]
         flgPrompt = True
     End If
     
     flgSave = False
   Next
   
   If flgPrompt Then
      MsgBox "New mails have been updated. Please check the tbl_Mail details", vbOKOnly
   Else
      MsgBox "No Mail saved! . . ."
   End If
   
   [green]'Release memory[/green]
   Set OlItems = Nothing
   Set Olfolder = Nothing
   Set Olmapi = Nothing
   Set Olapp = Nothing
    
End Sub[/blue]

See Ya! . . . . . .

 

[eilob]

THANK YOU!! I will see how it works and will let you know. Regards eilob

 

[eilob]

Ok, I was testing the code, the point of this, is to run the code every 20 seconds, and if the email is saved already in the access DB then shouldnt save it, but is saving it. That's way I am comparing the body value of the mail with the ones in the db and if is different then is ok to save. But is not doing it, when I run it it takes long awhile and then after I thought that it crashed, the message box comes up telling me that they are saved successfully but there are duplicates, meaning that the body havent been compared.

Here is the code, I changed it as indicated, but there is something wrong..

Public Sub ImportOutlookItems()
Dim Olapp As Outlook.Application
Dim OlBody As String
Dim OlItems As Outlook.Items
Dim Olfolder As Outlook.MAPIFolder
Dim OlMail As Object
Dim Olmapi As Outlook.NameSpace
Dim flgSave As Boolean, flgPrompt As Boolean, DQ As String
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Mail", dbOpenDynaset)
DQ = """"

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

'Open the inbox
Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
Set OlItems = Olfolder.Items

For Each OlMail In OlItems
OlBody = Replace(OlMail.Body, DQ, "'")

If OlMail.UnRead = True Then

If rst.RecordCount = 0 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

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 = OlBody
rst!CreationTime = OlMail.CreationTime
rst!LastModificationTime = OlMail.LastModificationTime
rst!Last_Checked = Now
rst.Update
rst.Requery
flgPrompt = True
End If

flgSave = False
Next

If flgPrompt Then
MsgBox "New mails have been updated. Please check the tbl_Mail details", vbOKOnly
Else
MsgBox "No mail saved!..."
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

 

[TheAceMan1]

eilob . . .

Although I'm not running the code every 20 secs I have noticed that some emails are not saved when they should've been. Going back and checking I find an email which looks like text but were really full of objects . . . hence no text.

BTW: [blue]whats the point of all this?[/blue]

See Ya! . . . . . .

 

[eilob]

I dont know really, I just got a job a day a week in this company and they are giving me all this stuff to automate, they want the number of emails and how long time it takes to open them...

 

[TheAceMan1]

eilob . . .

Have you thought about linking a table to the InBox? . . . this should work faster . . .

See Ya! . . . . . .

 

[eilob]

yes, I thought on the possibility, could you please explain me how would that work?

Thanks

 

[TheAceMan1]

eilob . . .

Well with a linked table access would have more direct access to the info. You could then work with a simple recordset [blue]instead of working across platforms![/blue] . . . [blue]certainly faster![/blue]

To get an Idea of the table and what its contents look like, perform the following:
[ol][li]From the database window [blue]File[/blue] - [blue]Get External Data[/blue] - [blue]Link Tables[/blue].[/li]
[li]In the dialog that shows in the [blue]Files of Type[/blue] combobox at the bottom select [blue]OutLook()[/blue].[/li]
[li]A dialog box should popup showing the address book and personal folders. Expand [blue]personal folders[/blue], select the [blue]InBox[/blue] and click [blue]next[/blue].[/li]
[li]Change the name of the table and/or click [blue]finish[/blue][/li]
[li]In the table window you should now see the table with an InBox looking icon.[/li]
[li]Browse the table to see if it suites your needs.[/li]
[li]The body is renamed [blue]Content[/blue] here. Exand the record and content widths so you can a decent amount of contents. I'm hoping you see what I referred to before . . . some emails have no content (none could be transferred because of objectivity (no actual text but text in objects).[/li][/ol]

[blue]Your Thoughts so far . . .[/blue]

BTW the table is easily refreshed if need be.

See Ya! . . . . . .

 

[eilob]

I linked the tables successfully, but I have a question, if an email is read, or if it is deleted this table will be changed as well?
So could I set a macro where if there is a new record then will be copied to another table? would that be the way to work it?

Thankss!

 

[TheAceMan1]

eilob . . .

I havn't tested it yet but yes it should!

See Ya! . . . . . .

 

[TheAceMan1]

eilob . . .

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .