Passing object Variables from cells into (Excel) VBA

Question

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?

Twist · Accepted Answer · 2018-06-25 12:02:38Z

1

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.

answered Jun 25, 2018 at 12:02

Twist

262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dekks Over a year ago

This worked perfectly, thanks. I didn't realise you had to assign the value to a variable before you could call it, is this specific to this example because it's a string?

Collectives™ on Stack Overflow

Passing object Variables from cells into (Excel) VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related