CODEWITHSUNDEEP
vbaexcelerror-handlingoutlook
CaffeinatedMike
CaffeinatedMike

Reputation: 1607

File sent as email attachment via Excel VBA is always corrupt

I'm using the following error handling method to save a copy of the file that's currently open and send it to my email if it causes an error.

Private Declare Function GetTempPath _
Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Sub MainSub()
Dim OutApp As Object, OutMail As Object
Dim wb As Workbook
On Error GoTo NotifyandRepair
    Call Sub1
    Call Sub2
    Call Subn

Exit Sub
NotifyandRepair:
Set wb = ThisWorkbook

Application.DisplayAlerts = False
wb.SaveAs TempPath & "ErroringFile.xlsx", FileFormat:= _xlNormal,AccessMode:=xlExclusive,ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
Application.DisplayAlerts = True

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "[email protected]"
.Subject = "Error Occured - Error Number " & Err.Number
.Body = Err.Description
.Attachments.Add TempPath & "ErroringFile.xlsx"

.Send '~~> Change this to .Display for displaying the email
End With

Set OutApp = Nothing: Set OutMail = Nothing
End Sub

Function TempPath() As String
TempPath = String$(MAX_PATH, Chr$(0))
GetTempPath MAX_PATH, TempPath
TempPath = Replace(TempPath, Chr$(0), "")
End Function

It appears to work fine. When an unhandled error occurs it sends me a copy of the file and its been renamed "ErroringFile.xlsx". The problem is the file is always corrupt.

Am I doing something wrong?

How do I fix this issue so the file isn't corrupt?

Upvotes: 1

Views: 1191

Answers (1)

PatricK
PatricK

Reputation: 6433

It's the wrong FileFormat you SaveAs.

See XlFileFormat Enumeration, you should be saving it as xlOpenXMLWorkbook.

Upvotes: 2

Related Questions