Early binding issue, reference to Microsoft Excel not working

[Bering]

Hello,

I have this macro that is stored in Microsoft Outlook to send out emails, it works perfectly fine for me but not for my new colleague.

The only requirement is to have the excel file LOG 2.xlsm open before running it.

For my colleague it stops here and will not work unless she restarts her PC.

Set wb = myXL.Workbooks("LOG 2.xlsm") '<-- Wb already open. Rename as appropriate

The file is saved in the same location for all of us and she is using the same version of Excel.

I am using the below references:

Visual Basic for Applications
Microsoft Outlook 16.0 Object Library
Microsoft Office 16.0 Object Library
Microsoft Excel 16.0 Object Library
Microsoft Scripting Runtime
Accessibility CpIAdmin 1.0 Type Library


Any idea is much appreciated!! Thank you

Option Explicit
Sub Send_Email_to_Investors()
Dim olMsg As MailItem
    
    Set olMsg = ActiveExplorer.Selection.Item(1)
    Email_to_Investors
lbl_Exit:
    Set olMsg = Nothing
    Exit Sub
End Sub
Sub Email_to_Investors()
    
     Dim myMail As Outlook.MailItem
     Dim wb As Excel.Workbook 'Early bind - needs a reference to Excel
     Dim myXL As Excel.Application 'Early bind - needs a reference to Excel
     Dim mySh As Worksheet
     Dim myBody, Signature, sPath As String, strFile As String, strFolderPath As String, ext As String
     Dim oAttachments As Outlook.Attachments
     Dim StrSignature, signImageFolderName, completeFolderPath As String
     Dim strLocation, strFileName, strFileExt, strFileName1, strFileExt1, pass As String
     Dim i As Long, fCol As Long, lCol As Long, iCount As Long
     Dim stExport As Variant
     Dim sFolder As String
     Dim aFolders() As Variant
     Dim iFolderCount As Integer

    
        
     Set myXL = GetObject(, "Excel.Application") 'Excel already running
     Set wb = myXL.Workbooks("LOG 2.xlsm") '<-- Wb already open. Rename as appropriate

 

[RoryA]

Does she get an error? If so, what is the message?

 

[Bering]

Does she get an error? If so, what is the message?

Yes, the error is

Run-time error '9':
Subscript out of range

Thank you!!

 

[Georgiboy]

I get that error if i have more than one spreadsheet open.

How many instances of Excel are open when you run the code in Outlook?

 

[Bering]

I get that error if i have more than one spreadsheet open.

How many instances of Excel are open when you run the code in Outlook?

Thank you. We tried with just one spreadsheet open, but she still got the issue. Only restarting seems to fix the issue..

 

[Georgiboy]

Do you have any code in any other files that are used that may make an instance of Excel not visible at any point?

 

[Bering]

Do you have any code in any other files that are used that may make an instance of Excel not visible at any point?

no, I don't think so but I will double check that. Thank you

EDIT: perhaps using the full path could fix the issue?

 

[Georgiboy]

Could also see if you get a different result if you open the xlsm file from the VBA rather than having it open already?

 

[Bering]

Could also see if you get a different result if you open the xlsm file from the VBA rather than having it open already?

That's a good idea, I'll try that, thanks

 

[RoryA]

If you use the full path with getobject it will grab the correct workbook, and open it if required.