Run-time error '1004'???

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

TotallyConfused

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
 
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
 
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
 
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.
 
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
 
Back
Top