none
Error while copying data from excel to outlook using WordEditor RRS feed

  • Question

  • Hello,

    I am trying to copy some data range from Excel to outlook using WordEditor. I can do it using HTML but have some spacing/formatting issues which does not seems to get fixed by HTML.

    But, I am getting an error saying the method or property is unavailable because the document is locked for editing pointing to this line of code :   pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText). I have tried document.unprotect and then I get an error that the document is already unprotected. I am fairly new to WordEditor and have got this code from various sources. Any idea what I am missing here?

    Here is the complete code:

    Sub esendtable()

    Dim xInspect As Object

    Dim mail As Object
    Dim newEmail As Object
    Dim pageEditor As Object
    Dim rng1 As Range
    Dim rng2 As Range
    Dim TrgRow As Long
    Dim st As Worksheet
    Dim LastRow As Long

    Set st = ActiveSheet
    Set rng1 = Nothing
    Set rng2 = Nothing

    'Select the first blank row 

    TrgRow = FirstBlankRow(st.Range("A:I"))

    LastRow = st.Cells(st.Rows.Count, 1).End(xlUp).Row
    Set rng1 = st.Range("A1", st.Cells(TrgRow - 1, 9)).SpecialCells(xlCellTypeVisible)
    'Set rng2 = st.Range(st.Cells(TrgRow + 1, 1), st.Cells(LastRow, 9)).SpecialCells(xlCellTypeVisible)

    Set mail = CreateObject("Outlook.Application")
    Set newEmail = mail.CreateItem(0)

    With newEmail
        .To = "abc@google.com"
        .CC = ""
        .BCC = ""
        .Subject = "Data"
        .Body = "Please find the requested information" & vbCrLf & "Best Regards"

        Set xInspect = newEmail.GetInspector
    '    On Error Resume Next
        Set pageEditor = xInspect.WordEditor

        rng1.Copy
    Dim odProt As Integer
        odProt = pageEditor.ProtectionType
            Debug.Print "Document is protected with type " & odProt & ", unprotecting temporarily"
    '        pageEditor.Unprotect
    '        pageEditor.RemoveLockedStyles

        pageEditor.Application.Selection.Start = Len(.Body)
        pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
        pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)

        .Body = "Please find the requested information" & vbCrLf & "Range 2"

        rng1.Copy

        pageEditor.Application.Selection.Start = Len(.Body)
        pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
        pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
        .Display
        '.Send
        Set pageEditor = Nothing
        Set xInspect = Nothing
    End With

    Set newEmail = Nothing
    Set outlook = Nothing

    End Sub

    Wednesday, August 22, 2018 4:50 AM