Code not working since upgrade to 2007
 

Hello all. I just recently upgraded to Excel 2007 and one part of my
old code is not working. Wondering if any of you have run into this
and might have a quick and easy solution.
Here is the code that is bombing
Sub sendbook()
On Error GoTo mailerror
Dim wb As Workbook
Application.ScreenUpdating = False
Dim currdate As String
currdate = Format(frmMain.DTPicker1.Value, "dd-mmm-yy")
Sheets(currdate).Select
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Daily Sales " & currdate & ".xls"
.PrintOut
.SendMail ", "Daily Sales for " &
currdate
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close True
End With
Application.ScreenUpdating = True
MsgBox "Your mail has been sent", vbInformation, "Sucessful Mail
Send"
Exit Sub
mailerror:
MsgBox "There was a problem sending your mail." & vbLf & "Please
try to resend." _
& vbLf & "If you get this error a second time please contact
me", vbCritical, _
"Mail Send Error"
ActiveWorkbook.Close
Application.ScreenUpdating = True
Call cleanup
End Sub

It is now throwing an error on Kill .Fullname and then showing the
mesage box for mailerror. The path to the file is correct and I can
manually delete the file. Wondering if anyone has run into this before
and has a quick solution or work around. I am not excatly what you
would call a programmer as I am sure you can already tell.
Cheers and Thank you.
Terry

Code not working since upgrade to 2007
 

hi Terry

All mail code on my site is working now in almost all Excel versions

Check out the new code
http://www.rondebruin.nl/sendmail.htm


--

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


"Terry K" wrote in message ...
Hello all. I just recently upgraded to Excel 2007 and one part of my
old code is not working. Wondering if any of you have run into this
and might have a quick and easy solution.
Here is the code that is bombing
Sub sendbook()
On Error GoTo mailerror
Dim wb As Workbook
Application.ScreenUpdating = False
Dim currdate As String
currdate = Format(frmMain.DTPicker1.Value, "dd-mmm-yy")
Sheets(currdate).Select
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Daily Sales " & currdate & ".xls"
.PrintOut
.SendMail ", "Daily Sales for " &
currdate
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close True
End With
Application.ScreenUpdating = True
MsgBox "Your mail has been sent", vbInformation, "Sucessful Mail
Send"
Exit Sub
mailerror:
MsgBox "There was a problem sending your mail." & vbLf & "Please
try to resend." _
& vbLf & "If you get this error a second time please contact
me", vbCritical, _
"Mail Send Error"
ActiveWorkbook.Close
Application.ScreenUpdating = True
Call cleanup
End Sub

It is now throwing an error on Kill .Fullname and then showing the
mesage box for mailerror. The path to the file is correct and I can
manually delete the file. Wondering if anyone has run into this before
and has a quick solution or work around. I am not excatly what you
would call a programmer as I am sure you can already tell.
Cheers and Thank you.
Terry

Code not working since upgrade to 2007
 

On Jan 4, 11:08 am, "Ron de Bruin" wrote:
hi Terry

All mail code on my site is working now in almost all Excel versions

Check out the new codehttp://www.rondebruin.nl/sendmail.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Terry K" wrote in ...
Hello all. I just recently upgraded to Excel 2007 and one part of my
old code is not working. Wondering if any of you have run into this
and might have a quick and easy solution.
Here is the code that is bombing
Sub sendbook()
On Error GoTo mailerror
Dim wb As Workbook
Application.ScreenUpdating = False
Dim currdate As String
currdate = Format(frmMain.DTPicker1.Value, "dd-mmm-yy")
Sheets(currdate).Select
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Daily Sales " & currdate & ".xls"
.PrintOut
.SendMail ", "Daily Sales for " &
currdate
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close True
End With
Application.ScreenUpdating = True
MsgBox "Your mail has been sent", vbInformation, "Sucessful Mail
Send"
Exit Sub
mailerror:
MsgBox "There was a problem sending your mail." & vbLf & "Please
try to resend." _
& vbLf & "If you get this error a second time please contact
me", vbCritical, _
"Mail Send Error"
ActiveWorkbook.Close
Application.ScreenUpdating = True
Call cleanup
End Sub

It is now throwing an error on Kill .Fullname and then showing the
mesage box for mailerror. The path to the file is correct and I can
manually delete the file. Wondering if anyone has run into this before
and has a quick solution or work around. I am not excatly what you
would call a programmer as I am sure you can already tell.
Cheers and Thank you.
Terry

Thanks Ron,
It looks like you have the perfect correction on your site. I will
update what I have and post the corrected code when complete.
Thank you very much for your help.
Cheers, Terry