VBA Excel and Outlook not communicating

Question

I try to call an email from a group mailbox and reply to it. All works perfectly and as expected except for the reply aspect. I can either make a reply body (which will be the only thing in the email) and send that or open an empty reply and keep the email history (the previous messages).

I have narrowed the problem to the fact excel and outlook aren't communicating as they should. I have the correct ticket references in both applications and ensured this is the problem by running some dummy code below that also fails.

Sub Find_Email()

Dim objOutlook As Object: Set objOutlook = CreateObject("Outlook.Application")
Dim olMail As Outlook.MailItem
Dim olReply As Outlook.MailItem
Dim olNS As Namespace: Set olNS = GetNamespace("MAPI")
Dim olMailbox As Folder: Set olMailbox = olNS.Folders("Group Inbox")
Dim olFolder As Folder: Set olFolder = olMailbox.Folders("Group Folder")
Dim subFolder As Folder: Set subFolder = olFolder.Folders("Group Subfolder")
Dim olItems As Outlook.Items: Set olItems = subFolder.Items

TheDate = Format(Date, "DD-MM-YYYY")
TheDate1 = Format(Date, "YYYY-MM")
TheDate2 = Format(Date, "YYYYMMDD")
TheDate3 = Format(Date, "YYYY")

'Find most recent email and populate

olItems.Sort "ReceivedTime", True
For i = 1 To 20 'used to be olItems.Count
    Set olMail = olItems(i)
    If InStr(olMail.Subject, "Subject " & TheDate) > 0 Then
        Set olReply = olMail.ReplyAll
        With olReply
            '.To = "Recipients"
            '.CC = ""
            '.Subject = "anything" & TheDate
            .HTMLBody = EmailBody & .HTMLBody
            If Attach_File = True Then: olReply.Attachments.Add "Location"
            .Display
            Exit Sub
        End With
    End If
Next i
End Sub

' Fails at .HTMLBody as application-defined error or object-defined error

Dummy Code: Outlook:

Function testoutlook()

Call MsgBox("hello")

End Function

Excel:

Sub testexcel()

Dim o As Outlook.Application

Set o = GetObject("", "Outlook.application")

o.Application.testoutlook

End Sub

'This fails at the function call, Object doesn't support this property or method

Consider deleting the dummy code. [Call Outlook Macro From Excel. Object Does not support property or method](https://stackoverflow.com/questions/51893262/vba-call-outlook-macro-from-excel-object-does-not-support-property-or-method) — niton, Mar 06 '23 at 12:45
Similar to [Some MailItem Properties return values, others do not](https://stackoverflow.com/questions/17065844/outlook-vba-some-mailitem-properties-return-values-others-do-not). — niton, Mar 06 '23 at 12:49
What line of code exactly gives the error message? Have you tried running the code under the debugger attached? — Eugene Astafiev, Mar 06 '23 at 15:31
If I put say .HTMLBody = mailbody, that works fine and if I remove the line it also works and displays the email history (as if hitting the replyall button) but trying to call .HTMLBody as existing causes the error. e.g. if i input a debug.print(.HTMLBody) it produces the same error — Max_Bruce, Mar 06 '23 at 16:55
I do believe i put the relevant error message at the end of each code portion — Max_Bruce, Mar 06 '23 at 16:56
Sounds like a security issue when automating Outlook from external applications. — Eugene Astafiev, Mar 06 '23 at 17:01
Check out my answer for that - I've added the article which explains a possible security issue. — Eugene Astafiev, Mar 06 '23 at 17:53

Eugene Astafiev · Answer 1 · 2023-03-06T17:51:48.527

First, Outlook is a singleton which means you can't run two instances of Outlook at the same time (two outlook.exe processes). So, the following code:

Dim objOutlook As Object: Set objOutlook = CreateObject("Outlook.Application")

Retrieves the running Outlook instance if it has already been run.

But that is possible only if two applications (Excel and Outlook) are under the same security context. So, if Outlook is run with admin privileges (Run As Administrator) and Excel was run under a regular user account without admin privileges - these two applications will never find each other (Excel will not be able to run the second Outlook instance, nor connect to the already running under a different security context). You need to make sure that Outlook is not running or run Excel under the same security context with the Outlook process.

Second, I've noticed the code where you are iterating over all items in the folder and check the Subject property in the loop:

For i = 1 To 20 'used to be olItems.Count
    Set olMail = olItems(i)
    If InStr(olMail.Subject, "Subject " & TheDate) > 0 Then

Instead, you need to use the Find/FindNext or Restrict methods of the Items class. These methods allow getting items that correspond to the search criteria and iterate over them only. Read more about these methods in the following articles that I wrote for the technical blog:

Third, make sure that you deal with a well-formed HTML makrup when dealing with the message body:

.HTMLBody = EmailBody & .HTMLBody

Even if Outlook handles most markup issues correctly, it is better to make sure you don't get anything unpredictable when adding something before the opening <html> tag. Instead, find the opening <body> tag and insert your content right after it.

Fourth, you may face with a security issue when automating Outlook from external applications. Read more about that in the Outlook Object Model Security Warnings article.

Hiya, This is all very helpful, especially the Find method. I will definitely be putting this in as soon as this issue is solved. I have tried using .Body instead of .HTMLBody and the same result occurs. I have even put in a solution to the formatting outlined above in another comment where each case is dealt with but the same issue occurs. If the issue is that the securities are different, would it still be able to find the email and open the email from outlook. If it could do this but not then take the .Body/.HTMLBody from it then I believe we have found the route issue. — Max_Bruce, Mar 06 '23 at 15:05
The issue is not related to the `HTMLBody` property modification. Just make sure that both applications are run under the same security context so Excel could retrieve an Outlook `Application` instance in the code. — Eugene Astafiev, Mar 06 '23 at 15:26
Just make sure that both applications are closed (no processes found in the Task manager). — Eugene Astafiev, Mar 06 '23 at 16:55
Right :) Make sure there is no `outlook.exe` in the list of running processes. — Eugene Astafiev, Mar 06 '23 at 17:00
I've tried that. Is it possible that the outlook settings have securities that may be blocking it — Max_Bruce, Mar 06 '23 at 17:25

VBA Excel and Outlook not communicating

1 Answers1