CODEWITHSUNDEEP
excelvba
Dekks
Dekks

Reputation: 137

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("[email protected]").Folders("Inbox").Folders("SubInbox")

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

Account: [email protected]
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?

Upvotes: 0

Views: 203

Answers (1)

Twist
Twist

Reputation: 26

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 "[email protected]" 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.

Upvotes: 1

Related Questions