Reading data from a closed excel file into outlook

Question

How do I read data from a CLOSED excel file into outlook VBA collections?

The size of Sheet1 is N-rows by 3-columns and data is sparsely populated with text and numbers.

N is not fixed (changing as rows are added to or deleted from the end of the Sheet1).

I would like to read data (including empty cells) from the entire N x 3 range.

The total number of VBA collections is obviously 3, say ColumnA, ColumnB, ColumnC, since the excel file has data in columns A, B, C, respectively.

The excel file needs to be read unopened (I've seen it done before).

Kindly provide VBA code if possible.

Answer 1

In the Outlook VBA editor set a reference to Excel.

Tools | References
Tick Microsoft Excel Object Library

Add Option Explict to new modules. You will find this helpful.

Tools | Options | Editor tab
Tick Require Variable Declaration

. You could read excel data into Outlook refer to the below code: Option Explicit

Sub links()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim ExcelFileName As String
Dim FilePath As String

Dim oMsg As mailItem

ExcelFileName = "C:\links.xlsx"

Set exWb = objExcel.Workbooks.Open(ExcelFileName)

FilePath = exWb.Sheets("Sheet1").Cells(1, 1)

On Error Resume Next
Set oMsg = ActiveInspector.currentItem
On Error GoTo 0
If oMsg Is Nothing Then
    Set oMsg = CreateItem(0)
    oMsg.Display
End If

' This adds to existing text. 
' Must display first to save a signature
'oMsg.body = Chr(34) & FilePath & Chr(34) & oMsg.body
'or
oMsg.HTMLBody = Chr(34) & FilePath & Chr(34) & oMsg.HTMLBody

ExitRoutine:
    Set oMsg = Nothing
    Set exWb = Nothing
    Set objExcel = Nothing

End Sub

Reference from:

Outlook 2007 Macro to read excel file containing file paths and create hyperlinks