Announcement

Collapse
No announcement yet.

Convert early binding to late binding?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert early binding to late binding?



    Hi everyone! We are having an issue with a spreadsheet that our company uses on a daily basis. It was created in Office 2013 and the problem we are now facing is that if someone with 2016 opens it, then saves it, the 2013 users are no longer able to use it. The code opens Outlook and attaches an image of a range of cells to the bottom. When the error is received it is due to the references, specifically the Microsoft Word Object Library. I will paste the code below, keep in mind I am not a VBA expert by any means, so I am reaching out for help. Thanks!

    Private Sub CommandButton1_Click()

    'Copy range of interest
    Dim r As Range
    Set r = Range("A1:Q86")
    r.Copy

    'Open a new mail item
    Dim OutApp As Object
    Dim outMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set outMail = OutApp.CreateItem(0)

    'Get its Word editor
    outMail.Display
    Dim wordapp As Object
    Dim wordDoc As Object
    Set wordapp = CreateObject("Word.Application")
    Set wordDoc = outMail.GetInspector.WordEditor

    'Define StrBody
    Dim StrBody As String

    'Define User Signature
    Dim signature As String
    signature = outMail.body

    StrBody = _
    "<html><font face=Calibri><font size=4><p>Good evening,</p>" & _
    "<br>" & _
    "<br>" & _
    "<html><font face=Calibri><font size=4><p>[Insert your analysis of the production day here.]</p>" & _
    "<br>" & _
    "" & "<br>"

    With outMail

    .To = Worksheets("MasterDefinitions").Cells(63, "C").Value
    .CC = Worksheets("MasterDefinitions").Cells(64, "C").Value & "; " & Worksheets("MasterDefinitions").Cells(66, "C").Value & "; " & Worksheets("MasterDefinitions").Cells(67, "C").Value
    .Attachments.Add ActiveWorkbook.FullName
    .Subject = Worksheets("MasterDefinitions").Cells(65, "C").Value
    .HTMLBody = StrBody & "<br>" & .HTMLBody & "<br>" & "<br>" & "<br>" & "<br>" & "<br>"

    With outMail

    'To paste as picture
    wordDoc.Range(Start:=wordDoc.Range.End - 2).PasteAndFormat wdChartPicture
    .Display

    End With
    On Error GoTo 0

    Set outMail = Nothing
    Set OutApp = Nothing

    End With
    End Sub

    I feel like I need to remove the references but I don't know how.
    Attached Files

  • #2
    Right click that sheet's tab, View Code, rename the previous sub or delete it, paste:
    Code:
    Private Sub CommandButton1_Click()
      Dim OutApp As Object, outMail As Object
      Dim r As Range, StrBody As String, signature As String
      'Dim wordDoc As Word.Document
      Dim wordDoc As Object
        
      'Copy range of interest
      Set r = Range("A1:Q86")
      r.Copy
      
      'Open a new mail item
      Set OutApp = CreateObject("Outlook.Application")
      Set outMail = OutApp.CreateItem(0)
      
      'Get its Word editor
      outMail.Display
      Set wordDoc = outMail.GetInspector.WordEditor
      
      'Define User Signature
      signature = outMail.body
      
      StrBody = _
          "<html><font face=Calibri><font size=4><p>Good evening,</p>" & _
          "<br>" & _
          "<br>" & _
          "<html><font face=Calibri><font size=4><p>[Insert your analysis of the production day here.]</p>" & _
          "<br>" & _
          "" & "<br>"
      
      With outMail
        .To = Worksheets("MasterDefinitions").Cells(63, "C").Value
        .CC = Worksheets("MasterDefinitions").Cells(64, "C").Value & "; " & Worksheets("MasterDefinitions").Cells(66, "C").Value & "; " & Worksheets("MasterDefinitions").Cells(67, "C").Value
        .Attachments.Add ActiveWorkbook.FullName
        .Subject = Worksheets("MasterDefinitions").Cells(65, "C").Value
        .HTMLBody = StrBody & "<br>" & .HTMLBody & "<br>" & "<br>" & "<br>" & "<br>" & "<br>"
        With outMail
          'To paste as picture
          wordDoc.Range(Start:=wordDoc.Range.End - 2).PasteAndFormat wdChartPicture
          .Display
        End With
      End With
      
      On Error GoTo 0
      
      Set outMail = Nothing
      Set OutApp = Nothing
    End Sub
    In VBE, select menu items, Tools > References > uncheck Microsoft Word object.

    Comment


    • #3
      Thanks! That fixed the error. The only thing now is when the cells are pasted as an image at the bottom of the email now the formatting is off. If that's not something that can be fixed then that is fine, they can live with it like that.

      Comment


      • #4
        I am not sure what you mean by formatting is off. Formatting of the cells and the image or body of the email?

        What if you set the reference and then ran the old code? I bet formatting would be off then too?

        I am not sure why you used html for the body. I normally use the word object for such when I use the copy/paste word method.

        Comment


        • #5
          I have attached screenshots of the differences.
          Thanks.I'll have an other look. I didn't write the code, I just got thrown into it, you know how that goes

          If I ran the old code like u said the format of the image changes too.
          It's like it no longer inserts the range of cells as an image.
          Attached Files

          Comment


          • #6


            I can not reproduce your problem. Both codes produced the same result.

            Comment

            Working...
            X