Code not running on other PCs, I've located which line has the issue

[IgorDavydov]

Hi,
I've created this abomination of a Macro in VBA from few different codes, and it seems to work fine for me.
I've sent it to my colleague and we made sure he has the same references enabled, Macros enabled, files with the same names. When we try to run it on his PC however, it doesn't do anything and doesnt produce an error message. When I went line-by-line with F8 through it, it seemed to crash after the creation of the input box pop-up (we select the range and it doesn't do anything) Set xRg = Application.InputBox("Please select email address range", "DSV", xAddress, , , , , 8)
Any ideas how to fix this? Is it because of ActiveWindow?
I have the getboiler as function in the same module.

Sub SendEmailToAddressInCells()
    Dim xRg As Range
    Dim xRgEach As Range
    Dim xRgVal As String
    Dim xAddress As String
    Dim xOutApp As Outlook.Application
    Dim xMailOut As Outlook.MailItem
    Dim SigString As String
    Dim strbody As String
    Dim Signature As String

  
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select email address range", "DSV", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xRg = xRg.SpecialCells(xlCellTypeConstants, xlTextValues)
  
strbody = "Hi,<br>" & _
            "<br>" & _
            "Please confirm the following orders will be shipped according to the table below" & "<br>" & _
            "<br>" & _
            "<br>" & _
            "<br>"

    On Error Resume Next
            
    SigString = Environ("appdata") & _
                "\Microsoft\Signatures\ConfirmAuto.htm"
  


    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    For Each xRgEach In xRg
        xRgVal = xRgEach.Value
        If xRgVal Like "?*@?*.?*" Then
            Set xMailOut = xOutApp.CreateItem(olMailItem)
            With xMailOut
                .To = xRgVal
                .CC = "abc@xyz.com"
                .Subject = "Order confirmation"
                .Attachments.Add "C:\Users\igor.davydov\AppData\Roaming\Microsoft\Signatures\Confirm_files\image001.png", olByValue, 0
        sImgName = "image001.png"
                .HTMLBody = strbody & Signature & "<img src='cid:" & sImgName & "'" & " ><br>"
                .Display
                '.Send
            End With
        End If
    Next
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel!

Delete these lines "On Error Resume Next".
That line is to ignore any error and move on.
But we need to know what the mistake is.

Run again line by line with F8 and check which line the macro has an error on and what the error message says.

Go back here and comment.

 

[IgorDavydov]

Hi and welcome to MrExcel!

Delete these lines "On Error Resume Next".
That line is to ignore any error and move on.
But we need to know what the mistake is.

Run again line by line with F8 and check which line the macro has an error on and what the error message says.

Go back here and comment.

Thank you for the reply, Dante.

I've tried doing this, and am now able to go all the way down with F8. This still didn't produce any action, however, when I compared going line-by-line on my PC vs. my colleagues, it seems like his macro doesn't enter the GetBoiler function, while my code, as soon as it encounters it, goes into the function. Might that be the problem? Outside of that I don't have a clue since it still doesn't produce neither errors nor results.

 

[Alex Blakenburg]

@DanteAmor is likely to know more about this than me, but on your colleagues machine try commenting out the line below and see if it works then.
It does for me.
In the first instance it looks like it is hard coded to your machine and that needs to be addressed but you also need a way of handling the error.

                .Attachments.Add "C:\Users\igor.davydov\AppData\Roaming\Microsoft\Signatures\Confirm_files\image001.png", olByValue, 0
        sImgName = "image001.png"

 

[DanteAmor]

On your colleagues machine the GetBoiler function is missing, surely it does exist in some module in your excel file. It should be similar to this, or put the following code below the macro.

Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

 

[IgorDavydov]

On your colleagues machine the GetBoiler function is missing, surely it does exist in some module in your excel file. It should be similar to this, or put the following code below the macro.

Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Thanks, the code for GetBoiler is already in the same very module as the Sub.

@Alex Blakenburg You were right, the issue was with this line. Despite the fact that my colleague copied his path to this image with the same name, it didn't work. He instead put the image on his desktop and we updated the address, and it ran successfully. Your help is very much appreciated, all the way from Australia

 

[Alex Blakenburg]

Thank you for letting us know. Glad we could help.
PS: The time zone tends to be more of an issue then the distance