VBA Code: Email tab with pasted values

[molly13]

Hello,

I am trying to create a macro to set up an email with the current tab. I found a macro online and edited it slightly, and it works. But now, I want to change the macro so that the tab that is emailed is emailed with values instead of formulas. Does anybody know what code I can add so that the temporary file I've created is with pasted values (without pasting values on the master tab? Here is my code below:

Thank you very much!!!

Sub Email_Sheet()


Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy


Set LWorkbook = ActiveWorkbook

'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name

On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs Filename:=LFileName

'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)


'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
.Subject = "Weekly Report [" & Range("E8") & " " & Range("E7") & "]"
.body = "Please see attachment. Thank you very much."
.Attachments.Add LWorkbook.FullName
.Display
End With

'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False

'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

 

[FormR]

Hi, welcome to the forum!

Try this directly after the "Set LWorkbook = ActiveWorkbook" line..

With LWorkbook.ActiveSheet.UsedRange
    .Cells.Value = .Cells.Value
End With

 

[molly13]

That worked beautifully. Thanks so much!!