Excel to Outlook Email Recipients and Formatting

[twothings]

Greetings

I have below code open Outlook from Excel, pull an email address to BCC field, subject and some contents. What changes are required to pull multiple emails from cells B12:B18, B21:22, B29, B32 and B35 to the BCC field?

Also, is it possible to pull across a Richtext formatted email I have in the excel sheet, either in a range of cells or a single cell?

Your help is most appreciated

Sub email()    Dim a As Integer
    Dim objOutlook As Object
    Dim objMail As Object
    Dim rngTo As Range
    Dim rngSubject As Range
    Dim rngBody As Range
    Dim rngAttach As Range
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    
    a = ActiveCell.Row
    
    With ActiveSheet
        Set rngTo = Range("B29")
        Set rngSubject = Worksheets("Admin").Range("B3")
        Set rngBody = Worksheets("Admin").Range("B6")
    End With
    
    With objMail
        .Bcc = rngTo.Value
        .Subject = rngSubject.Value
        .Body = rngBody.Value
        .Display

    End With
    
    Set objOutlook = Nothing
    Set objMail = Nothing
    Set rngTo = Nothing
    Set rngSubject = Nothing
    Set rngBody = Nothing
    Set rngAttach = Nothing

End Sub

 

[twothings]

Just a followup, for the email list range I tried using the following and close variations of it without success.

[COLOR=#333333]Set rngTo = Range("[/COLOR]B12:B18[COLOR=#333333]")
[/COLOR]

and

[COLOR=#333333]Set rngTo = Range("B29","[/COLOR]B32", "B35[COLOR=#333333]")
[/COLOR]

 

[twothings]

I'm still trying to figure this out. Should this be a strong instead of a range?

[COLOR=#333333]Dim rngTo As Range[/COLOR]

 

[twothings]

*bump*

 

[twothings]

I have managed to get this working through some alterations. Is it possible to maintain email body formatting with font, sizing, colour etc etc

Sub taxis()

Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Dim c As Range
Dim bcc1, bcc2, bcc3, bcc4 As Variant


    bcc1 = Worksheets("Notifications").Range("C28").Value
    bcc2 = Worksheets("Notifications").Range("C30").Value
    bcc3 = Worksheets("Notifications").Range("C32").Value
    bcc4 = Worksheets("Notifications").Range("C34").Value
    
        Email_Subject = Worksheets("Admin").Range("B3")
        Email_Send_To = ""
        Email_Cc = ""
        Email_Bcc = bcc1 & ";" & bcc2 & ";" & bcc3 & ";" & bcc4 & ";"
        Email_Body = Worksheets("Admin").Range("B6") 'modify as needed
    
    
    On Error GoTo debugs
    Set Mail_Object = CreateObject("Outlook.Application")
    Set Mail_Single = Mail_Object.CreateItem(0)
    With Mail_Single
        .Subject = Email_Subject
        .To = Email_Send_To
        .CC = Email_Cc
        .Bcc = Email_Bcc
        .Body = Email_Body
        .Display 'Change to Send to send without viewing
    End With
debugs:
    If Err.Description <> "" Then MsgBox Err.Description


    'End If


End Sub