Generate an email with a range from a worksheet

Question

I've a command button on Excel to set up an email.

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")

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

See my response here: https://stackoverflow.com/questions/73005935/send-each-row-an-excel-spreadsheet-as-an-individual-email-in-outlook/73008522#73008522 — karl, Sep 18 '22 at 17:24
Does this answer your question? [Send each row an Excel Spreadsheet as an individual email in Outlook](https://stackoverflow.com/questions/73005935/send-each-row-an-excel-spreadsheet-as-an-individual-email-in-outlook) — karl, Sep 18 '22 at 17:24
Use Ron de Bruin's [RangetoHtml](https://www.rondebruin.nl/win/s1/outlook/bmail2.htm) — CDP1802, Sep 18 '22 at 17:33
Does this answer your question? [Paste Excel range in Outlook](https://stackoverflow.com/questions/18663127/paste-excel-range-in-outlook) — niton, Sep 19 '22 at 07:29
Hi niton, not sure why but nothing shows up from the range. I filled my sheet from D4 to D12. Thank you — Sonny, Oct 05 '22 at 23:24
I think I underestimated this endeavor but will continue to plug away at it. I was thinking it was going to be a simple line addition like adding an attachment but it's not. Thank you everyone for your responses. — Sonny, Oct 05 '22 at 23:27

score 0 · Answer 1 · answered Sep 18 '22 at 17:31

0

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

Try DIMing it as a range!

answered Sep 18 '22 at 17:31

user16099339

1
1

How does it help to set up the message body? – Eugene Astafiev Sep 18 '22 at 18:03

score 0 · Answer 2 · answered Sep 18 '22 at 18:09

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.

Generate an email with a range from a worksheet

2 Answers2