Strange behavior and errors I think ?????

[ksor]

I run Windows 10 FULLY updated and MSOffice 365 HOME - just updated as you can see in the text below.

I have cooked up this VBA code to send a part of the sheet as a outlook mail ... and it kind of works:

Sub SendNamedRangeAsPictureInBody()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim WordEditor As Object
    Dim ws As Worksheet
    Dim strRange As String
    Dim rng As Range
    Dim InlineShape As Object

    ' Define your sheet and named range here
    Set ws = ThisWorkbook.Sheets("Bla Bla")
    strRange = "ToBookKeeping"
    
    ' Get the range
    Set rng = ws.Range(strRange)

    ' Copy the range as a picture
    rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    ' Create the Outlook application and email
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)

    ' Construct the email
    With OutlookMail
        .To = "BookKeepersMailAddress"
        .Subject = "Hjemmeladning for den angivne periode !"
        

        ' Get the Word editor for the email and paste the image
        Set WordEditor = OutlookMail.GetInspector.WordEditor
        WordEditor.Content.Paste
        
        ' Access the last pasted inline shape (the picture) and resize it
        Set InlineShape = WordEditor.InlineShapes(WordEditor.InlineShapes.Count)
        InlineShape.LockAspectRatio = msoFalse
        InlineShape.Width = InlineShape.Width * 2 ' Double the width
        InlineShape.Height = InlineShape.Height * 2 ' Double the height
            
        WordEditor.Content.InsertAfter vbCrLf & vbCrLf '& vbCrLf
    
        ' Add additional text or formatting here if needed
        WordEditor.Content.InsertAfter "Fortsat god dag !" & vbCrLf & "TEAM SUPPORT"
    
        
        .Display    ' Display the email before sending
        '.Send       '  At sende giver FEJL !!!!!!!!!!!!!!!

    End With

    ' Clean up
    Set ws = Nothing
    Set rng = Nothing
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    Set WordEditor = Nothing
    Set InlineShape = Nothing
   
End Sub

In my FIRST try it worked really nicely BUT ONLY as a .DISPLAY of the mail !!!!
I had to manually click on the SEND button to send it !


If I used the .SEND command instead of .Display I got this error:

"Invalid procedure call or argument"

in the .Send line !


OK, I then tried to search out there and found others with the same problem and they were adviced to update MSOffice (and thereby Outlook) and so I did ...
and now I just have DIFFERENT error in the line:

Set WordEditor = OutlookMail.GetInspector.WordEditor


the error is: Run-time error '-2147467259 (80004005)'


What the *BEEP* is wrong here - try the attached Excel file ... but put you OWN mail address in it ?

(PS: I know I asked in another forum ... but no one answers there . then you have to ask some other people !)

[Aflatoon]

I know I asked in another forum ... but no one answers there . then you have to ask some other people

and common courtesy (and forum rules) means that you should give us links to wherever else you posted this.

[ksor]

I tried again this morning and ...


The "DISPLAY-version" works again WITHOUT the "new after-update error" !!!


Still the same error in the "SEND-version" though

[georgiboy]

Still need a link to your other forum post...

As already stated by Aflatoon: It's common courtesy (and forum rules), I would imagine that it is the rules on the other forum also.

[Aussiebear]

Don't know if this issue has been resolved for the OP but heres a possible solution for others viewing this thread.

The .Display method in Outlook VBA makes the email visible to the user in a new window. The key point is that until the email is displayed (or at least a proper mail item object is fully created and referenced), the .Send method cannot be reliably called. When the user comments out .Display, the code might be trying to call .Send on an object that hasn't been fully initialized or properly referenced as a MailItem object.

Sub CreateAndSendEmail()
    Dim olApp As Object ' Or Outlook.Application
    Dim olMail As Object ' Or Outlook.MailItem
    ' Attempting to use an existing Outlook application, if running
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    ' If Outlook isn't running, create a new instance
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    ' Create a new mail item
    Set olMail = olApp.CreateItem(0) 
    ' 0 represents olMailItem
    ' Set email properties
    With olMail
        .To = "recipient@example.com"
        .CC = "cc@example.com"
        .Subject = "Test Email"
        .Body = "This is the body of the email."
        .Send ' Directly sends the email without displaying
    End With
    ' Clean up objects
    Set olMail = Nothing
    Set olApp = Nothing
End Sub