Excel VBA

css navigation by Css3Menu.com

Mail from Excel

From time to time, I have needed to mail a spreadsheet to a bunch of people after it has been broken into component parts.

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")
    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
    With usrAPinvoices
        If usrAPinvoices.optEmailRpts = True Then
            Application.DisplayAlerts = False
            'mailing routine
            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
            'don't mail
        End If
    End With
End Sub

As usual, the ENTIRE solution is not here, simply enough to get you started.

© 2003-2017

Updated:  06/14/2017 17:53
This page added:  05 June 2003