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 "(E-Mail Removed)", "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

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" <(E-Mail Removed)> wrote in message news:4cc5eaac-a74f-4569-9e72-(E-Mail Removed)...
> 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 "(E-Mail Removed)", "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

On Jan 4, 11:08 am, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <jtkinse...@hotmail.com> wrote in messagenews:4cc5eaac-a74f-4569-9e72-(E-Mail Removed)...
> > 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 "someb...@somewhere.com", "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