title: copy range and send as outlook email body
body: <p>I have the below VBA code to copy range in excel and send it as body of the email from outlook. The excel sheet is saved in server and it works perfectly fine in my computer and some other computers/laptops too. But it gives compile error for some other users. All users are connected to same network and using same version of outlook and excel 2016.  </p>
  
  <blockquote>
    <p>Compile error - Can't find project or Library</p>
  </blockquote>
  
  <p>The error comes at line mentioned as (ERROR HERE) when I click the command button running this script from other computers of my colleagues(users). Outlook and excel are 2016. I have added the outlook object library in the reference menu. Excel sheet is located on server folder <code>\\hp-fs2\Groups\H&amp;P ISM\Navigational Assessment</code> 
  Any solution for amending the code from the experts on this forum would be greatly appreciated.</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim rng As Range
  Dim OutApp As Object
  Dim OutMail As Object
  
  Sheet1.Unprotect ("so")
  Set rng = Nothing
  On Error Resume Next
  Set rng = Sheets("Sheet1").Range("B9:I22").SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  
  With Application
      .EnableEvents = False
      .ScreenUpdating = False
  End With
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  On Error Resume Next
  With OutMail
      .To = "abcdg@abcd.com"
      .CC = ""
      .BCC = ""
      .Subject = "This is Automatic Notification - Navigation Assessment Overview sheet has just been updated !"
      .HTMLBody = RangetoHTML(rng)
      '.Send
      .display
  End With
  On Error GoTo 0
  
  With Application
      .EnableEvents = True
      .ScreenUpdating = True
  End With
  
  Set OutMail = Nothing
  Set OutApp = Nothing
  
  Sheet1.Protect ("so")
  End Sub
  
  Function RangetoHTML(rng As Range)
  
  Dim fso As Object
  Dim ts As Object
  Dim TempFile As String
  Dim TempWB As Workbook
  
  TempFile = Environ$("temp") &amp; "\" &amp; Format(Now, "dd-mm-yy h-mm-ss") &amp; ".htm"
  
  '( ERROR HERE - at the line TempFile = Environ$ )
  
  rng.Copy
  Set TempWB = Workbooks.Add(1)
  With TempWB.Sheets(1)
      .Cells(1).PasteSpecial Paste:=8
      .Cells(1).PasteSpecial xlPasteValues, , False, False
      .Cells(1).PasteSpecial xlPasteFormats, , False, False
      .Cells(1).Select
      Application.CutCopyMode = False
      On Error Resume Next
      .DrawingObjects.Visible = True
      .DrawingObjects.Delete
      On Error GoTo 0
  End With
  
  'Publish the sheet to a htm file
  With TempWB.PublishObjects.Add( _
       SourceType:=xlSourceRange, _
       Filename:=TempFile, _
       Sheet:=TempWB.Sheets(1).Name, _
       Source:=TempWB.Sheets(1).UsedRange.Address, _
       HtmlType:=xlHtmlStatic)
      .Publish (True)
  End With
  
  'Read all data from the htm file into RangetoHTML
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
  RangetoHTML = ts.readall
  ts.Close
  RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                        "align=left x:publishsource=")
  
  'Close TempWB
  TempWB.Close savechanges:=False
  
  'Delete the htm file used in this function
  Kill TempFile
  
  Set ts = Nothing
  Set fso = Nothing
  Set TempWB = Nothing
  End Function
  </code></pre>