VBA Code - add emails to outlook BCC from list (that updates)

[NormChart55]

Hello All - I am looking to tweak my macro to pull the .BCC emails from a pivot list that could change month to month (as users are added/removed from a program) and add them into the outlook email that is getting created. The pivot email data starts at Sheet2 cell E5 but the end of the pivot could be variable month to month. Hoping for an easy solution to be able to pull this information into the .BCC section of the email.

Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object

Dim StrBody As String

LDate = Date

StrBody = "Text," & "<br>" & "<br>" & _

"Text." & "<br>"

Set rng = Nothing

On Error Resume Next

'Only the visible cells in the selection

'Set rng = Selection.SpecialCells(xlCellTypeVisible)

'You can also use a fixed range if you want

Set rng = Sheets("Ref").Range("AX1:AY1").SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

With Application

.EnableEvents = False

.ScreenUpdating = False

End With

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.CC =

.BCC =

.Subject =

.Display 'or use .Display

.HTMLBody = StrBody & RangetoHTML(rng) & .HTMLBody

End With

On Error GoTo 0

With Application

.EnableEvents = True

.ScreenUpdating = True

End With

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

 

[NormChart55]

Any thoughts out there on this one?