Results 1 to 8 of 8

Macro to send individual tabs from a worksheet to different email address based on in

Threaded View

  1. #1
    Registered User
    Join Date
    06-27-2014
    Location
    san diego
    MS-Off Ver
    2011
    Posts
    4

    Question Macro to send individual tabs from a worksheet to different email address based on in

    (source)
    I'm not quite sure what I'm doing wrong but i I get the "Please activate the data sheet to parse/email and then run this macro. Aborting." message every time. I am always on the supplier sheet so it is the active sheet. I made some minor code adjustments to fit my needs but i don't see why they would be effecting it. here it is:

    Option Explicit
    
    Sub ParseItemsAndEmail()
    'Jerry Beaucaire  (4/22/2010)
    'Based on selected column, data is filtered to individual workbooks
    'workbooks are named for the value plus today's date
    Dim LR As Long, BR As Long, Itm As Long, vCol As Long
    Dim ws As Worksheet, vTitles As String, SvPath As String
    Dim MyArr As Range, Supplier As Range
    Dim OutApp As Object, OutMail As Object
    'On Error GoTo ErrHandl
    
    If ThisWorkbook.Name = ActiveWorkbook.Name Then
        MsgBox "Please activate the data sheet to parse/email and then run this macro. Aborting."
        Exit Sub
    End If
    Application.ScreenUpdating = False
    
    'Column to evaluate from, column A = 1, B = 2, etc.
       vCol = 4
     
    'Sheet with data in it
       Set ws = ActiveSheet
    
    'Path to save files into, remember the final \
        SvPath = ThisWorkbook.Path
    
    'Range where titles are across top of data, as string, data MUST
    'have titles in this row, edit to suit your titles locale
        vTitles = "A1:Z1"
       
    'Spot bottom row of data
       LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
    
    'Put list into an array for looping (values cannot be the result of formulas, must be constants)
        Set MyArr = ThisWorkbook.Sheets("Suppliers").Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeConstants)
    
    'Turn on the autofilter, one column only is all that is needed
        ws.AutoFilterMode = False
        ws.Range(vTitles).AutoFilter
    
    'Loop through list one value at a time
        For Each Supplier In MyArr
            ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=Supplier.Value
            BR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
            If BR > 1 Then
                ws.Range("A1:A" & BR).EntireRow.Copy
                Workbooks.Add
                Range("A1").PasteSpecial xlPasteAll
                Cells.Columns.AutoFit
                ActiveWorkbook.SaveAs SvPath & ws.Name & " " & Supplier.Offset(, 2) & ".xls", xlNormal
        
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
    
                With OutMail
                    .Subject = "Weekly Income Statement"
                    .To = Supplier.Offset(, 1)
                    '.CC =
                    .Attachments.Add ActiveWorkbook.FullName
                    .Send '.or Display to display
                End With
                
                ActiveWorkbook.Close False
                
                ws.Range(vTitles).AutoFilter Field:=vCol
                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        Next Supplier
    
    'Cleanup
        ws.AutoFilterMode = False
        Application.ScreenUpdating = True
    End Sub
    Last edited by JBeaucaire; 06-28-2014 at 03:13 AM. Reason: Added missing CODE tags, moved to own thread. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 05-26-2013, 07:45 AM
  2. Want to send individual tabs of a worksheet to different email addresses
    By billbirch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2013, 07:38 PM
  3. Send email to address (in cell)l and individual pdf files (in cell)
    By oc2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-19-2012, 06:51 PM
  4. Replies: 6
    Last Post: 12-02-2011, 02:14 PM
  5. VBA code to look up email address and send worksheet
    By JSB0009 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2010, 10:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1