need rons help on email.(recall temp file?)

[pswanie]

i got a code from rons site to email a workbook as a attachment. to mail it
the workbook got "send" to a temp file and then there is a kill comand

for one or other reason the sheet got cleared(got the code to do that) but
it did not email the workbook. is there a way to "find" the workbook?

help much needed!!!!


thanx

(if i cant recall it. please help to change so that a copy get saved in a
folder C:\maykent. It can overrite this every time. got the code with a
command button wich we click once a week)

Any fine tuning much appreciated


Here is the code that i got...
************************************************************

Public Sub CopyIt()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "This will e-MAIL and CLEAR the entire stock sheet" & _
vbNewLine & " ENSURE TO SELECT CAREFULLY" '
Define message.
Style = vbOKCancel ' Define buttons.
Title = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbOKCancel Then ' User chose Yes.





Dim Msg2, Style2, Title2, Help2, Ctxt2, Response2, MyString2
Msg2 = "Ensure to select 'yes'…" & _
vbNewLine & "On the next security warning" ' Define message.
Style2 = vbExclamation ' Define buttons.
Title2 = "Maykent t/a KFC" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt2 = 1000 ' Define topic
' context.
' Display message.
Response2 = MsgBox(Msg2, Style2, Title2)

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.Protect Password:=""


'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 = "" & wb1.Name & " " & Format(Now, "dd-mmmm-yyyy ")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) -
InStrRev(wb1.Name, ".", , 1)))

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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Weekly Stocksheet"
.Body = ""
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.send '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



Lastrow = Range("N" & Rows.Count).End(xlUp).Row
For RowCount = 1 To Lastrow
If Range("N" & RowCount).Interior.ColorIndex = 28 Then
Range("C" & RowCount) = Range("N" & RowCount)
End If
Next RowCount


Lastrow = Range("N" & Rows.Count).End(xlUp).Row
For RowCount = 1 To Lastrow
If Range("N" & RowCount).Interior.ColorIndex = 28 Then
Range("D" & RowCount) = ""
Range("E" & RowCount) = ""
Range("F" & RowCount) = ""
Range("G" & RowCount) = ""
Range("H" & RowCount) = ""
Range("I" & RowCount) = ""
Range("J" & RowCount) = ""
Range("K" & RowCount) = ""
Range("L" & RowCount) = ""
Range("N" & RowCount) = ""

End If
Next RowCount
Range("M1") = ""

ActiveSheet.Protect Password:=""
ThisWorkbook.Save
Application.Quit



Else


ActiveSheet.Protect Password:=""
ThisWorkbook.Save

End If
End Sub



************************************************************

 

[Joel]

You need to save the workbook before you run the macro, otherwise, it saves
the file with the extension .workbook1 instead of .xls

The variable "FileExtStr" is the cause of the problem. Below is the path of
the file that is saved. replace "your_name" with your login name

If you save the workbook

C:\Documents and Settings\your_name\Local Settings\Temp\Book1.xls
25-March-2008 .xls

if you don't save the file

C:\Documents and Settings\your_name\Local Settings\Temp\Book1 25-March-2008
..book1

 

[pswanie]

to be a little more clearer...

the code used to work fine. I got it to warn the user to ensure to select
YES on the next security warning. thats the warning that comes up to say
that some program is trying to send a email on youre behalf. (i think) They
selected no. Thus the file did not mail/and aint in any of the oulook
folders. i need to try and recover the file wich was suposed to be send and
re email it. (and try and secure that this dont hapen again. either by
saving a copy and then over right it next week or somehow)


thanx joel

Phillip

 

[Joel]

Did you try the recyle bin?

The file gets put into the temp directory I posted. Then after the e-mail
portion of the code it gets deleted with the kill (from temp directory)
statement. The only place it may be is in the recycle bin.