VBA Merge Cells with semicolon

[Jasesair]

Excel can be most frustrating at times! I simply want to correct the .BCC line for this emailing macro to work. I'm wanting everything in cells AU7:AU107 to be added to my BCC line in an Outlook email but separated by a semicolon ( ; ). Would appreciate some support so I can move on to the next challenge!!!

Sub EmailJuniors()

    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 = Range("")
        .CC = Range("")
        .BCC = Join(Application.Transpose(.Range("AU7:AU107").Value), ";") & ";"
        .Subject = Range("AP3")
        .Body = Range("AO2") & vbLf & _
                "" & vbLf & _
        .Display
    End With
    On Error GoTo 0

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

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Fluff]

Try removeing the full stop in front of Range

 

[Jasesair]

Why thankyou!!! So simple.

 

[Fluff]

You're welcome & thanks for the feedback.