Excel 2007 VBA use string variable value in object variable

Good afternoon guys!

I looked around, but I'm not finding anything that addresses my particular issue, so I'll do my best to explain.

Specs:

Excel 2007, VBA, Outlook 2007

Okay, so I've been reworking some scripts that I created to automate our reports in Excel / Avaya CMS. For the past few years I've been the sole person doing our reports, so it wasn't ever necessary to set things up on anyone else's computers. However, things are changing and I'm in the process of updating the scripts / training others to use them. At the moment, when I put my scripts on their computer, I have to go into the VBA code and manually set every reference to their own relative folder paths / outlook folder paths. Painless enough, except when I do any type of changes to the scripts and have to go through the whole process again on each computer.

So this was my solution: Create a config worksheet on the automated reporting workbook, on that config worksheet store the file paths, and in the code simply use variables to reference the config worksheet. This should make it as easy as setting the variables once from the config on a new computer without having to touch any of the lines of code.

Problem: At midnight there is data that is emailed to us from another office. We use Outlook at this office, so I've simply been having it go to the folder specified in the scripts, download the attachments, and then use the downloaded data for the reports. Since everyone sets up their own outlook folders, the paths inside of outlook are different for each user. Since VBA is accessing a worksheet to grab the config information from a cell, it's returning the path for Outlook folders as a string value. However, the outlook folder variable is of Object type, and so it doesn't allow me to use the string variable as it's value, even though the string itself is the actual value the object needs (just not as a string). So is it possible to convert the string value to a value that can be used in the Object variable?

Worksheet Config Cell Value (B5)- The String Value

outNamespace.Folders("Mailbox - Some Guy").Folders("Reports").Folders("ImportantData")

Code:

'Config tab


Dim serverConfig As Worksheet
Set serverConfig = Sheets("CONFIG")

Dim dirOutlookData As String    
dirOutlookData = serverConfig.Range("B5").Value

Dim outFolder As Object
Set outFolder = dirOutlookData 

Any ideas? Since the string that's returning for dirOutlookData is the value needed for the object variable value, how can I convert the value from the string variable so it can be used in such a way?

Thanks in advance.

Answer

Seems too convoluted a solution. Nobody could share their VBA.

In the example provided

outNamespace.Folders("Mailbox - Some Guy").Folders("Reports").Folders("ImportantData"

you could instead use this directly in the VBA

Set mailboxFolder = outNamespace.GetDefaultFolder(olFolderInbox).Parent

You now have the "Mailbox - Some Guy" folder in a generic way not needing to specify "Some Guy".

Set myFolder = mailboxFolder.Folders("Reports").Folders("ImportantData")