I have a page on sample Outlook automation from Excel, so why not the reverse: a page with sample code for automating Excel from Outlook.
There is really only one thing we need to be concerned about here: creating an Excel worksheet. Once that is done, we can use any methods or properties of Excel, as if we were in Excel itself (with a few caveats, which I'll explain).
Create Excel Workbook (From Outlook, late bound)
Sub CreateXLWorkbook() Dim xlApp As Object ' Excel.Application Dim xlWkb As Object ' Excel.Workbook Set xlApp = CreateObject("Excel.Application") ' New Excel.Application Set xlWkb = xlApp.Workbooks.Add xlApp.Visible = True Set xlApp = Nothing Set xlWkb = Nothing End Sub
If you are using late bound references to Excel, you'll need to qualify some of your method calls with 'xlApp', or use 'xlApp' instead of Application, whenever you want to use the Excel.Application Object.
For example, if we were to extend the code above and turn off screen updating, I'd need to write xlApp.ScreenUpdating = False, not Application.ScreenUpdating = False.
The early bound version is as follows. You will need to set a reference to the appropriate Microsoft Excel object library before compiling the code. See the Binding page for more information about setting object library references
Create Excel Workbook (From Outlook, early bound)
Sub CreateXLWorkbook() Dim xlApp As Excel.Application Dim xlWkb As Excel.Workbook Set xlApp = CreateObject("Excel.Application") ' or New Excel.Application Set xlWkb = xlApp.Workbooks.Add xlApp.Visible = True Set xlApp = Nothing Set xlWkb = Nothing End Sub
The rest is an exercise left up to you.
The advantage of the early bound approach is that you gain Excel's Intellisense. I've explained elsewhere how to combine the two approaches to take advantage of the benefits of late and early binding. And I've provided encapsulated code that let's you create numerous types of Application object references.
FYI- You can download the Excel 2003 VBA Language Reference from Microsoft.
