Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert all 3d references to normal references in a workboo | Excel Discussion (Misc queries) | |||
How to convert all 3d references to normal references in a workboo | Excel Worksheet Functions | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Tools | References - information about references | Excel Programming |