Runtime error 1004 - cannot open the file

J

Jen

Hello. I have a macro assigned to a button to send an email. This is
working for all users here except for two. Two users receive the error:
'Runtime error 1004 - Excel cannot open the file 'File Name.xlsm' because the
file format or file extension is not valid. Verify that the file has not
been corrupted and that the file extension matches the format of the file.'

There error happens on this line of code:
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Here is all of the code:
Sub MailWorksheet()
'Macro to send email
'http://www.rondebruin.nl/tips.htm

Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & "\"
Set wb1 = ActiveWorkbook


If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
If wb1.HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If

End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
TempFileName = ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm")
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

On Error Resume Next

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

On Error Resume Next
With OutMail
.To = "email here"
.CC = ""
.BCC = ""
.Subject = "Purchase Order"
.Attachments.Add ActiveWorkbook.FullName
.Send
MsgBox "Your purchase order has been sent. Thank you.",
vbOKOnly
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
On Error GoTo 0
wb2.Close SaveChanges:=False
Kill TempFilePath & TempFileName & FileExtStr
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top