Hi everyone,

I am using Office 2007 and I would like to use VBA to paste a range of formatted Excel cells into an Outlook message and then mail the message. In the following code (that I lifted from various sources), it runs without error and then sends an empty message... the paste does not work.

Can anyone see the problem and better yet, help with a solution?

Thanks, -Steph

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("[email protected]")
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