VBA References Q

[John]

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

 

[Jean-Yves]

Hi John
Use late binding.
Regards
JY

 

[John]

Thanks Jean-Yves

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

Thanks

 

[John]

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

 

[Jean-Yves]

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]

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