Edit an open email from excel

Question

Edit: Thank you everyone. All the suggestions probably work. I just finally realized, there's a security option enabled by my employer to prevent this type of action. Unfortunately, I can't edit emails, only create them.

I'm trying to figure out how to modify an open email from excel.

I have an email template that excel will replace certain strings with values from a worksheet in my workbook.

I've been researching activeinspector, but I'm still unsure how cross programming works between excel and outlook. Searching various sites gets me this, but it's not working.

Error 287 on str = outlookMail.Body

' Create the outlook object
Dim outlookApp As Outlook.Application
Set outlookApp = New Outlook.Application

Dim outlookInspector As Outlook.Inspector
Dim outlookMail As Outlook.MailItem

Set outlookInspector = outlookApp.ActiveInspector

Dim str As String

If Not outlookInspector Is Nothing Then
    If TypeOf outlookInspector.CurrentItem Is Outlook.MailItem Then
        Set outlookMail = outlookInspector.CurrentItem
        str = outlookMail.Body

        ' Replacements
        str = Replace(str, "DIPOC", "string1")
        str = Replace(str, "REFNUM", "string2")
        str = Replace(str, "RCVDATE", "string3")
        str = Replace(str, "EMPNAME", "string4")
        str = Replace(str, "EMPEIDN", "string5")
        str = Replace(str, "ACTIONREQ", "string6")

        outlookMail.Body = str
    End If
End If

I'm still new to this so any help will be appreciated. Thank you in advance.

Answer 1

You are initializing the olOutMail variable before you initialize the oInspector variable. Switch them around:

Set oInspector = Outlook.ActiveInspector
Set olOutMail = oInspector.CurrentItem

Answer 2

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

Dim oInspector As Inspector
Dim olOutMail As MailItem

If your code compiles, then you have a reference to the Outlook object model, and the Outlook identifier refers to the Outlook type library: Dim Outlook As Object is shadowing that declaration. Rename it, and explicitly qualify the types with the library they're from:

Dim outlookApp As Outlook.Application
Set outlookApp = New Outlook.Application

Dim outlookInspector As Outlook.Inspector
Dim outlookMail As Outlook.MailItem

I couldn't figure out a consistent scheme for your prefixing, so I dropped it.

Now when you assign the inspector reference:

Set oInspector = Outlook.ActiveInspector

That's ambiguous, at least to a human reader: if you get IntelliSense when you type that . dot, then VBA is understanding Outlook as Outlook.Application, and this means trouble, for now you have an implicit Application reference that is not the instance you mean to work with... and that could very well why you're getting this error.

You want to disambiguate that.

Set outlookInspector = outlookApp.ActiveInspector

That should give you the Inspector reference you mean to work with.

Next problem, is that you set the olOutMail object reference assuming there's an active inspector, and that you're looking at a MailItem - expect fire if either assumption isn't true.

If Not outlookInspector Is Nothing Then
    If TypeOf outlookInspector.CurrentItem Is Outlook.MailItem Then
        'NOW we KNOW we're looking at a mailitem.
        Set outlookMail = outlookInspector.CurrentItem
        '...work with outlookMail here...
    End If
End If

Answer 3

This will fail if there isn't an active e-mail open (there's no error handling code here), but just for testing purposes it was functioning for me. I am assuming dipoc, RefNum, rcvdate are variables you have defined elsewhere (I tested it with static strings). Also the name of the Outlook object is "Outlook", so it would be best to avoid naming an object "Outlook". If you have HTML formatting in an e-mail (like in a signature), you want to use .HTMLBody instead of .Body

Sub test()
    ' Create the outlook object
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")

    Dim olInspector As Object
    Dim olOutMail As Object
    Set olInspector = olApp.ActiveInspector
    Set olOutMail = olInspector.CurrentItem

    Dim str As String
    str = olOutMail.Body

    ' Replacements
    str = Replace(str, "DIPOC", dipoc)
    str = Replace(str, "REFNUM", RefNum)
    str = Replace(str, "RCVDATE", rcvdate)

    olOutMail.Body = str

    ' Remove the outlook objects
    Set olInspector = Nothing
    Set olOutMail = Nothing
    Set olApp = Nothing
End Sub

Answer 4

it runs well from my end. It changes my email to plain text format.

Here is my test environment: Office 2016 + windows 10 and below are the code I’ve tested.

Sub strat()
' Create the outlook object
Dim outlookApp As Outlook.Application
Set outlookApp = New Outlook.Application
Dim outlookInspector As Outlook.Inspector
Dim outlookMail As Outlook.MailItem
Set outlookInspector = outlookApp.ActiveInspector
Dim str As String
If Not outlookInspector Is Nothing Then
    If TypeOf outlookInspector.CurrentItem Is Outlook.MailItem Then
        Set outlookMail = outlookInspector.CurrentItem
        str = outlookMail.Body

        ' Replacements
        str = Replace(str, "For", "string1")
        str = Replace(str, "REFNUM", "string2")
        str = Replace(str, "RCVDATE", "string3")
        str = Replace(str, "EMPNAME", "string4")
        str = Replace(str, "EMPEIDN", "string5")
        str = Replace(str, "ACTIONREQ", "string6")
        outlookMail.Body = str
        MsgBox str
    End If
End If
End Sub

Best Regards,

Evan