Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Error 1004 is due to the fact that wb2 is not defined in the code.

You need to set wb2 to a workbook name

set wb2 = SaveCopyAs(TempFilePath & TempFileName & FileExtStr)

You also can't use a slash ifin a filename a will have an error in this
statment

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")

Replace theh slashes with underscore

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd_mmm_yy")

You can also simplify this statemnt

from
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

to
FileExtStr = LCase(mid(wb1.Name, InStrRev(wb1.Name, ".")))


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147452

The lines to be replaced are....

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy")
FileExtStr = Mid(wb1.Name, InStrRev(wb1.Name, "."))

If this post helps click Yes
---------------
Jacob Skaria


"TotallyConfused" wrote:

Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

In the first line that's highlighted as an error (wb2.SaveCopyAs...), you
have a reference to the object variable wb2 but you haven't used the Set
statement yet to assign it a value.

Hope this helps,

Hutch

"TotallyConfused" wrote:

Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thank you. Sorry for asking but where to do I set the statement for wb2?

"Tom Hutchins" wrote:

In the first line that's highlighted as an error (wb2.SaveCopyAs...), you
have a reference to the object variable wb2 but you haven't used the Set
statement yet to assign it a value.

Hope this helps,

Hutch

"TotallyConfused" wrote:

Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thank you Jacob, but I am sorry to say that I replaced the lines and when I
click on the button it just hangs. A little better but not quite. Anything
else I can do? Thank you.

"Jacob Skaria" wrote:

The lines to be replaced are....

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy")
FileExtStr = Mid(wb1.Name, InStrRev(wb1.Name, "."))

If this post helps click Yes
---------------
Jacob Skaria


"TotallyConfused" wrote:

Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy")
FileExtStr = Mid(wb1.Name, InStrRev(wb1.Name, "."))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Set OutApp = CreateObject("Outlook.Application")

If this post helps click Yes
---------------
Jacob Skaria


"TotallyConfused" wrote:

Thank you Jacob, but I am sorry to say that I replaced the lines and when I
click on the button it just hangs. A little better but not quite. Anything
else I can do? Thank you.

"Jacob Skaria" wrote:

The lines to be replaced are....

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy")
FileExtStr = Mid(wb1.Name, InStrRev(wb1.Name, "."))

If this post helps click Yes
---------------
Jacob Skaria


"TotallyConfused" wrote:

Hi I am sorry but I need help with this. I have UserForm that has command
button that generates the code below. When I click on the button it gives
me error message with the following:

Run-time error "1004" and takes me to the code where the following is
highlighted in yellow. These are the two lines highlighted in yellow.

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Can you please tell what is wrong with this? Below is the code for the
button. I need to get this up an running asap.


Private Sub Outmail_Click()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim Outmail As Object

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

wb2.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

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

On Error Resume Next
With Outmail
.To = "myemailaddress"
.CC = ""
.BCC = ""
.Subject = "Request"
.Body = "Request attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
On Error GoTo 0

wb2.Close SaveChanges:=False

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set Outmail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thank you very much. It worked.

"joel" wrote:


Error 1004 is due to the fact that wb2 is not defined in the code.

You need to set wb2 to a workbook name

set wb2 = SaveCopyAs(TempFilePath & TempFileName & FileExtStr)

You also can't use a slash ifin a filename a will have an error in this
statment

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd/mmm/yy")

Replace theh slashes with underscore

TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd_mmm_yy")

You can also simplify this statemnt

from
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

to
FileExtStr = LCase(mid(wb1.Name, InStrRev(wb1.Name, ".")))


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147452

.