Excel工作簿邮件按钮开发需求：发送内容并附加日期目录附件

Got it, let's work through this solution step by step. You've already got the foundation for sending Excel content via a button, so we'll focus on integrating the attachment functionality tied to that date-named folder your batch script creates.

First, we need VBA to generate the exact same YYYY-MM-DD folder path your batch script creates. This ensures we're pulling attachments from the correct directory. Here's a helper function to handle that:

Function GetDateFolderPath() As String
    ' Match the YYYY-MM-DD format from your batch script
    Dim dateFolderName As String
    dateFolderName = Format(Date, "yyyy-mm-dd")
    
    ' Replace this with your base directory (where the batch creates the folder)
    ' Example: If your batch makes the folder in "C:\DailyReports\", use that here
    GetDateFolderPath = "C:\YourBaseDirectory\" & dateFolderName & "\"
End Function

Pro tip: If your batch creates the folder in the same directory as your Excel workbook, replace the hardcoded path with ThisWorkbook.Path & "\" & dateFolderName & "\" to make it dynamic.

Assuming you're using Outlook to send emails (the most common setup), here's how to modify your existing code to attach all files from the date-named folder:

Sub SendExcelContentWithAttachments()
    Dim olApp As Object
    Dim olMail As Object
    Dim dateFolderPath As String
    Dim targetFile As String
    
    ' Initialize Outlook
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
    
    ' Grab the date folder path
    dateFolderPath = GetDateFolderPath()
    
    ' Check if the folder exists first (avoid errors!)
    If Dir(dateFolderPath, vbDirectory) = "" Then
        MsgBox "Date-named folder not found! Run your batch script first.", vbExclamation
        Exit Sub
    End If
    
    ' --- Your existing Excel content code goes here ---
    ' Example: Convert your sheet's used range to HTML for the email body
    olMail.BodyFormat = 2 ' Set to HTML format
    olMail.HTMLBody = "<p>Hi Team,</p><p>Here's today's Excel report:</p>" & RangetoHTML(ActiveSheet.UsedRange)
    
    ' --- Attach all files from the date folder ---
    targetFile = Dir(dateFolderPath & "*.*")
    Do While targetFile <> ""
        olMail.Attachments.Add dateFolderPath & targetFile
        targetFile = Dir ' Move to next file
    Loop
    
    ' Set email details
    olMail.Subject = "Daily Excel Report - " & Format(Date, "yyyy-mm-dd")
    olMail.To = "recipient@yourcompany.com"
    olMail.CC = "cc@yourcompany.com"
    
    ' Choose to send automatically or preview first
    ' olMail.Send ' Uncomment this to send without preview
    olMail.Display ' Use this to review before sending
    
    ' Cleanup
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

' Helper function to convert Excel ranges to HTML (you likely already have this)
Function RangetoHTML(rng As Range) As String
    Dim fso As Object, ts As Object
    Dim tempFile As String, tempWB As Workbook
    
    tempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
    ' Copy range to a temporary workbook
    rng.Copy
    Set tempWB = Workbooks.Add(1)
    With tempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues
        .Cells(1).PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        .DrawingObjects.Delete ' Remove any shapes to clean up HTML
    End With
    
    ' Publish to HTML
    tempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=tempFile, _
         Sheet:=tempWB.Sheets(1).Name, _
         Source:=tempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic).Publish True
    
    ' Read the HTML file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(tempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    
    ' Cleanup temp files
    tempWB.Close savechanges:=False
    Kill tempFile
    Set ts = Nothing: Set fso = Nothing: Set tempWB = Nothing
End Function

If you only want to attach specific file types (e.g., PDFs only), replace Dir(dateFolderPath & "*.*") with Dir(dateFolderPath & "*.pdf").

For the second requirement (sending custom details with images from the date folder), use this standalone macro:

Sub SendDetailsWithImageAttachments()
    Dim olApp As Object
    Dim olMail As Object
    Dim dateFolderPath As String
    Dim imageFile As String
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    
    dateFolderPath = GetDateFolderPath()
    
    If Dir(dateFolderPath, vbDirectory) = "" Then
        MsgBox "Date-named folder not found!", vbExclamation
        Exit Sub
    End If
    
    ' --- Add your custom details here ---
    olMail.Body = "Hi Stakeholders," & vbNewLine & vbNewLine & _
                  "Today's key updates:" & vbNewLine & _
                  "- Sales Target: 92% completed" & vbNewLine & _
                  "- Inventory: Low on Product X" & vbNewLine & vbNewLine & _
                  "Attached are supporting images for reference."
    
    ' --- Attach only image files (jpg/png/bmp/gif) ---
    imageFile = Dir(dateFolderPath & "*.jpg")
    Do While imageFile <> ""
        olMail.Attachments.Add dateFolderPath & imageFile
        imageFile = Dir
    Loop
    ' Repeat for other image formats if needed
    imageFile = Dir(dateFolderPath & "*.png")
    Do While imageFile <> ""
        olMail.Attachments.Add dateFolderPath & imageFile
        imageFile = Dir
    Loop
    
    ' Email settings
    olMail.Subject = "Daily Update - " & Format(Date, "yyyy-mm-dd")
    olMail.To = "stakeholders@yourcompany.com"
    
    ' olMail.Send
    olMail.Display
    
    ' Cleanup
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

Bonus: If you want to embed images directly in the email body instead of attaching them, add this snippet after attaching an image:

Dim embeddedImg As Object
Set embeddedImg = olMail.Attachments.Add(dateFolderPath & "sales_chart.jpg")
embeddedImg.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001F", "SalesChart"
olMail.HTMLBody = "<p>Here's today's sales chart:</p><img src=""cid:SalesChart"" />" & olMail.HTMLBody

To make these functions accessible:

• Go to the Developer tab > Insert > Choose Button (Form Control)
• Draw the button on your sheet, then select the relevant macro (e.g., SendExcelContentWithAttachments)
• Rename the button to something clear like "Send Report + Attachments" or "Send Update + Images"

• Ensure Outlook is running before triggering the macro (or enable Outlook's macro permissions in its Trust Center)
• Double-check the base directory in GetDateFolderPath matches where your batch script creates the folder
• Test with olMail.Display first to avoid accidental sends

内容的提问来源于stack exchange，提问作者Kaustubh Gurudatt Kamat