Generate an email with a range from a worksheet?-CodePudding

I'm new to this and got my first Excel macro working yesterday. I've create a command button on Excel to set up an email and I want to send a range from the worksheet. I would like it to keep the formatting if possible. I believe the issue is with:

xMailBody = ThisWorkbook.Activeworksheet("Sheet1").Range("AA65:AE67")

Everything else worked okay.

Thank you very much.

Sonny

Private Sub CommandButton1_Click() 

'Updated by 2022/09/16 

    Dim xOutApp As Object 

    Dim xOutMail As Object 

    Dim xMailBody As String 

    On Error Resume Next 

    Set xOutApp = CreateObject("Outlook.Application") 

    Set xOutMail = xOutApp.CreateItem(0) 

    xMailBody = ThisWorkbook.Activeworksheet("Sheet1").Range("AA65:AE67") 

                  On Error Resume Next 

    With xOutMail 

        .To = Range("AD69") 

        .CC = "" 

        .BCC = "" 

        .Subject = Range("AD70") 

        .Body = xMailBody 

        .Display   'or use .Send 

    End With 

    On Error GoTo 0 

    Set xOutMail = Nothing 

    Set xOutApp = Nothing 

End Sub

CodePudding user response：

You have xMailBody declared as a string then are stating that it is the desired range.

Try DIMing it as a range!

CodePudding user response：

First of all, I've noticed that you are trying to set the Body property which is a plain text string:

.Body = xMailBody

If you need to preserve formatting you can create a well-formed HTML formatting and then assign it to the HTMLBody prperty of Outlook items.

The Outlook object model supports three main ways of customizing the message body:

The Body property returns or sets a string representing the clear-text body of the Outlook item.
The HTMLBody property of the MailItem class returns or sets a string representing the HTML body of the specified item. Setting the HTMLBody property will always update the Body property immediately. For example:

     Sub CreateHTMLMail() 
       'Creates a new e-mail item and modifies its properties. 
       Dim objMail As Outlook.MailItem 
       'Create e-mail item 
       Set objMail = Application.CreateItem(olMailItem) 
       With objMail 
        'Set body format to HTML 
        .BodyFormat = olFormatHTML 
        .HTMLBody = "<HTML><BODY>Enter the message <a href="http://google.com">text</a> here. </BODY></HTML>" 
        .Display 
       End With 
     End Sub

The Word object model can be used for dealing with message bodies. In that case you can just copy the required range in Excel and then paste to the message directly using the Paste method from the Word object model. See Chapter 17: Working with Item Bodies for more information.

Also you may consider using the RangetoHTML function to convert Excel data to the HTML markup.