Outlook Form - list OPen Excel Workbooks in drop down combo box

[Viper010]

In excel, I have used the following code successfully as part of an Excel macro and form, to pre-popluate a form drop down combo box with the open excel workbooks, to allow the use to select which workbook.

Private Sub UserForm_Initialize()
Dim SOmething As Excel.Application


Dim wkb As Workbook



With Me.ComboBox1
For Each wkb In Application.Workbooks
.AddItem wkb.Name
Next wkb
End With

LoadFrm
End Sub

Using the above code, does not seem to work in Outlook 2013. Returns an error object not set?


I suspect its to do with the different methods, vba works within outlook rather than excel. Objective here, is to pre-populate an outlook form, with current open excel workbooks, allow the user to select the workbook to use, then use that selection, as variable without outlook, to export attributes of emails to the excel workbook selected.

I have the rest of the code: just need the form selection of excel workbook to work.

Any ideas?

thanks

[grimes0332]

The code cannot work in Outlook... and your post is a little lacking in detail.

The first question is have you set a reference to the Excel Object model using the Tools/References menu?

Then what you have to do is declare an object either as a generic object or as 'Excel.Application' depending on the answer to the first question (declared as 'Something').

You than need to get a reference to a running instance of Excel. If that succeeds you then loop through the workbooks but change the 'Application' to 'Something' in the'For' line. Application refers to the application running the code, which is Outlook and Outlook does not have a Workbooks collection. Not forgetting this will require some error handling code, and not just

Please Login or Register  to view this content.

General answer, reflects the level of detail in your post.

[Viper010]

Thanks for the response and apologies for not responding sooner - I got distracted.

Anyway, yes, in Outllook 2013, under VBA, References, I have the Microsoft Excel 15.0 Object Library selected. I believe Outlook does not recognize native Excel commands, but I am trying to find a way to make this happen.

My basic objective here, to to have some Outlook code, called via a macro, to:
Loop through a defined subfolder of Inbox
identify particular emails within that subfolder, then
populate key fields, detailed in the email header and contents, into a selected Excel workbook.

for example:
Email contained in Inbox > Certification
email subject"Re: Response required - Certification"
email contents, may include one or more statements, such as "I no longer require the account" or "The account is still in use" or "other comments"

Objective, depending on the key words found in the contents, for each email in Certification folder I would like to output the responses, to an excel workbook, to include:
Senders name
email address
date
response

I thought I would hard-code, all the excel workbook names, settings etc, but then thought (and hence my question) if I could have a form to allow a user of this code, to configure the options (excel workbook name, location, parameters to search for in each email and outputs, this would make the code more flexible for others to use.

So I am looking for a way to control Excel via Outlook and to include within an outlook form, a list (via drop down) of all open excel workbooks, then using the workbook selected, use this in code, to output results to that workbook.

I hope this makes more sense, if not please let me know.

Thanks