Paste cell contents in Outlook email maintaining formatting

Question

I created a procedure to create email drafts in Outlook. Users can modify the email body by writing it on a range, and it's added to the email maintaining all formatting options.

My problem is that while my procedure worked at first, when I started using the word editor Microsoft Outlook starting crashing with the message "Microsoft Outlook has stopped running" and when I kill outlook I get message "The remote procedure failed" on VBA

Why might this be happenning? is there a way to open Outlook before running the code to avoid the error?

Public Sub CreateDraft(Destinatary As String, CC As String, Subject As String, Body As Range, Optional AttachmentPath As String = "")
    Dim OutApp  As Object
    Dim OutMail As Object
    Dim WordDoc As Word.Document
    Dim WordRange As Word.Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .Display
        .To = Destinatary
        .CC = CC
        .Subject = Subject
        Set WordDoc = OutApp.ActiveInspector.WordEditor
        Set WordRange = WordDoc.Goto(What:=wdGoToSection, Which:=wdGoToFirst) ' The code crashes here
        Body.Copy
        WordRange.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
        .HTMLBody = .HTMLBody
        If (AttachmentPath <> "") Then
            .Attachments.Add (AttachmentPath)
        End If
        .Save
        .Close (False)
    End With
    Application.CutCopyMode = False
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Microsoft Outlook crashes with the message "Microsoft Outlook has stopped running". When I kill outlook I get "The remote procedure failed" on VBA — lisovaccaro, Nov 12 '15 at 11:30

score 0 · Answer 1 · answered Nov 11 '15 at 19:05

The issue is not that Outlook isn't running, it's that when you have programmatically opened Outlook it's in a different state to when a user has opened the application. Things such as the Word Editor are not going to work properly. You can verify this by stepping through your code, and you should see that after creating the Outlook.Application object that the Outlook icon is in the system tray and its tooltip will explain that another program is controlling Outlook.

My suggestion would be to identify when Outlook is in this state, and then notify your user that they need to make sure Outlook is running prior to executing your code. One way to determine if it has been opened by the user is to look at the Explorers object. There will always be at least 1 when the user has opened Outlook, and if there isn't you could prompt the user to do something about it before trying again.

Dim oApp As Outlook.Application

'   Open / Connect with Outlook
Set oApp = CreateObject("Outlook.Application")

'   Check Outlook has been opened by a user
While oApp.Explorers.Count = 0

    If Not MsgBox("Please open Outlook and click Retry to try again.", vbExclamation + vbRetryCancel, "Outlook Not Open") = vbRetry Then

        Exit Sub
    End If

Wend

' Outlook has been opened by the user
' Continue.....

score 0 · Answer 2 · answered Nov 12 '15 at 12:01

This is the only thing that worked. I defined OutApp and OutMail as Outlook objects and then got the inspector from the OutMail object. Using the .GetInspector method.

Public Sub CreateDraft(Destinatary As String, CC As String, Subject As String, Body As Range, Field1 As String, Field2 As String, Field3 As String, Optional AttachmentPath As String = "")
    Dim OutApp As New Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim OutInspector As Outlook.Inspector
    Dim WordDoc As Word.Document
    Dim WordRange As Word.Range
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = Destinatary
        .CC = CC
        .Subject = Subject
        If AttachmentPath <> "" Then .Attachments.Add (AttachmentPath)
        .Display
        Set OutInspector = OutMail.GetInspector
        Set WordDoc = OutInspector.WordEditor
        If Not (WordDoc Is Nothing) Then
            Set WordRange = WordDoc.Range(0, 0)
            Call Body.Copy
            Call WordRange.PasteExcelTable(LinkedToExcel:=False, WordFormatting:=False, RTF:=False)
            Application.CutCopyMode = False
        End If
        Call .Save
        Call .Close(False)
    End With
End Sub

Paste cell contents in Outlook email maintaining formatting

2 Answers2