ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Runtime error 1004 - cannot open the file (https://www.excelbanter.com/excel-programming/423987-runtime-error-1004-cannot-open-file.html)

Jen

Runtime error 1004 - cannot open the file
 
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




All times are GMT +1. The time now is 08:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com