Save copy to temp with only values and send to mail

stjuch

New Member
Joined
Sep 23, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I need to change my code to save the temp file with only values (after it has been calculated) and only send the workbook with no formulas with no changes to the original workbook. I can't seem to get the value part right without also deleting the formulas in the original sheet. Anyone who can help me get this in?

Here's the code:
VBA Code:
Sub SendMail()
    Dim wb1 As Workbook
    Dim wal As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim answer As Integer
   
    answer = MsgBox("Vil du sende arket?", vbQuestion + vbYesNo + vbDefaultButton1, "Bekræft send")
    If answer = vbYes Then
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculate
    End With

    Set wb1 = ActiveWorkbook
   
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Hensættelse_" & Range("B3").Value & "_" & Format(Now, "ddmmyyHHmmss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
   
    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .to = "mail@mail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Hensættelse " & Range("B3").Value
        .Body = ""
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        .OutMail.HTMLBody
        .Send
    End With
    On Error GoTo 0

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    MsgBox "Arket er sendt."
    Else
    MsgBox "Arket er ikke sendt!"
    End If
End Sub

Thanks!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
The following code will get rid of formula. Change it to suit your need.

VBA Code:
With ThisWorkbook.Sheets(1).UsedRange
    .Value = .Value
End With
 
Upvote 0
The following code will get rid of formula. Change it to suit your need.

VBA Code:
With ThisWorkbook.Sheets(1).UsedRange
    .Value = .Value
End With
Hi yky,

Thanks for your help. When I try to include this code I get an error "1004" application-defined or object-defined error.

Where do I need to include this to make sure only the saved copy is converted to values and not the original sheet?

Thanks!
 
Upvote 0
I gave a basic example of how to keep the value while getting rid of formula. You need to modify it to suit your need.

It looks like you want to save the entire workbook with values only. You declared wa1 but didn't use it. I'll just use it. Try this:

VBA Code:
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr"   <= add after this line
Set wb1 = ActiveWorkbook
For Each wa1 In wb1.Sheets

wa1.UsedRange.Value = wa1.UsedRange.Value

Next wa1

Test it on a copy first, not the original workbook.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top