Passing object Variables from cells into (Excel) VBA

I have a VBA script that tallies unread emails in a selected folder in Outlook. At the moment the account and folder name is hardcoded into the script, but I'd like to make it configurable from one of the excel sheets.

Currently the variable is set here:

Set inboxselect = GetObject(, "Outlook.Application").GetNameSpace("MAPI").Folders("mailbox@companyname.com").Folders("Inbox").Folders("SubInbox")

Ideally I'd like to have a sheet called Config, with data like:

Account: mailbox@companyname.com
Folder:  Inbox
Subfolder: Subfolder

And then reference this in the VBA script, but I'm having trouble understanding how to pass a cell reference into the variable. I tried:

inboxselect = GetObject(, "Outlook.Application").GetNameSpace("MAPI").Folders(Worksheets("Config").Range("B1")).Folders("Inbox").Folders("SubInbox") 

But it gives a a type mis-match error.

Is anyone able to provide any light on how this should be done?

Answer

The variables you're trying to pass to the GetObject() function are strings.

You could use named cells on your Excel worksheet, e.g. if cell A1 contains "mailbox@company.com" name that cell Account (use the name manager or just overwrite the cell reference in the top-left of the window.

In vba you can then reference your named cell using

Dim sAccount as String
sAccount = Range("Account").Value
Set inboxselect = GetObject(, "Outlook.Application").GetNameSpace("MAPI").Folders(sAccount).Folders("Inbox").Folders("SubInbox")

Similarly for the other variables.