Reading .xlsx attachment data with VBA

I have an .xlsx attachment in an e-mail in Outlook. I want to get summed length of strings in one of the columns using Outlook macro. Is it possible to read attachements data on the fly in Outlook macro or what is the advised approach? Do I need to save the attachment first, then open it as a workbook and work from there?

You can open the file directly if you are using Open XML API, but it is not accessible in VBA.

Otherwise you can use Excel Object Model to read that file:

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = false
Set Workbook = ExcelApp.Workbooks.Open("C:\Path\To\Your\File.xlsx")

The Outlook object model doesn't provide anything for dealing with attached files on the fly. The best what you could do using a low-level API (Extended MAPI) is to get the byte array which represents the attached file.

You need to save the attached file on the disk and then automate Excel for getting the job done. The Attachment.SaveAsFile method saves the attachment to the specified path. The following sample code taken from MSDN shows how to use that method:

Sub SaveAttachment() 
 Dim myInspector As Outlook.Inspector 
 Dim myItem As Outlook.MailItem 
 Dim myAttachments As Outlook.Attachments 
 
 Set myInspector = Application.ActiveInspector 
 If Not TypeName(myInspector) = "Nothing" Then 
   If TypeName(myInspector.CurrentItem) = "MailItem" Then 
     Set myItem = myInspector.CurrentItem 
     Set myAttachments = myItem.Attachments 
     'Prompt the user for confirmation 
     Dim strPrompt As String 
     strPrompt = "Are you sure you want to save the first attachment in the current item to the Documents folder? If a file with the same name already exists in the destination folder, it will be overwritten with this copy of the file." 
     If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then 
       myAttachments.Item(1).SaveAsFile Environ("HOMEPATH") & "\My Documents\" & _ 
       myAttachments.Item(1).DisplayName 
     End If 
   Else 
     MsgBox "The item is of the wrong type." 
   End If 
 End If 
End Sub

To get access to Excel types from the Outlook VBA environment you need to add a corresponding COM reference:

' Set reference to 'Microsoft Excel 16.0 Object Library' in
' the Project|References dialog (or Tools|References for VB4 or VBA).

' Declare the object as an early-bound object
  Dim oExcel As Excel.Application

  Set oExcel = CreateObject("Excel.Application")

' The Visible property is called via the v-table
  oExcel.Visible = True

See Using early binding and late binding in Automation for more information.