copying excel range to outlook results in String value True instead of a expected results-CodePudding

Trying to copy a range from excel to outlook mail body. no errors, but when the macro is ran i get

Please order from the inserted excel. True

i can copy and paste myself manually and i get a somewhat table looking structure with some color, easily visible and ledgible. but then if i use it in vba it doesnt give me same results.

im trying to accomplish this with .pastespecial

i have tried using for loops, but then its not formatted at all and impossible to read, at least without alot of work. seems like i should be able to just copy and paste..

heres the code.

    Sub Send_Email()
'sends full order to joey@

    Dim xRg As Range
    Dim xAddress As String
    Dim xMailOut As Outlook.MailItem
    Dim xOutApp As Outlook.Application
    Dim orderTime As String
    Dim orderDate As String
    Dim orderTimeDateFinal As String
    Dim xEmailBody As String
    
    orderDate = Format(Date, "MM-DD-YY")
    orderTime = Format(Time, "hh-nn AM/PM")
    
    orderTimeDateFinal = orderTime & " : " & orderDate
    
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Range("A4:V50")
    
    xRg.Copy
    
    MsgBox xRg
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
   
        xEmailBody = "Please order from the inserted excel." & vbNewLine & xRg.PasteSpecial
    With xMailOut
        .Subject = "CEM Tooling Order " & orderTimeDateFinal
        .Importance = olImportanceHigh
        .To = "[email protected]"
        .Body = xEmailBody
        .Display
        'remove comment on line below to auto send the emails.
        '.Send
    End With
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

The problem is you're doing a .PasteSpecial while setting a variable:

xEmailBody = "Please order from the inserted excel." & vbNewLine & xRg.PasteSpecial

What you want to do instead, is create a string that is all of you xRg values. (I think? Do you really want to send 1034 cells' data (A4:V50) to the email?)

Try something like this:

xAddress = ActiveWindow.RangeSelection.Address
Set xRg = Range("A4:V50")
If xRg Is Nothing Then Exit Sub

Dim xRgValues as String
For Each cell In xRg.Cells
    ' This goes by column, then row. So A4, then B4, ..., V4, A5, B5, ...
    If cell.Address = xRg.Cells(1, 1).Address Then
        xRgValues = cell.Value
    Else
       ' Change the comma as needed for the separator.
        xRgValues = xRgValues & ", " & cell.Value
    End If
Next cell
.... 
xEmailBody = "Please order from the inserted excel." & vbNewLine & xRgValues

Edit: I wrote this Answer before clarifying, so it may or may not apply. This takes simply the values, no formatting, and puts that in the email. But perhaps the general loop idea is helpful.

CodePudding user response：

You could try using the word editor but I think it needs to be displayed to work.

Option Explicit

Sub Send_Email()

    Dim xRg As Range
    Dim xMailOut As Outlook.MailItem
    Dim xOutApp As Outlook.Application
    
    Set xRg = Range("A4:V50")
    xRg.Copy

    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
   
    ' word objects
    Dim OutInsp, oWrdDoc, sText As String
    sText = "Please order from the inserted excel." & vbCrLf & vbCrLf
    With xMailOut
        .Display
        Set OutInsp = .GetInspector
        Set oWrdDoc = OutInsp.WordEditor
        With oWrdDoc
           .Paragraphs.Add.Range.Text = sText
           .Paragraphs.Add.Range.Paste
        End With
     
        .To = ""
        .Subject = "CEM Tooling Order " & Format(Now, "MM-DD-YY hh-nn AM/PM")
        .Importance = olImportanceHigh
           
    End With

    Set xMailOut = Nothing
    Set xOutApp = Nothing
   
End Sub