Trying to copy a range from a worksheet to an outlook (2013) email msg....

[blackbird4823]

I am using this code:

Sub CopyAndPasteToMailBody()
With Range("E4")
.Value = Time
.NumberFormat = "hh:mm"
End With
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
    ActiveSheet.Range("A1:I122").Copy
    wEditor.Application.Selection.Paste
    With mail
        .Subject = "Columbus (5D) End-of-Day"
        .To = "[EMAIL="usarmy.knox.usarec.list.columbus-bn-stns@mail.mil"]mail recipient[/EMAIL]"
        .cc = "[EMAIL="usarmy.knox.usarec.list.5dbn-s3@mail.mil"]mail [/EMAIL]recipient"
        '.Send  'Remove ' to send without viewing
    End With
End Sub

I always get an error msg on this line:
Set wEditor = mailApp.ActiveInspector.wordEditor
runtime error 287:
application-defined or object-defind error.

I have(tools/references) loaded:
MS outlook 15.0 object Library
MS Word 15.0 object Library
MS Office 15.0 object Library
MS DAO 3.6 Object Library

ANY ideas greatly appreciated!!!

 

[RickXL]

Hi,

I have never done this before but it looks as if you need another line or so in there. This seems to work:

Sub CopyAndPasteToMailBody()
    Dim mailApp As Outlook.Application
    Dim mail As Outlook.MailItem
    Dim wEditor As Word.document
    
    With Range("E4")
        .Value = Time
        .NumberFormat = "hh:mm"
    End With
    
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.WordEditor
    ActiveSheet.Range("A1:I122").Copy

    wEditor.Range(0, 0).PasteExcelTable False, True, False
    With mail
        .Subject = "Columbus (5D) End-of-Day"
        .To = "mail recipient"
        .cc = "mail recipient"
        '.Send  'Remove ' to send without viewing
    End With
End Sub

It applies Word Editing so you get the boxes and it also keeps the signature. Looking at the documentation there are lots of other options!

Regards,

 

[blackbird4823]

I truly appreciate the analysis of my code.
I have implemented all of your changes.

The problem is I still get the error msg on this line:
Set wEditor = mailApp.ActiveInspector.wordEditor
runtime error 287:
application-defined or object-defind error.

I suspect it is because I do not have a specific reference loaded.
I have(tools/references) loaded:
MS outlook 15.0 object Library
MS Word 15.0 object Library
MS Office 15.0 object Library
MS DAO 3.6 Object Library

 

[RickXL]

Hi,

As it works for me but not for you then it must be a difference between our two set ups.

I suppose you do actually have Word available?

According to this link you need to have two things enabled: IsWordMail and EditorType=olEditorWord.
https://msdn.microsoft.com/en-us/library/office/ff868196.aspx

I have added a MsgBox to this code so that you can find out. Both entries should be flagged as True.

Sub CopyAndPasteToMailBody()

    Dim mailApp As Outlook.Application
    Dim mail As Outlook.MailItem
    Dim wEditor As Word.document
    
    With Range("E4")
        .Value = Time
        .NumberFormat = "hh:mm"
    End With
    
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    
    MsgBox "IsWordMail = " & ActiveInspector.IsWordMail & vbLf & "EditorType = " & (ActiveInspector.EditorType = olEditorWord)
    
    mail.Display
    
    Set wEditor = mailApp.ActiveInspector.WordEditor
    ActiveSheet.Range("A1:I2").Copy 'was I122

    wEditor.Range(0, 0).PasteExcelTable False, True, False
    With mail
        .Subject = "Columbus (5D) End-of-Day"
        .To = "mail recipient"
        .cc = "mail recipient"
        '.Send  'Remove ' to send without viewing
    End With
    
End Sub

 

[blackbird4823]

I get err msg's on the msgbox line!!
Is it possibly because my email format is set to TEXT (and cannot be changed to HTML)???

 

[RickXL]

Was Outlook open when you ran the macro?

Is your version of Ootlook and Word 2013?

Try this, it seems to work if Outlook is not open first:

Sub CopyAndPasteToMailBody()

    Dim mailApp As Outlook.Application
    Dim mail As Outlook.MailItem
    Dim wEditor As Word.document
    
    With Range("E4")
        .Value = Time
        .NumberFormat = "hh:mm"
    End With
    
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    
    mail.Display
    
    MsgBox "IsWordMail = " & ActiveInspector.IsWordMail & vbLf & "EditorType = " & (ActiveInspector.EditorType = olEditorWord)
    
    Set wEditor = mailApp.ActiveInspector.WordEditor
    ActiveSheet.Range("A1:I2").Copy 'was I122

    wEditor.Range(0, 0).PasteExcelTable False, True, False
    With mail
        .Subject = "Columbus (5D) End-of-Day"
        .To = "mail recipient"
        .cc = "mail recipient"
        '.Send  'Remove ' to send without viewing
    End With
    
End Sub

 

[RickXL]

Hi,

I finally found my Plain Text/HTML setting and you are right. It does not work when set to Plain Text. HTML and RTF both seem to work though.