Referencing Excel cell from Outlook VBA

E

expect_ed

My apologies if this is a redundant post. I responded to a thread from Jan
but it appears there is not a view that shows new replies. If I missed it I
apologize.

My issue is this:
I have about 2 dozen projects active at any one time which change week by
week. I am set up now with macros/toolbars/keyboard shortcuts to route the
selected message to their respective project folders by keybrd shortcut.

I would like to be able to edit the routing without having to go into VBA
and edit code every time. Is it possible to create an array in excel which
would list the ALT key and the target folder and have the seperate Outlook
VBA subroutines reference a cell in that array to know which folder to route
to. Then I could easily change the routing by editing the excel sheet rather
than digging into code each time. This would also make the solution more
easily distributable to my co-workers who could edit their own excel sheets
but are not capable of editing VBA.

So my Macro now is:
Application.ActiveExplorer.Selection.Item(1).Move
Application.GetNamespace("MAPI").Folders("Archival Folder").Folders("Target
Folder")

I think "all" I need to do is replace the "Target Folder" with a reference
to the specific excel cell. Is that possible?? A specific example would be
helpful.

TIA
ed
 
M

Michael Bauer [MVP - Outlook]

Assuming that Excel is running and the workbook is open:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim rn as Excel.Range
Dim TargetFolder as string

Set xl=GetObject(,"Excel.Application")
Set wb=xl.Workbooks("Bookname")
Set ws=wb.Sheets(1)
Set rn=ws.Range("project 1")

TargetFolder=rn.Value

As you can see you can name a cell in Excel. For instance, write the folder
name for project 1 into cell B2 and name that cell 'project 1'. then you can
even change the cell later without having to rewrite code.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Thu, 22 Mar 2007 14:25:21 -0700 schrieb expect_ed:
 
E

expect_ed

Michael, that is excellent. It would have taken me another 2 days work or
more to figure that out on my own. My co-workers are going to think I'm a
genius thanks to you (of course I'm taking all the credit ; }.

Sorry to be a pest but just one more thing to make it really functional.
Given the path to the workbook can I force the worksheet open from within
Outlook? How might I go about that?

Thanks again for your help.
ed
 
E

expect_ed

Thanks again Michael. I figured out the Workbood.Open syntax and got it to
work
BUT
When I tried using your example i could not get it working, so I tried it in
a macro by itself to isolate the problem and found:
Macro breaks with a "Compile error: User-Defined type not defined"
Choosing debug and
The Sub line is hilighted in yellow [Sub UseXl()]
The line: Dim xl as Excel.Application is hilighted in black starting with
xl.

What am I missing??
Thanks
 
M

Michael Bauer [MVP - Outlook]

Please add a reference to Microsoft Excel via Tools/References.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Fri, 23 Mar 2007 10:36:30 -0700 schrieb expect_ed:
Thanks again Michael. I figured out the Workbood.Open syntax and got it to
work
BUT
When I tried using your example i could not get it working, so I tried it in
a macro by itself to isolate the problem and found:
Macro breaks with a "Compile error: User-Defined type not defined"
Choosing debug and
The Sub line is hilighted in yellow [Sub UseXl()]
The line: Dim xl as Excel.Application is hilighted in black starting with
xl.

What am I missing??
Thanks


Michael Bauer said:
Please see the Workbooks.Open function.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Fri, 23 Mar 2007 05:51:13 -0700 schrieb expect_ed:
you
can missed
it week
by
Click to expand...
Click to expand...
 
E

expect_ed

That did it. Sorry for my limited knowledge and thanks so much for your help.
ed

Michael Bauer said:
Please add a reference to Microsoft Excel via Tools/References.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Fri, 23 Mar 2007 10:36:30 -0700 schrieb expect_ed:
Thanks again Michael. I figured out the Workbood.Open syntax and got it to
work
BUT
When I tried using your example i could not get it working, so I tried it in
a macro by itself to isolate the problem and found:
Macro breaks with a "Compile error: User-Defined type not defined"
Choosing debug and
The Sub line is hilighted in yellow [Sub UseXl()]
The line: Dim xl as Excel.Application is hilighted in black starting with
xl.

What am I missing??
Thanks


Michael Bauer said:
Please see the Workbooks.Open function.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Fri, 23 Mar 2007 05:51:13 -0700 schrieb expect_ed:

Michael, that is excellent. It would have taken me another 2 days work or
more to figure that out on my own. My co-workers are going to think I'm a
genius thanks to you (of course I'm taking all the credit ; }.

Sorry to be a pest but just one more thing to make it really functional.
Given the path to the workbook can I force the worksheet open from within
Outlook? How might I go about that?

Thanks again for your help.
ed

:


Assuming that Excel is running and the workbook is open:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim rn as Excel.Range
Dim TargetFolder as string

Set xl=GetObject(,"Excel.Application")
Set wb=xl.Workbooks("Bookname")
Set ws=wb.Sheets(1)
Set rn=ws.Range("project 1")

TargetFolder=rn.Value

As you can see you can name a cell in Excel. For instance, write the
folder
name for project 1 into cell B2 and name that cell 'project 1'. then you
can
even change the cell later without having to rewrite code.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Quick-Cats - The most effective way to assign Outlook categories:
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Thu, 22 Mar 2007 14:25:21 -0700 schrieb expect_ed:

My apologies if this is a redundant post. I responded to a thread from
Jan
but it appears there is not a view that shows new replies. If I missed
it
I
apologize.

My issue is this:
I have about 2 dozen projects active at any one time which change week
by
week. I am set up now with macros/toolbars/keyboard shortcuts to route
the
selected message to their respective project folders by keybrd shortcut.

I would like to be able to edit the routing without having to go into
VBA
and edit code every time. Is it possible to create an array in excel
which
would list the ALT key and the target folder and have the seperate
Outlook
VBA subroutines reference a cell in that array to know which folder to
route
to. Then I could easily change the routing by editing the excel sheet
rather
than digging into code each time. This would also make the solution more
easily distributable to my co-workers who could edit their own excel
sheets
but are not capable of editing VBA.

So my Macro now is:
Application.ActiveExplorer.Selection.Item(1).Move
Application.GetNamespace("MAPI").Folders("Archival
Folder").Folders("Target
Folder")

I think "all" I need to do is replace the "Target Folder" with a
reference
to the specific excel cell. Is that possible?? A specific example
would
be
helpful.

TIA
ed
Click to expand...
Click to expand...
Click to expand...
 
Top