如何修改Excel宏：将PDF导出改为保存并发送新Excel工作表

Hey there! Let's walk through exactly what you need to adjust in your VBA code to swap out the PDF export for a new Excel workbook, then attach that workbook to your email.

Key Adjustments to Make #

Here are the core changes you'll need to implement:

1. Replace PDF export with Excel workbook creation & saving
   Your original code uses ExportAsFixedFormat to generate a PDF. Instead, we'll create a brand new blank workbook, copy your formatted content into it, then save that workbook to your desired location.

2. Update the email attachment target
   Instead of attaching the PDFFile path, you'll now attach the path of your newly saved Excel file.

3. Optional: Clean up temporary Excel file
   If you don't need to keep the workbook after sending the email, you can add code to delete it once the email is handled.

Modified Code Example #

Here's your updated code with these changes applied (I've added comments to highlight what's new):

Sub CopyRangeAndSendAsExcel()
    Dim myRange As Range
    Dim newWorkbook As Workbook
    Dim ExcelFile As String
    
    ' --- Original content copying logic (kept the same) ---
    Sheets("Sheet1").UsedRange.ClearContents
    Set myRange = Application.Union(Range("A1:E3"), Range("A5:E5"), Range("A8:E17"), Range("A19:E48"), Range("A49:E77"))
    myRange.Copy Worksheets("Sheet1").Range("A1")
    
    ' --- New Excel workbook creation & saving logic ---
    ' Define your save path (adjust this to your desired location/filename)
    ExcelFile = Environ("USERPROFILE") & "\Desktop\ExportedData.xlsx"
    
    ' Create a new blank workbook
    Set newWorkbook = Workbooks.Add
    
    ' Copy the used range from Sheet1 to the new workbook's first sheet
    ThisWorkbook.Sheets("Sheet1").UsedRange.Copy
    newWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    newWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
    
    ' Save the new workbook
    newWorkbook.SaveAs Filename:=ExcelFile, FileFormat:=xlOpenXMLWorkbook
    
    ' Close the new workbook (since we don't need it open anymore)
    newWorkbook.Close SaveChanges:=False
    
    ' --- Now attach this Excel file to your email (replace with your existing email code) ---
    ' Example email code (adjust as needed for your setup):
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    With OutMail
        .To = "recipient@example.com"
        .CC = ""
        .BCC = ""
        .Subject = "Exported Excel Data"
        .Body = "Attached is the exported data in Excel format."
        .Attachments.Add ExcelFile ' Attach the new Excel file instead of PDF
        .Send ' Use .Display if you want to preview the email first
    End With
    On Error GoTo 0
    
    ' --- Optional: Delete the temporary Excel file after sending ---
    Kill ExcelFile
    
    ' Clean up objects
    Set OutMail = Nothing
    Set OutApp = Nothing
    Set newWorkbook = Nothing
    Set myRange = Nothing
End Sub

Quick Notes on the Changes: #

• We use Workbooks.Add to create a new blank workbook, then copy both values/number formats and cell formats from your Sheet1 to ensure the layout matches.
• The ExcelFile variable defines where the new workbook is saved (I used the Desktop as an example—adjust this path to your needs).
• The email attachment line now uses ExcelFile instead of PDFFile.
• The optional Kill command deletes the temporary file after sending; remove this if you want to keep the workbook.

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