best practice - global constant or class module for outlook vba project?

[kittenwhisky]

My outlook VBA project moves multiple emails from 1 folder to another folder. The target folder for each email depends on the email's mailbox and its tag.

For example:

Emails from mailbox1 tagged "Client" are moved to folder "Clients1"
Emails from mailbox2 tagged "Client" are moved to folder "Clients2"
Emails from mailbox3 tagged "Client" are moved to folder "Clients3"

Emails from mailbox1 tagged "Supplier" are moved to folder "Supplier1"
Emails from mailbox2 tagged "Supplier" are moved to folder "Supplier2"
Emails from mailbox3 tagged "Supplier" are moved to folder "Supplier3"

The mailbox-tag-folder combinations remain constant.

I am refactoring my code and am after the best practice here: Where/how should I store these constant mailbox-tag-folder combinations?

1. public nested collection
   ie public collection of mailboxes. each mailbox item contains tag/folder pairs.
2. class module
   which stores mailbox-tag-folder combinations

[AdorablePlum]

Could just store them in an array within a function? Such arrays are easy to create from an Excel sheet.

Function GetFolder(ByVal TheMailBox$, ByVal TheClient$) As String
Dim mapping(0 To 5)
mapping(0) = "mailbox1|Client|Clients1"
mapping(1) = "mailbox2|Client|Clients2"
mapping(2) = "mailbox3|Client|Clients3"
mapping(3) = "mailbox1|Supplier|Supplier1"
mapping(4) = "mailbox2|Supplier|Supplier2"
mapping(5) = "mailbox3|Supplier|Supplier3"


For i = 0 To UBound(mapping)
    If Split(mapping(i), "|")(0) = TheMailBox And Split(mapping(i), "|")(1) = TheClient Then
        GetFolder = Split(mapping(i), "|")(1)
        Exit For
    End If
Next
End Function

[AdorablePlum]

Thanks. Yes I think that other options could be better if it needs to be scaled. I was also thinking of using an ini file or other external source and maintaining the mappings there. That way the code wouldn't need to be modified if more mappings were added.

[kittenwhisky]

Hi everyone, I'm so sorry I missed your replies!

Thank you for the lightweight, array based solution @AdorablePlum - I will use it next time when I'm prototyping or working on a smallscale solution.

What I ended up doing in this case:

- create a class for each tag (clsTag) , so I have objects called Client, Supplier, etc.

- Each tag object has a property Mailbox. That property is actually a collection, with a key-value pair of mailboxName-folderPath. so Supplier.Mailbox("mailbox2") returns a folderPath to Supplier2

- I store all the tag objects in another collection called Tags. That way, I can use the following syntax to find the destination folder: DestFolder = Tags("Supplier").Mailbox("mailbox3")

I have saved all the tag-mailbox-folderpaths in a csv structured like this:

| Client | Supplier | ...
mailbox1 | client1 | supplier1 | ...
mailbox2 | client2 | supplier2 | ...
mailbox3 | client3 | supplier3 | ...

At startup, outlook reads the csv file, and stores all the paths into the class objects / collections, ready for later use.

Thanks for suggesting an ini file @AdorablePlum, I will consider this for my other projects.

[AdorablePlum]

Thanks for sharing.