VBA & Macro Problems

Email an Excel Worksheet as a Values-Only Attachment with VBA

Muhammad TalhaMuhammad Talha Nov 22, 2026 869 views

Question details

The user needs to email a copy of the active Excel worksheet with cell formulas replaced by values, then remove the temporary attachment file.

Email an Excel Worksheet as a Values-Only Attachment with VBA
Product
Microsoft Excel and Microsoft Outlook
Device & OS
not provided
Scenario
A VBA macro copies the active worksheet into a temporary workbook and attaches it to an Outlook email.
Observed behavior
The existing macro retains formulas, and the attempted conversion refers to an undefined destination workbook.
Before you start

Use desktop Excel with VBA enabled and classic Outlook for Windows configured with a sending account; the new Outlook for Windows does not support this VBA automation. Select the worksheet you want to send and prepare the recipient address before running the macro.

Solution 1Recommended

Copy the active worksheet as values and send it through Outlook

Use this macro to create a separate XLSX attachment, replace the copied sheet's cell formulas with their current values, and delete the temporary file after Outlook submits the message.

The macro assigns the newly created workbook to tempBook immediately after copying the sheet, avoiding an undefined destination reference. Replacing the used range with its own values leaves the original worksheet unchanged.

The attachment is saved as XLSX, so VBA code in the copied sheet is not retained. Cell formatting is generally preserved, but this does not remove formulas from objects such as conditional formatting or defined names.

1
Open the VBA editor

In desktop Excel, press Alt+F11, then choose Insert > Module. Save the workbook containing the macro as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code.

2
Add the macro

Paste the following code into the module. Replace recipient@example.com and the subject before running it: Sub EmailActiveSheetAsValues() Dim sourceSheet As Worksheet Dim tempBook As Workbook Dim outlookApp As Object Dim mail As Object Dim tempFile As String Dim baseName As String Dim counter As Long Dim oldAlerts As Boolean Dim errorText As String Dim submitted As Boolean oldAlerts = Application.DisplayAlerts On Error GoTo Failed Set sourceSheet = ActiveSheet sourceSheet.Copy Set tempBook = ActiveWorkbook With tempBook.Worksheets(1).UsedRange .Value = .Value End With baseName = Environ$("TEMP") & "\ExcelSheet_" & Format$(Now, "yyyymmdd_hhnnss") tempFile = baseName & ".xlsx" Do While Len(Dir$(tempFile)) > 0 counter = counter + 1 tempFile = baseName & "_" & counter & ".xlsx" Loop Application.DisplayAlerts = False tempBook.SaveAs Filename:=tempFile, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = oldAlerts tempBook.Close SaveChanges:=False Set tempBook = Nothing Set outlookApp = CreateObject("Outlook.Application") Set mail = outlookApp.CreateItem(0) With mail .To = "recipient@example.com" .Subject = "Worksheet values" .Body = "Please see the attached worksheet." .Attachments.Add tempFile .Send End With submitted = True GoTo CleanUp Failed: errorText = Err.Description CleanUp: On Error Resume Next Application.DisplayAlerts = oldAlerts If Not tempBook Is Nothing Then tempBook.Close SaveChanges:=False If Len(tempFile) > 0 Then If Len(Dir$(tempFile)) > 0 Then Kill tempFile End If On Error GoTo 0 If submitted Then MsgBox "The message was submitted to Outlook. Check your temporary folder if file cleanup failed." Else MsgBox "The message was not submitted: " & errorText End If End Sub

3
Run the macro

Return to Excel, select the worksheet to send, press Alt+F8, select EmailActiveSheetAsValues, and click Run. The macro sends immediately through Outlook rather than opening a draft.

4
Check the result

Check Outlook's Outbox or Sent Items for the message. If cleanup fails, open the folder shown by entering %TEMP% in Windows File Explorer and remove the ExcelSheet_*.xlsx file created by the macro.

Copy the active worksheet as values and send it through Outlook
Outlook submission and cleanup: Outlook may display a security prompt or queue the message, and submission does not guarantee delivery. The macro attempts to delete the temporary file after .Send; check the temporary folder if Excel or another process prevents deletion.
Free Microsoft Office alternative

Work with Excel worksheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Microsoft Office formats, making it easier to move existing files into a different workspace. It does not fix Excel-to-Outlook VBA automation.

Open and edit worksheets in a familiar spreadsheet interface.Work with Microsoft Excel XLSX and XLS files for easier file migration.Use a free, lightweight office suite for everyday documents and spreadsheets.
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro change formulas in my original worksheet?

No. The macro uses sourceSheet.Copy to create a new workbook, then replaces the used range's formulas with values in the copied sheet.

Can I review the email before sending it?

Yes. Replace .Send with .Display in the VBA code, but do not immediately delete the temporary file in a draft workflow. Arrange cleanup after you have sent or discarded the message.

Why does the Outlook automation fail?

Check that classic Outlook for Windows is installed and configured with a sending account. The new Outlook for Windows does not support the COM automation used by CreateObject("Outlook.Application"). Outlook security prompts may also require attention.

Does replacing the used range remove every formula from the workbook?

No. The assignment replaces cell formulas in the copied sheet's used range with their current values. Formulas in conditional formatting, defined names, or other workbook objects may remain.

Where is the temporary attachment saved?

The macro saves it in the Windows temporary folder using Environ$("TEMP"). Enter %TEMP% in File Explorer to check for an ExcelSheet_*.xlsx file if deletion fails.