Email to include ranges from different sheets

user_10631384 0
Viewed 50

I wanted to use VBA to send an outlook email with ranges from three different sheets in the workbook, how can I combine the three ranges(rng1 then leave a blank row, then rng2 and leave a blank row, then rng3) in order to show them in the same email? I tried the below code but there is a

type mismatch error

Set rngComb = rng1 & rng2 & rng3

I'm not sure how to modify it.

Sub combEmail()
Dim OutApp As Object, OutMail As Object
Dim rngComb As Range, rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = ThisWorkbook.Sheets("Sheet1").Range("C12:F14")
Set rng2 = ThisWorkbook.Sheets("Sheet2").Range("C16:F18")
Set rng3 = ThisWorkbook.Sheets("Sheet3").Range("H12:K14")
Set rngComb = rng1 & rng2 & rng3

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

    With OutMail
        .To = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
        .Subject = "CombinedNotice"
        .HTMLBody = RangetoHTML(rngComb)
        .display
    End With
    Set OutMail = Nothing
        
End Sub
outlook vba excel
1 Answers

Using Ron de Bruin's RangetoHTML

You would need to run RangetoHTML on each one individually. That will give you an HTML String for each one.

They can then be concatenated.

Dim sHTML As String
sHTML = RangetoHTML(rng1) & "<br>" & RangetoHTML(rng2) & "<br>" & RangetoHTML(rng3)

and then

.HTMLBody = sHTML

Or just:

.HTMLBody = RangetoHTML(rng1) & "<br>" & RangetoHTML(rng2) & "<br>" & RangetoHTML(rng3)

To add more space between each one, add more <br> tags. - "<br><br><br><br>"

From the RangetoHTML code page:

Important: Be aware that if you have set Excel to R1C1 reference style the code will not work. You can add this line at the start of your macro to be sure it is set to xlA1 style

Application.ReferenceStyle = xlA1

And this one to set it back if you need

Application.ReferenceStyle = xlR1C1
user_84229530
Related