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
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