VBA code to create an email not importing names in "To, CC, or BCC"

[jmk15315]

Good morning.

I have a VBA code that works. By that, I mean that it opens Outlook to create a message and populates some of the information, but does not pull the recipients from the list in my worksheet.
I was hoping someone could help point me toward what I am doing wrong, or where the problem might reside.

This is the code I have.

Private Sub CommandButton150_Click()
   
' EMAIL ROBOT FORCAST TO ABB REP

    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
   
        With OutMail
            .To = Sheet26.Range("ER6:ER18")
            .CC = Sheet26.Range("ER19:ER24")
            .BCC = Sheet26.Range("ER26:ER28")
            .Recipients.ResolveAll
            .Subject = "ROBOT FORCAST (COBOT SYSTEM)" & "REFERENCE" & "-" & Sheets("SUMMARY").Range("B5")
            .Body = "Attached is a pdf copy of the Robot Forcasting sheet."
            .Attachments.Add
            .Display
        End With
        On Error GoTo 0
    
' INDICATE EMAIL SENT 
 MsgBox "File sent successfully"
        
        Set OutMail = Nothing
        Set OutApp = Nothing

End Sub

Any thoughts would be helpful

 

[Alex Blakenburg]

What happens when you do this ?

            .To = Join(Application.Transpose(Sheet26.Range("ER6:ER18")), ";")
            .cc = Join(Application.Transpose(Sheet26.Range("ER19:ER24")), ";")
            .bcc = Join(Application.Transpose(Sheet26.Range("ER26:ER28")), ";")

 

[jmk15315]

That solved my issue. Thanks

 

[Alex Blakenburg]

You're welcome. Thanks for letting me know.