Runtime error 1004 - cannot open the file

J

Jen

Hello. I have assigned a macro to a button to have a workbook emailed. This
works for all users here except for two. Two users receive 'Runtime error
1004 - Excel cannot open the file 'FileName.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.'

It errors for these two users on this line:
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Thanks!


Here is the entire 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 address 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