Excel VBA

Mail from Excel

In this example, there is a list on another page that contains BranchIDs and email addresses to send to. The list is read one Branch at a time and that branch’s data pulled out.

Sub MailNow()
    Dim objSht As Object, objCell As Object, mArray, J, Counter, Ranger As String
    Dim EndRow As Long, NamesArray As Variant, mMsg, Alpha
    Set objSht = ThisWorkbook.Sheets("Front")
    ThisWorkbook.Activate
    objSht.Activate
    EndRow = Application.CountA(ActiveSheet.Range("H:H"))
    mArray = Range(Cells(2, 9), Cells(EndRow, 9)).Value
    J = objSht.UsedRange.Rows.Count
    ReDim NamesArray(J, 12)
    Counter = 1
    While Counter <= UBound(mArray)
        Counter = Counter + 1
    Wend
    With usrAPinvoices
        If usrAPinvoices.optEmailRpts = True Then
            Application.DisplayAlerts = False
            'mailing routine
            Workbooks(ExpFileName).Activate
            mMsg = "This Invoice file created on " & Application.Text(Now(), "dd mmm yyyy HH:mm") _
                & ". Advise Harvey Flapdipple immediately with errors."
            If ActiveWorkbook.HasRoutingSlip = False Then
                ActiveWorkbook.HasRoutingSlip = True
            End If
            With ActiveWorkbook.RoutingSlip
                .Recipients = BranchAdmin
                .Subject = Branch & " AP Invoices"
                .Message = mMsg
                .Delivery = xlAllAtOnce
                .ReturnWhenDone = False
                .TrackStatus = True
            End With
           Application.DisplayAlerts = True
        Else
            'don't mail
        End If
    End With
End Sub