Store range into array?

I cant figure out how to set .HTMLBody as the contents of sheet1 (which contains unformatted HTML). Here is the code I'm using (it's also included in the attached workbook):

Sub Mail_Sheet_Outlook_Body()

Dim OutApp As Object
Dim OutMail As Object

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "me@me.com"
.CC = ""
.BCC = ""
.Subject = "hi"
.HTMLBody = "?????????????????????????????????????????????"
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Hi Matchooo,

Your HTMLBody has to be one continuous string so if you want to send the info on sheet1 trying concatenating it all into one cell. In this file I added a column before the HTML with this function in A1:

=CONCATENATE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,B22,B23,B24,B25,B26,B27)

Then all you have to do is change:

.HTMLBody = Range("A1")

Alternatively you can do the concatenation in the code to build the HTML string check this page for more info especially the RangeToHTML Macro - http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Depends if the length will change.

Here this includes a section which will create the HTML from the range in the code:

Sub Mail_Sheet_Outlook_Body()
' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
    Dim OutApp As Object
    Dim OutMail As Object
    Dim FinalRow As Long
    Dim HTMLCell As Variant
    Dim HTMLStr As String
    
    FinalRow = Range("A50000").End(xlUp).Row
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    For HTMLCell = 1 To FinalRow Step 1
        If HTMLCell = 1 Then
            HTMLStr = Cells(HTMLCell, 1)
        Else
            HTMLStr = HTMLStr & Cells(HTMLCell, 1)
        End If
    Next HTMLCell

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
On Error Resume Next
    With OutMail
        .To = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "hi"
        .HTMLBody = HTMLStr
        .Display
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

Don't forget to add reputation by clicking the star on the bottom left if you're query is solved. Mark the thread as Solved too.