Outlook VBA macro to synchronize two folders

Question

I'm trying to synchronize two outlook folders (one is a shared mailbox and the other is personal custom folder) My main goal is to have those two folders in sync at least every time the "original" shared folder receive a new mail that should be copied in the "destination" custom folder.

I was able to retrieve all mail messages in the original shared mailbox within a specific time interval (eg. the current day)

Private ons As Outlook.NameSpace
Dim sourceFolder As Outlook.Folder
Dim sourceFolderRecipient As Outlook.Recipient
Set sourceFolderRecipient = ons.CreateRecipient("[email protected]")
Set sourceFolder = ons.GetSharedDefaultFolder(sourceFolderRecipient, olFolderInbox)
Dim sourceFolderMails As Outlook.items
Set sourceFolderMails = sourceFolder.items
Dim sourceFolderMail As Outlook.MailItem  

Dim strFilter As String
strFilter = "[SentOn] > '" & Date & "'"

Set sourceFolderMails = sourceFolderMails.Restrict(strFilter)

And I can copy all messages in the sourceFolderMails list in the destination folder too

Unfortunately when a new mail arrives the function is called again and it will duplicate all messages in the destination folder plus the new mail.

Is there a way to filter the sourceFolderMails like sourceFolderMails.Restrict("all Subject and SentOn messages different from a list") or some other solution to copy only the new mail/mails?

MORE INFO

My sync function is called every time a new message arrives (triggering ItemAdd event). I've tried using two nested For Each item In items for sourceFolderMails and destinationFolderMails item containers comparing Subject and SentOn attributes. It works but the SentOn comparison causes the macro to increase the execution time form less than a second to over 30 seconds and it is unfeasible because I have to check up to six shared mail folders. (Subject comparison doesn't affect the execution time while other attributes like Size does and this is the reason why I'm looking for a different way to filter the retrieved mail list eg. using items.Restrict(filter) ).

UPDATE

Considering there are some MailItem properties that can be compared without affecting execution time (at least Subject and EntryID) a possible solution could be replicate on the copied messages the original item EntryID (because it changes after message copy)

Dim objCopy As Outlook.MailItem
Dim EntryID2Property As Outlook.UserProperty

Set objCopy = sourceFolderMail.Copy
Set EntryID2Property = objCopy.UserProperties.Add("EntryID2", olText)
EntryID2Property.Value = sourceFolderMail.EntryID
objCopy.Save
objCopy.Move destinationFolder

It is possible to add a custom property and then compare it with the original EntryID

If (sourceFolderMail.Subject = destinationFolderMail.Subject) And (sourceFolderMail.EntryID = destinationFolderMail.UserProperties.Find("EntryID2")) Then

Unfortunately, the custom property comparison is as slow as the SendOn. I'm continue looking for a best way to detect a copied mail after comparing it with its original item.

Just a note: you might be able to set a Rule on OWA to accomplish this — Kubie
If you do not use the built-in functionality of a rule or ItemAdd, in a nested loop you could compare, by Subject and SentOn, each item in sourceFolderItems.Restrict(strFilter) with all items in a corresponding targetFolderItems.Restrict(strFilter). Copy when there is no match. — niton
OWA rule would be the fastest solution but it is unavailable by admin policies cause it will increase server traffic and causing disk space issues. I've already tried two nested "For Each item In items" for sourceFolderMails and destinationFolderMails item containers comparing Subject and SentOn. It works but the SentOn comparison causes the macro to increase the execution time to over 30 seconds and it is unfeasible because I have to check up to six shared mail folders. (Subject comparison doesn't affect the execution time while other attributes like Size does). — Mave751
You could indicate what you tried. How to create a Minimal, Complete, and Verifiable example — niton
If you want to save the EntryID to compare then try docs.microsoft.com/en-ca/office/vba/api/outlook.userproperties — niton

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2018-10-02T16:41:22

You can use Items.ItemAdd event on the source folder to copy the item passed the event handler.

Your code of course must be running when a new message arrives to the source folder.

Outlook VBA macro to synchronize two folders

1 Answers