Open an outlook email by Subject on MS Access linked table with VBA

[Juliano]

Hi everyone
I linked my Inbox folder to MS access table. It means that every time I open my access table, it is updated as per current emails in my inbox - It is already an built in feature on MS Access.

I would like to know on how from my MS Access, using the Subject field in my linked table I open the same message straight in Outlook email by VBA.

Thanks in advance

 

[Diane Poremsky]

You need to use vba and either do a lookup or, if you have the message id, find it using that. (The message id is unique, subjects might not be, but the lookup could also check dates.)

 

[Juliano]

When using the built in option to link my access table to outlook folder, it doesn't show any ID field for email. So manually there is no way to get this field from Outlook that's why my only option was the Subject field.
Actually the record exists on Outlook due its a linked table to Outlook data.
I just don't know how to proceed to make this magic. I mean, this automation by VBA I need assistance from experts.

 

[Juliano]

Is there a way we can import all outlook table fields so we can get the proper email ID avoiding duplication?
I really don't know how to proceed.

 

[Juliano]

Scratching the helper, I could develop the code below.
Now my problem is the EntryID field that I don't have on my Access table because when using the assistant, the build in feature don't retrieve this field.
My problem now is how to import/link this field

Sub openOutlookEmailByAccess()
    Dim OutLookApp As New Outlook.Application
    Dim OutLookNameSpace As Outlook.NameSpace
    Dim OutLookMail As Outlook.MailItem
    Set OutLookNameSpace = OutLookApp.GetNamespace("MAPI")
    OutLookNameSpace.Logon
    Set OutLookMail = OutLookNameSpace.GetItemFromID("00000000B92893D4C199E74592434EEA5D4818860700B38917A16466634586AD214BF5FA94F600A17CBD800D0000B38917A16466634586AD214BF5FA94F600A17CBDBAC70000")
    OutLookMail.Display
End Sub

 

[Diane Poremsky]

As long as you have received dates, you should be able to get unique messages using Find or Restrict.

 

[Juliano]

Well, I'm trying to use your suggestion as per code below. But is saying that a variable was not set. Err 91. Why is not working?

Sub openOutlookEmailByAccess()
    Dim OutLookApp As New Outlook.Application
    Dim OutLookNameSpace As Outlook.NameSpace
    Dim OutLookMail As Outlook.MailItem
    Dim OutLookItems As Outlook.Items
    Dim OutLookRestItems As Outlook.Items
    Dim strFilter As String
   
    Dim OutLookInboxFolder As Outlook.MAPIFolder
    Dim OutLookSrcFolder As Outlook.MAPIFolder
    Dim strSrcSubject As String
    Dim strSrcReceived As String
   
    Set OutLookNameSpace = OutLookApp.GetNamespace("MAPI")
    Set OutLookInboxFolder = OutLookNameSpace.GetDefaultFolder(olFolderInbox)
   
    Set OutLookSrcFolder = OutLookInboxFolder.Folders("@04-COMPLETED")
   
    OutLookNameSpace.Logon
    strSrcSubject = "[Subject]= 'Testing Tips'"
    strSrcReceived = "[Received] ='" & Format("03/11/2017 08:06:19", "ddddd h:mm:ss AMPM") & "'"
   
    strFilter = strSrcSubject & " And " & strSrcReceived
   
    Set OutLookItems = OutLookSrcFolder.Items
    OutLookRestItems = OutLookItems.Restrict(strFilter)
   
End Sub

 

[Diane Poremsky]

does it show you which line its dying on?

 

[Juliano]

OutLookRestItems = OutLookItems.Restrict(strFilter)

 

[Michael Bauer]

You need to use the Set statement as you did on the line above the error causing line.

 

[Juliano]

Thank you for your reply.