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

You change the code not correct

Use the code from this page
http://www.rondebruin.nl/mail/folder2/mail1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jen" wrote in message ...
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