excelvbaoutlookms-word

Apply Outlook VBA from Excel - Runtime error 5: Invalid procedure call or argument


I am trying to copy all of content of a Word document into an Outlook email body while keeping the format.

Following the solution found on this page I get an error on the following line:

.BodyFormat = olFormatRichText

When the error handler is removed, I get

Runtime Error 5:
Invalid procedure call or argument

Sub Sender(Target As Range)

    Dim OutApp As Object
    Dim OutMail As Object
    Dim wd As Object
    Dim editor As Object
    Dim doc As Object
    
    Dim fp As String
    fp = "C:\Users\urdearboy\"
    
    Set wd = CreateObject("Word.Application")
    Set doc = wd.documents.Open(fp & "mydearfile.docx")
    doc.Content.Copy
    doc.Close
    Set wd = Nothing
    
    On Error GoTo BNP:
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .SentOnBehalfOfName = "urdearboy@so.com"
        .to = Target.Offset(, 2)
        .Subject = "Hi Mom"
        .BodyFormat = olFormatRichText                 '<----- ERROR LINE
        Set editor = .GetInspector.WordEditor
        editor.Content.Paste
                
        .Display
        '.Send
                
        Target.Offset(, -1) = "Sent"
    End With
            
BNP:
    Set OutApp = Nothing
    Set OutMail = Nothing
    
End Sub

Context: I decided to go with the Word to Outlook copy because the file has a lot of formatting and photos and getting the right format strictly in Outlook HTML sounds like a nightmare. If done manually, this would essentially be a complete CTRL + A + Copy from word and CTRL + V in Outlook which keeps all formatting, photos, and gifs with correct format. The goal here is to mimic that process in VBA. If there is a better solution, open to thoughts there as well.


Solution

  • If you're late-binding, then add:

    Const olFormatRichText As Long = 3
    

    (seems like you didn't have Option Explicit on too...)

    You can find the appropriate value of olFormatRichText here.