Pasting Clipboard's contents in an outlook mail message

[inderdaad]

I daily use a macro that puts excel data on the clipboard and also generates an outlook page (the mail adres is available in this outlook document)
If I manually paste the clipboard data into the outlook, it works fine.
I am looking for a VBA code to perform this action, it would make the macro complete
VBA does not need to generate an outlook document in this case

Thanks in advance

 

[AOB]

I'm guessing you want the Excel content to appear in the body of the email, as opposed to simply attaching the workbook as an attachment?

In that scenario, I like to publish the Excel range as HTML and then add that to the HTMLBody of the Outlook MailItem via TextStream

It's very quick, maintains (most) formatting and is fairly simple to implement

To publish the range as HTML and then read it back to a string variable, this should get you started :

Public Function ExcelRangeToHTMLString(rng As Range) As String
    
    Const strTempFileName As String = "MyTempHTML.htm"

    Dim objFSO As Object
    Dim objFSOTextStream As Object
    Dim strTempFilePath As String
    Dim strHTML As String
        
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    strTempFilePath = objFSO.GetSpecialFolder(2)
    strTempFilePath = strTempFilePath & "\" & strTempFileName

    With rng
        .Parent.Parent.PublishObjects.Add(4, strTempFilePath, .Parent.Name, .Address, 0, "", "").Publish True
    End With
            
    Set objFSOTextStream = objFSO.OpenTextFile(strTempFilePath, 1)
    strHTML = objFSOTextStream.ReadAll
    
    Set objFSOTextStream = Nothing
    Set objFSO = Nothing
    
    ExcelRangeToHTMLString = strHTML
    
End Function

Now you can define a range in Excel, pass the Range object to that function and it will return the string representing it in HTML

Add that to the .HTMLBody of the Outlook MailItem object and you should be good to go?

Once you're comfortable with that, you can modify the function to take various inputs (ranges, strings, hyperlinks etc.) and use it to build your email body piece by piece, then simply append it to the email when it's constructed.