Create Outlook e-mails from a filtered Excel list

I have recently been challenged with the following problem:

• There is an excel list with names and emails. These names have been divided into Teams also indicated in the list.
• An email is top be sent to each team, with a customized message.
• Message should start with “Dear [Name 1], [Name 2], … and [Last Name], where all names are team members,
• Email addresses of all team members should be added to the email.

This was a nice little challenge to use VBA (Visual Basic for Applications) as a way of automating repetitive processes such as this one, which seems to be a pretty standard requirement. My approach is pretty straightforward and has four stages:

1.- Create a Macro-enabled Excel file and Enable Outlook Objects

Create a macro-enabled Excel file (xlsm) and enable the Microsoft Outlook Object library (Tools menu in the VBA Editor in the Excel Developer Ribbon).

Define variables to access Outlook and activate the specific sheet with Data:

Sub Macro1_Email()
    Dim OutlookApp As Outlook.Application
    Dim OutlookMail As Outlook.MailItem
    
    Set OutlookApp = New Outlook.Application
    Set OutlookMail = OutlookApp.CreateItem(olMailItem)
    Worksheets("Sheet with data").Activate

2. Obtain the range — User Input

I used an input message box to define the range which should contain Names and Emails:

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox(Title:="Please Select a Range", Prompt:="Please select the Name and Emails", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub

The last cell is identified to customize the email body:

Dim rngLast As Excel.Range
Set rngLast = rng.Columns(1).Cells(rng.Columns(1).Cells.Count)

3. Build the message components

Considering that the names would be in the first column of the input and the emails in the second columns, first the email list is built, for which we use the SpecialCells(xlCellTypeVisible) method, particularly using the xlCellTypeVisible cell type:

str_to = ""
For Each OneCell In rng.Columns(2).SpecialCells(xlCellTypeVisible)
   If str_to = "" Then
        str_to = OneCell.Value
   Else
        str_to = str_to & ";" & OneCell.Value
   End If
Next OneCell

Next the greeting is built from the filtered names, using a FOR statement to go through all Cells in the range (also restricted to the visible cells), and then using an IF THEN statement to identify if each name is either the first in the list, last in the list or in another middle position, since they will have different treatment:

str_greet = "Dear "
For Each OneCell In rng.Columns(1).SpecialCells(xlCellTypeVisible)
    If str_greet = "Kære " Then
        str_greet = str_greet & OneCell.Value
    ElseIf OneCell.Address = rngLast.Address Then
        str_greet = str_greet & " and " & OneCell.Value & ","
    Else
        str_greet = str_greet & " ," & OneCell.Value
    End If
Next OneCell

4. Build the email in Outlook using some of the properties of the Outlook Object.

With OutlookMail
   .BodyFormat = olFormatHTML
   .Display
   .HTMLBody = str_greet
   .To = str_to
   .Subject = "Email Title here"
End With

If other parts of the e-mail are also automatized, then the email can eventually also be sent automatically using the Send Event available for Outlook Objects. I hope this helps.