如何统计Excel附件数量，区分处理非Excel附件及无附件场景？

Great approach starting with counting .xls attachments—let’s build on that to cover non-Excel cases like PDFs. Here’s how you can extend your existing code to handle all scenarios cleanly:

Step 1: Loop Through Attachments & Classify Types #

First, iterate over each attachment in your email item (oOlItm), categorizing them into Excel vs non-Excel. This lets you:

• Confirm if there are Excel files to download
• Detect when non-Excel attachments (like PDFs) exist but no Excel files are present
• Handle the "no attachments at all" case separately

Here’s a sample VBA snippet that implements this logic:

Sub ProcessEmailContent(oOlItm As Outlook.MailItem)
    Dim att As Outlook.Attachment
    Dim excelAttachmentCount As Integer
    Dim hasNonExcelAttachments As Boolean
    
    excelAttachmentCount = 0
    hasNonExcelAttachments = False
    
    ' Loop through all attachments in the email
    For Each att In oOlItm.Attachments
        ' Skip embedded content (like signatures/images) if needed
        If att.Type = olByValue Then
            ' Check for all common Excel file extensions
            Select Case LCase(Right(att.FileName, 5))
                Case ".xlsx", ".xlsm"
                    excelAttachmentCount = excelAttachmentCount + 1
                    ' Optional: Download the Excel file immediately
                    ' att.SaveAsFile "C:\Your\Target\Path\" & att.FileName
                Case Else
                    ' Check for older .xls format (4-character extension)
                    If LCase(Right(att.FileName, 4)) = ".xls" Then
                        excelAttachmentCount = excelAttachmentCount + 1
                    Else
                        ' Mark that non-Excel attachments exist
                        hasNonExcelAttachments = True
                    End If
            End Select
        End If
    Next att
    
    ' Handle each scenario based on our counts/flags
    If excelAttachmentCount > 0 Then
        Debug.Print "Found " & excelAttachmentCount & " Excel attachment(s) — proceeding to download."
        ' Add your full Excel download logic here
    Else
        If hasNonExcelAttachments Then
            Debug.Print "No Excel attachments detected, but non-Excel files (e.g., PDF) are present."
            ' Optional: Log this case or take specific action if needed
        End If
        ' Scrape email body into Excel regardless of non-Excel attachments
        Debug.Print "Scraping email body into Excel document."
        ' Add your body-scraping and Excel writing logic here
    End If
End Sub

Key Details to Note: #

• Cover all Excel formats: Don’t limit yourself to .xls—include .xlsx and .xlsm to catch modern Excel files.
• Skip embedded content: The att.Type = olByValue check filters out hidden attachments like email signatures or inline images, so you only process actual file attachments.
• Clear boolean flag: hasNonExcelAttachments gives you an explicit way to detect when non-Excel files exist, which is exactly what you asked for.

Edge Cases to Account For: #

• Attachments with .xls in the middle of the filename (e.g., project.xls.backup): Using Right() to check the end of the filename avoids false positives.
• Attachments with no file extension: These will be flagged as non-Excel, which is correct for your use case.
• Password-protected Excel files: The code counts them as Excel attachments, but you’ll need additional logic if you need to verify they’re accessible.

内容的提问来源于stack exchange，提问作者Selkie