View Single Post
 
Old 08-18-2024, 09:23 AM
ksor ksor is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Feb 2018
Location: Århus V, Denmark
Posts: 78
ksor is on a distinguished road
Angry Strange (for me at least) errors in Excel VBA

I have cooked up this VBA code to send a part of the sheet as a outlook mail:


Code:
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 nicely BUT only as a .DISPLAY of the mail - I had to 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 have ANOTHER error in the line:


Set WordEditor = OutlookMail.GetInspector.WordEditor


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


What is wrong here ?
Reply With Quote