Excel 2000 vs 2003 programming problem?

[Andre Croteau]

Hello,

I have been using the macro below (originally seen in Ron DeBruin's website)
to send a file by email using Excel 2000, and all is OK.

I have sent this file to a work colleague who has Excel 2003, and a problem
comes up when he runs the macro, but I don't know why it doesn't work.

The error states that Excel 2003 "cannot access the Read only file". When I
click on "debug", the VBA Editor highlights this line of code in yellow:


"wb1.SaveCopyAs wbname"


Can anyone advise me if this an Excel problem, or can it be from Outlook
2003?
Any help will be greatly appreciated.

Also is there a way to include program code so that Excel does NOT ask to
confirm before sending an email. I realise this protection is to avoid the
distribution of malicious code but what if I am a trusted source??

Thank you in advance

rgds

André


Original code:

Sub Mail_Workbook()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String
Dim emplname As String

emplname = Worksheets("BTA").Cells(2, 4).Value

Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
wbname = "C:\" & emplname & ".xls"
wb1.SaveCopyAs wbname
chDir "c:\"
Set wb2 = Workbooks.Open(wbname)
With wb2
.SendMail "(e-mail address removed)", _
"Travel Request for Approval - " & emplname
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

 

[Jim Rech]

wb1.SaveCopyAs wbname

It would seem that a file with the target name already exists an it is
read-only. At least when I set that up I get the same error message.

--
Jim
| Hello,
|
| I have been using the macro below (originally seen in Ron DeBruin's
website)
| to send a file by email using Excel 2000, and all is OK.
|
| I have sent this file to a work colleague who has Excel 2003, and a
problem
| comes up when he runs the macro, but I don't know why it doesn't work.
|
| The error states that Excel 2003 "cannot access the Read only file". When
I
| click on "debug", the VBA Editor highlights this line of code in yellow:
|
|
| "wb1.SaveCopyAs wbname"
|
|
| Can anyone advise me if this an Excel problem, or can it be from Outlook
| 2003?
| Any help will be greatly appreciated.
|
| Also is there a way to include program code so that Excel does NOT ask to
| confirm before sending an email. I realise this protection is to avoid
the
| distribution of malicious code but what if I am a trusted source??
|
| Thank you in advance
|
| rgds
|
| André
|
|
| Original code:
|
| Sub Mail_Workbook()
| Dim wb1 As Workbook
| Dim wb2 As Workbook
| Dim wbname As String
| Dim emplname As String
|
| emplname = Worksheets("BTA").Cells(2, 4).Value
|
| Application.ScreenUpdating = False
| Set wb1 = ActiveWorkbook
| wbname = "C:\" & emplname & ".xls"
| wb1.SaveCopyAs wbname
| chDir "c:\"
| Set wb2 = Workbooks.Open(wbname)
| With wb2
| .SendMail "(e-mail address removed)", _
| "Travel Request for Approval - " & emplname
| .ChangeFileAccess xlReadOnly
| Kill .FullName
| .Close False
| End With
| Application.ScreenUpdating = True
| End Sub
|
|