Dynamic Email VBA

[KasperSSI]

Hi,

Im looking for some help or inspiration for my project. I have created a dynamic range that show which employees are missing their time reg.

I have made the code so it finds the "To be approved" coloumn. but now i want it to look verticaly to find the "missing" and sum up the names for each approver and send that list in an email.

So in this case, SCH should have a list of two people and MME should have a list of one.

Do you have any suggestions or inspiration for this "issue" ?

Br
Kasper

 

[NateSC]

I have written something like this by filtering the columns, copying the visible cells to a new sheet, emailing that sheet, and then deleting that temporary sheet. If you want to go this route, let me know and I can send you some of the code.

The simplest method is to loop through the rows and see if the value in the cell is "Missing", but this is also the slowest. You could filter first, then loop through visible rows (I haven't tried this before, but it should work). A faster method is to read the data into an array and then do the same loop on the array. This is much faster than looping through the rows in the sheet.

Also, if you want to speed up your search for the Column title, I stole this from elsewhere: (I wrote my own version as a function, but this one should be faster.)

Dim ColNum As Integer
Dim CWS As Worksheet

'Find the column number where the column header is
Set CWS = ActiveSheet
ColNum = Application.WorksheetFunction.Match("To be approved", CWS.Rows(1), 0)

This will get you a column number, but you can use "Cells(Row_Index,Column_Index)" to interact with the sheet as needed.