Pasting formatted Excel range into Outlook message

Question

I would like to paste a range of formatted Excel cells into an Outlook message.

The following code (that I lifted from various sources), runs without error and sends an empty message.

Sub SendMessage(SubjectText As String, Importance As OlImportance)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim iAddr As Integer, Col As Integer, SendLink As Boolean
'Dim Doc As Word.Document, wdRn As Word.Range
Dim Doc As Object, wdRn As Object

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Set Doc = objOutlookMsg.GetInspector.WordEditor
'Set Doc = objOutlookMsg.ActiveInspector.WordEditor
Set wdRn = Doc.Range
wdRn.Paste

Set objOutlookRecip = objOutlookMsg.Recipients.Add("MyAddress@MyDomain.com")
objOutlookRecip.Type = 1
objOutlookMsg.Subject = SubjectText
objOutlookMsg.Importance = Importance

With objOutlookMsg
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
        ' Set the Subject, Body, and Importance of the message.
        '.Subject = "Coverage Requests"
        'objDrafts.GetFromClipboard
    Next
    .Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Steph, please don't crosspost; this question will be migrated automatically. If you register an account on Stack Overflow, and associate your accounts in your user profile, you'll maintain ownership of the question after it migrates. — quack quixote, Mar 24 '10 at 21:09
Are you sure that objOutlookMsg.BodyFormat is initialized to "Outlook.OlBodyFormat.olFormatRichText" or to "Outlook.OlBodyFormat.olFormatHTML"? In other formats, the paste would not work. — Axel Kemper, Jun 27 '13 at 09:58
Related: http://stackoverflow.com/questions/29092999/pasting-an-excel-range-into-an-email-as-a-picture — Jean-François Corbett, Mar 17 '15 at 08:10

score 1 · Answer 1 · edited Aug 04 '19 at 18:31

1

Put .Display before .Send,

Simple but Quick fix, your problem is the email is not refreshing with the pasted contents before it sends, forcing it to Display first gives it time...

Also make sure you have another macro which runs before this to Copy the Range into your clipboard...

edited Aug 04 '19 at 18:31

June7

19,874
8
24
34

answered Jun 27 '13 at 08:52

Shakey

11
1

score 1 · Answer 2 · answered Mar 25 '10 at 00:56

1

I think you need to call .Save on your Mail Item (objOutlookMsg) after you've made all the changes.

answered Mar 25 '10 at 00:56

JP Alioto

44,864
6
88
112

score 0 · Answer 3 · answered Mar 25 '10 at 01:09

There is a button in excel to do this, "Send to mail recipent" its not normally on the ribbon.

You can also use the simple mapi built into office using the MailEnvelope in VBA

.. a good article on what you are trying to do http://www.rondebruin.nl/mail/folder3/mail4.htm

Pasting formatted Excel range into Outlook message

3 Answers3

Linked