Macro runs fine until I give it to a co-worker

[chefdt]

I have a slick little email macro that I cobbled together from various sources. Runs great on my machine and another co-worker, but today when we gave it to a third coworker, he got a RUNTIME 1004 error either at

sht.activate or ActiveSheet.copy

There is obviously something in his environment that is different. Any ideas?

Sub EMAILit()

'LOOPER
Dim sht As Object
  For Each sht In Sheets
   If sht.Name <> "Summary" And sht.Name <> "Emails" Then
    sht.Activate
      With sht






Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String




'Turn off screen updating
Application.ScreenUpdating = False


'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook


'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs FileName:=LFileName


'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)


'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
.To = [b1].Value
.subject = "OPCO Slow Movers - Less Than 5/week - " & LFileName
.body = "DT - RevMan Lead" & vbCrLf & vbCrLf & _
"The attached file contains a list of items that move less than 5/week average from our warehouse.  I have matched these slow moving items with the customers who ordered them.  Take the opportunity to raise your margins on these infrequently ordered items!!"


.Attachments.Add LWorkbook.FullName
.Send
End With


'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False


'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing




    
End With
    End If
Next sht




End Sub

 

[blbat]

Look for easy things first:

Does that user have security set on the machine set so Macros can't run?

Is there an Older version of Excel on the error prone machine?

if so, you might have used a Reference to a VBA Library for Outlook that the machine does not have/cannot handle.

 

[chefdt]

Look for easy things first:

Does that user have security set on the machine set so Macros can't run?

Is there an Older version of Excel on the error prone machine?

if so, you might have used a Reference to a VBA Library for Outlook that the machine does not have/cannot handle.

He is using 2010. I wrote the macro in 2013.
All macro security turned off.

 

[blbat]

Can you log into his machine and run the macro?

(isolate if it's user profile, or machine environment.)

 

[chefdt]

Can you log into his machine and run the macro?

(isolate if it's user profile, or machine environment.)

He's on a remote network

 

[blbat]

Ok...I hope one of the guru's jumps in and helps you out soon then.

The only other thing I can think of is using the Workbook name in the ActiveSheet.copy statement:

WorkBookName.ActiveSheet.copy