A
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
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