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 ? :hi:

(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 :banghead:

[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