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")
    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


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

© 2003-2016

Updated:  11/16/2016 19:50
This page added:  05 June 2003