Results 1 to 4 of 4

Send unique values + top 2 header rows macro

Threaded View

  1. #1
    Registered User
    Join Date
    09-30-2019
    Location
    Tamworth, Staffordshire
    MS-Off Ver
    2010
    Posts
    4

    Send unique values + top 2 header rows macro

    Hi All,

    Just looking to see if someone could help me with this code i got from Ron De Bruin's website. It copies particular rows (+ the header row) of a sheet based on the value in column A, and pastes it into an Email body, sending to the applicable Email recipient in another tab. It works an absolute treat, except i need it to copy the unique values + the top 2 header rows (as opposed to just the top row). I

    Any help would be much appreciated. Thanks in advance.

    Sub Send_Row_Or_Rows_1()
    'Don't forget to copy the function RangetoHTML in the module.
    'Working in Excel 2000-2016
        Dim OutApp As Object
        Dim OutMail As Object
        Dim rng As Range
        Dim Ash As Worksheet
        Dim Cws As Worksheet
        Dim Rcount As Long
        Dim Rnum As Long
        Dim FilterRange As Range
        Dim FieldNum As Integer
        Dim mailAddress As String
        Dim StrBody As String
    
        On Error GoTo cleanup
        Set OutApp = CreateObject("Outlook.Application")
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        'Set filter sheet, you can also use Sheets("MySheet")
        Set Ash = ActiveSheet
    
        'Set filter range and filter column (Column with names)
        Set FilterRange = Ash.Range("A1:AJ" & Ash.Rows.Count)
        FieldNum = 1    'Filter column = A because the filter range start in A
    
        'Add a worksheet for the unique list and copy the unique list in A1
        Set Cws = Worksheets.Add
        FilterRange.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=Cws.Range("A1"), _
                CriteriaRange:="", Unique:=True
    
        'Count of the unique values + the header cell
        Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
    
        'If there are unique values start the loop
        If Rcount >= 2 Then
            For Rnum = 2 To Rcount
    
                'Filter the FilterRange on the FieldNum column
                FilterRange.AutoFilter Field:=FieldNum, _
                                       Criteria1:=Cws.Cells(Rnum, 1).Value
    
        'Add text to email body
        StrBody = "Please see weekly planner below." & "<br>" & _
                "" & "<br>" & _
                "Kind Regards" & "<br><br><br>"
    
                'Look for the mail address in the MailInfo worksheet
                mailAddress = ""
                On Error Resume Next
                mailAddress = Application.WorksheetFunction. _
                              VLookup(Cws.Cells(Rnum, 1).Value, _
                                    Worksheets("Mailinfo").Range("A1:B" & _
                                    Worksheets("Mailinfo").Rows.Count), 2, False)
                On Error GoTo 0
    
                If mailAddress <> "" Then
                    With Ash.AutoFilter.Range
                        On Error Resume Next
                        Set rng = .SpecialCells(xlCellTypeVisible)
                        On Error GoTo 0
                    End With
    
                    Set OutMail = OutApp.CreateItem(0)
    
                    On Error Resume Next
                    With OutMail
                        .To = mailAddress
                        .Subject = "Planner - " & ActiveSheet.Range("A2")
                        .HTMLBody = StrBody & RangetoHTML(rng)
                        .Display  'Or use Send
                    End With
                    On Error GoTo 0
    
                    Set OutMail = Nothing
                End If
    
                'Close AutoFilter
                Ash.AutoFilterMode = False
    
            Next Rnum
        End If
    
    cleanup:
        Set OutApp = Nothing
        Application.DisplayAlerts = False
        Cws.Delete
        Application.DisplayAlerts = True
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    Last edited by davesexcel; 09-30-2019 at 12:51 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to filter column and copy rows to new sheet with loop for all unique values in colum
    By Gryphoune in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2015, 09:15 AM
  2. macro to send an email for every unique item
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-08-2014, 11:34 PM
  3. VBA code to convert multiple rows as column header grouped by unique key
    By Shreyas11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2014, 09:03 PM
  4. [SOLVED] Macro that merges duplicate rows based on unique values - Need to edit current code
    By niya429 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2014, 02:57 PM
  5. [SOLVED] Macro to merge duplicate rows unique values - current macro not working
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2013, 03:42 PM
  6. Macro to send Email to Unique Recipients
    By jhuang in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-17-2012, 02:37 AM
  7. Replies: 3
    Last Post: 07-07-2011, 08:24 PM

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