Automatic Outlook Email with Multiple Fields From Excel File

Do you want to send one email per row of data?

Here's a shot assuming you want to process all. I converted the Tracker into an Excel Table and added some data validation for the completed columns.

It is also sending emails regardless of a if there isn't a deadline being either within 7 days or overdue

So you only want an email if any of the dates are within 7 days or overdue and the only thing you want in the body are those dates that fit these categories? Do you, or do you not want to report completed dates?

OK, I think I have it this time.

To help a bit with the reverse engineering: I declared most of the variables as global (they are declared in the module and outside of a subroutine or function). This means that the variables are known to all subroutines and functions inside the module. Otherwise, I would have to pass this information to the subordinate routines.

I put the information in an Excel Table (see this wiki: http://www.utteraccess.com/wiki/Tables_in_Excel) mainly for two reasons: (1) Tables know how big they are, so I don't have to figure out where the data begin and end. (2) I can refer to the data by table name and column header name which makes it easier to maintain the code.

It's always a good idea to tell Excel VBA where you want it to be. Your idea of active sheet and Excel's idea of active sheet may be different. There are two ways to tell VBA what sheet to use. (1) The long way: Sheets("SheetName") or (2) use what I call a "pointer:" Set shT = Sheets("Tracker") - the second method saves a lot of typing and has an additional benefit if you have more than one workbook open. A sheet pointer knows what workbook it belongs to.

I set another pointer, this time to a range: clT - since it is a range, it can point to an individual cell. This is what the loop: For Each clT In Range("Table_Tracker[Project]") does. It takes clT and points to each cell, in order, in the column Project in table Table_Tracker. Again, it's a table so it knows what rows the data begin and end on. I use the row number of the current cell (clT.Row) in some of the calculations.

MakeBody is a subroutine that takes 3 parameters: DateType, DateCell and CompletedCell. I do the same manipulations for each of the dates, so rather than repeat the code for each date, I take the parts that are different (mainly where they are) and pass them to the subroutine.

The subroutine "assembles" the body string for the message. Also if a date is overdue or due within 7 days, it sets a flag, SendFlag to TRUE - this tells the mail program if it should send this mail. So there is some waste in that I calculate a body whether the mail is going to be sent or not.

That's about it. If you have any other questions about the code, feel free to ask.

Oh, and one more thing (this should be the first thing to tell anyone about VBA):
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.