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:
Thanks!!
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!!