vba to export dynamic range to Outlook html body

[dshafique]

Hi everyone, I have been using rangeToHtml recently to automate sending out emails, and I am in love with it. i am still trying to learn some things and was wondering if there is a way for me to paste a dynamic range instead of a static range. for example, every month, I send out emails to managers containing their data from a spreadsheet. some months they have only 3 rows of data, some months could be 30. is there a way for me to make it so the email will check for "manager" in the manager column and copy every row containing the same manager and put it in the email? and then move on to the next manager in the list. this would really help me out a lot, this is what I have at the moment:

For Each cl In rng.SpecialCells(xlCellTypeVisible)    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = cl.Offset(0, 1).Value
        .CC = cl.Offset(0, 6).Value
        .BCC = ""
        .Subject = "Finding Number " & cl.Offset(0, 3) & " Review For the Month of " & MonthName(Month(Date))
        .HTMLBody = "Hi Folks, " & "<br>" & "<br>" & _
        "Can you please take a look at the following Findings and advise whether it can be closed or extended? Reporting on expired items for the business review meeting " & _
        "<br>" & RangetoHTML(Range("A" & cl.Row, "L" & cl.Row)) & "<br>" & "<br>" & "Thanks"
        .Display
    End With
    On Error GoTo 0

thanks in advance!

 

[Domenic]

Simply filter the the data for each manager, and pass each filtered range to RangeToHTML.