Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Script Help in Excel 2007
Okay, so I have this script on a worksheet that sends the sheet as an email.
It worked great in '03 but now receives an error message in '07, although the email does still send okay. I think the problem is deleting the copy after it is sent. The error message is " Run time Error '70' " - " Permission Denied ". Here is my code. Thank you Private Sub CommandButton1_Click() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant MyArr = Sheets("EmailAddresses").Range("a2:a25") strdate = Format(Now, "mm-dd-yy") If Range("D4") = "" Then MsgBox "Please select Leave Type" Exit Sub End If If Range("D10") = "" Then MsgBox "Please indicate whether this is a Leave or Return notification" Exit Sub End If If Range("D10") = "Leave Notification" And Range("D14") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Leave Notification" And Range("D15") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Return Notification" And Range("D21") = "" Then MsgBox "Please fill out both Return Date fields" Exit Sub End If If Range("D10") = "Return Notification" And Range("D22") = "" Then MsgBox "Please fill out both Return Date fields" Exit Sub End If If Range("D4") = "STD/CML" And Range("D10") = "Leave Notification" And Range("D17") = "" Then MsgBox "Please list Days CAP should subtract from PTO to satisfy waiting period" Exit Sub End If If Range("D10") = "Update to prior Notification" And Range("D14") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Update to prior Notification" And Range("D15") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d7") .ChangeFileAccess xlReadOnly Kill .FullName ActiveWorkbook.FollowHyperlink "\\Dtcdat-azpx001\03667\07800\Forms\Phone Bank Badge Change Application.doc", " ", NewWindow = True .Close False End With Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Script Help in Excel 2007
Permission error 70 has to deal with your email server I believe,
and permissions, like administrator, or if you have rights to this folder etc... Sorry can't be of more help on that issue. Pay attention to order the code is carried out... it's really not that hard to read.... 1. You send you mail - (.sendmail) 2. you make the file currently open (.xlreadonly) 3. then you delete the activeworkbook... (Kill .fullname) You might also want to take a look at: http://www.rondebruin.nl/sendmail.htm He's got some great code and appears bullet-proof to me. "RoadKill" wrote: Okay, so I have this script on a worksheet that sends the sheet as an email. It worked great in '03 but now receives an error message in '07, although the email does still send okay. I think the problem is deleting the copy after it is sent. The error message is " Run time Error '70' " - " Permission Denied ". Here is my code. Thank you Private Sub CommandButton1_Click() Dim wb As Workbook Dim strdate As String Dim MyArr As Variant MyArr = Sheets("EmailAddresses").Range("a2:a25") strdate = Format(Now, "mm-dd-yy") If Range("D4") = "" Then MsgBox "Please select Leave Type" Exit Sub End If If Range("D10") = "" Then MsgBox "Please indicate whether this is a Leave or Return notification" Exit Sub End If If Range("D10") = "Leave Notification" And Range("D14") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Leave Notification" And Range("D15") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Return Notification" And Range("D21") = "" Then MsgBox "Please fill out both Return Date fields" Exit Sub End If If Range("D10") = "Return Notification" And Range("D22") = "" Then MsgBox "Please fill out both Return Date fields" Exit Sub End If If Range("D4") = "STD/CML" And Range("D10") = "Leave Notification" And Range("D17") = "" Then MsgBox "Please list Days CAP should subtract from PTO to satisfy waiting period" Exit Sub End If If Range("D10") = "Update to prior Notification" And Range("D14") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If If Range("D10") = "Update to prior Notification" And Range("D15") = "" Then MsgBox "Please fill out both Leave Date fields" Exit Sub End If Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail MyArr, "LOA Notice - " & Sheets("STD-LOA").Range("d7") .ChangeFileAccess xlReadOnly Kill .FullName ActiveWorkbook.FollowHyperlink "\\Dtcdat-azpx001\03667\07800\Forms\Phone Bank Badge Change Application.doc", " ", NewWindow = True .Close False End With Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting HTML script to Excel 2007 converts the cells format to 'General' | Excel Discussion (Misc queries) | |||
Excel 2007 Can't Save File with VB Script but it can in 97/2003format??? | Excel Discussion (Misc queries) | |||
Trying to convert a excel 2003 to 2007, but it contain script. | Excel Discussion (Misc queries) | |||
Trying to convert a excel 2003 to 2007, but it contain script. | New Users to Excel | |||
Excel 2000/XP script to Excel97 script | Excel Programming |