VBA References Q
 

I have some VBA code that e-mails an Excel file, this works fine (created in
Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The problem
that I am having on the XP / 2000 PC's is that I get a Compile error and it
appears to be because my "Microsoft outlook 11.0 Object Library" is missing.
When I manually select "Microsoft outlook 10.0 Object Library" on these Xp /
2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select the
appropriate Outlook Object Library when other machines (other than Excel
2003) open it?

Thanks

VBA References Q
 

Hi John
Use late binding.
Regards
JY
"John" wrote in message
...
I have some VBA code that e-mails an Excel file, this works fine (created
in Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The
problem that I am having on the XP / 2000 PC's is that I get a Compile
error and it appears to be because my "Microsoft outlook 11.0 Object
Library" is missing. When I manually select "Microsoft outlook 10.0 Object
Library" on these Xp / 2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select the
appropriate Outlook Object Library when other machines (other than Excel
2003) open it?

Thanks

VBA References Q
 

Thanks Jean-Yves

Is that a piece of code, how would I construct that?

Thanks


"Jean-Yves" wrote in message
...
Hi John
Use late binding.
Regards
JY
"John" wrote in message
...
I have some VBA code that e-mails an Excel file, this works fine (created
in Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The
problem that I am having on the XP / 2000 PC's is that I get a Compile
error and it appears to be because my "Microsoft outlook 11.0 Object
Library" is missing. When I manually select "Microsoft outlook 10.0
Object Library" on these Xp / 2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select the
appropriate Outlook Object Library when other machines (other than Excel
2003) open it?

Thanks

VBA References Q
 

Below is my current mail code

Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
Dim wks As Worksheet
On Error Resume Next
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets("E-Sales Hours").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Schedule", "E-Sales Hours", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"

For Each wks In Worksheets
wks.Protect Password:="xyz"
Next

MyArr = Sheets("E-Schedule").Range("AG1:AG3")
.SendMail MyArr, Sheets("E-Schedule").Range("AG4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets(Array("E-Schedule")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Sales Hours")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Import")).Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
On Error GoTo 0
End Sub



"John" wrote in message
...
Thanks Jean-Yves

Is that a piece of code, how would I construct that?

Thanks


"Jean-Yves" wrote in message
...
Hi John
Use late binding.
Regards
JY
"John" wrote in message
...
I have some VBA code that e-mails an Excel file, this works fine (created
in Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The
problem that I am having on the XP / 2000 PC's is that I get a Compile
error and it appears to be because my "Microsoft outlook 11.0 Object
Library" is missing. When I manually select "Microsoft outlook 10.0
Object Library" on these Xp / 2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select the
appropriate Outlook Object Library when other machines (other than Excel
2003) open it?

Thanks

VBA References Q
 

Hi John,
In your code, there is no evidence of any Outlook object, mehod or property.
Probably you made a ref to Outlook to explore the possibilities.
Just remove that refeference and test/run your sub.
Still, the send mail method is firing the Outlook security warning and the
user has to agree
to let XL send mail.
Regards
JY

"John" wrote in message
...
Below is my current mail code

Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
Dim wks As Worksheet
On Error Resume Next
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets("E-Sales Hours").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Schedule", "E-Sales Hours", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"

For Each wks In Worksheets
wks.Protect Password:="xyz"
Next

MyArr = Sheets("E-Schedule").Range("AG1:AG3")
.SendMail MyArr, Sheets("E-Schedule").Range("AG4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets(Array("E-Schedule")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Sales Hours")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Import")).Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
On Error GoTo 0
End Sub



"John" wrote in message
...
Thanks Jean-Yves

Is that a piece of code, how would I construct that?

Thanks


"Jean-Yves" wrote in message
...
Hi John
Use late binding.
Regards
JY
"John" wrote in message
...
I have some VBA code that e-mails an Excel file, this works fine
(created in Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The
problem that I am having on the XP / 2000 PC's is that I get a Compile
error and it appears to be because my "Microsoft outlook 11.0 Object
Library" is missing. When I manually select "Microsoft outlook 10.0
Object Library" on these Xp / 2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select the
appropriate Outlook Object Library when other machines (other than
Excel 2003) open it?

Thanks

VBA References Q
 

Thanks Jean-Yves

Most of my code was via Ron De Bruin, with a slight tweak. Not sure why I
can selected the Outlook Object library in References, I've removed it and
e-mailed and it looks fine. Perhaps I didn't need it at all

Thanks


"Jean-Yves" wrote in message
...
Hi John,
In your code, there is no evidence of any Outlook object, mehod or
property.
Probably you made a ref to Outlook to explore the possibilities.
Just remove that refeference and test/run your sub.
Still, the send mail method is firing the Outlook security warning and the
user has to agree
to let XL send mail.
Regards
JY

"John" wrote in message
...
Below is my current mail code

Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
Dim wks As Worksheet
On Error Resume Next
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets("E-Sales Hours").Visible = True
Sheets("E-Import").Visible = True
Sheets(Array("E-Schedule", "E-Sales Hours", "E-Import")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ".xls"

For Each wks In Worksheets
wks.Protect Password:="xyz"
Next

MyArr = Sheets("E-Schedule").Range("AG1:AG3")
.SendMail MyArr, Sheets("E-Schedule").Range("AG4").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets(Array("E-Schedule")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Sales Hours")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets(Array("E-Import")).Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Home").Select
Range("A1").Select
On Error GoTo 0
End Sub



"John" wrote in message
...
Thanks Jean-Yves

Is that a piece of code, how would I construct that?

Thanks


"Jean-Yves" wrote in message
...
Hi John
Use late binding.
Regards
JY
"John" wrote in message
...
I have some VBA code that e-mails an Excel file, this works fine
(created in Excel 2003).

This same file is issued to other PC's that Run Excel XP / 2000. The
problem that I am having on the XP / 2000 PC's is that I get a Compile
error and it appears to be because my "Microsoft outlook 11.0 Object
Library" is missing. When I manually select "Microsoft outlook 10.0
Object Library" on these Xp / 2000 machines everything works fine.

As I maintain the file on Excel 2003 how can I automatically select
the appropriate Outlook Object Library when other machines (other than
Excel 2003) open it?

Thanks