Send a single e-mail to multiple recipients if certain cell is not populated with a value

[mikenelena]

I have a list of e-mail addresses in column D. Column B populates from a query as mileage reports are received from employees. Until a report is received and captured by the query, the cell shows "#N/A" (A VLookup with no result yet.)

I would like to send a single e-mail reminder to all employees that have not submitted a mileage report.

This is what I am working with so far. The line in red captures ALL the e-mail addresses, and needs to change, but to what? I'm guessing an "If" statement, but can't craft it properly.

Public Sub Mail_Workbook_1Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim EmailTo As String
    Dim BCCTo As String
    Dim strbody As String
    
[COLOR=#ff0000]    BCCTo = Join(Application.Transpose(Worksheets("MileageReports").Range("D5:D26").Value), ";")[/COLOR]
        
    strbody = "****** style=font-size:11pt;font-family:Calibri>Your mileage report is overdue." </BODY>"


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    
    On Error Resume Next
            With OutMail
                    .Display
                     .BCC = BCCTo
                    .Subject = "Mileage Reports Due"
                    .HTMLBody = strbody & "<br>" & .HTMLBody
                                        
            End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

Any help would be most appreciated!

Thanks,
...Mike

 

[Anthony47]

You cannot use JOIN to get all the due addresses, but need a loop. Try replacing your JOIN line with this code:

With Worksheets("MileageReports")
    For i = 5 To 26
        If IsError(.Cells(i, "B")) Then
            BCCTo = BCCTo & .Cells(i, "D") & "; "
        End If
    Next i
End With
BCCTo = Left(BCCTo, Len(BCCTo) - 2)

Bye

 

[mikenelena]

Thanks for your response Anthony. That code is throwing an invalid procedure error on the last line.

[COLOR=#333333]BCCTo = Left(BCCTo, Len(BCCTo) - 2)[/COLOR]

I'm not sure what this line does exactly, so it's difficult to know how to tweak it. Can you point me the right way?

Thanks!

 

[Anthony47]

Thanks for your response Anthony. That code is throwing an invalid procedure error on the last line.

[COLOR=#333333]BCCTo = Left(BCCTo, Len(BCCTo) - 2)[/COLOR]

I'm not sure what this line does exactly, so it's difficult to know how to tweak it. Can you point me the right way?

Thanks!

In responding to your point I forgot to consider that it is absolutely possible that everybody was on time and nothing is overdue...
Replace the instruction BCCTo = Left(BCCTo, Len(BCCTo) - 2) with

If Len(BCCTo) > 4 Then
    BCCTo = Left(BCCTo, Len(BCCTo) - 2)
Else
    MsgBox ("No any report is overdue")
    Exit Sub
End If

Bye

 

[mikenelena]

Anthony,

Thanks so much, that worked perfectly... I greatly appreciate your help!!!