Automatic Outlook Email with Multiple Fields From Excel File

[tw1ggy5]

https://www.excelforum.com/excel-pro...xcel-file.html

I was looking at something similar to this, however it needs to have multiple outputs in the mail as we are tracking multiple targets.

So taking the example from my excel file attached for the first line. Output to be something along the lines of; (referenced cell)

----------------------
To : me@email.com (E3)

Subject : Test Project (B3)

Monitoring dates have been passed for this project

Start Date (L2) is overdue by 2 day(s) (L2-date), it is/isn't(M2) marked complete.
Board Approval (N2) is overdue by 1 day(s) (N2-date), it is/isn't(O2) marked complete.
Finalise Appointments (P2) is due in 0 day(s) (P2-date), it is/isn't(Q2) marked complete.
Final Draft (R2) is due in 1 day(s) (R2-date), it is/isn't(S2) marked complete.
Final Draft (T2) is due in 1 day(s) (T2-date), it is/isn't(U2) marked complete.
End Date (V2) is due in 2 day(s) (V2-date), it is/isn't(W2) marked complete.
----------------------

It needs to check if any dates on the row are due within 7 days/due/expired that are not labelled with the corresponding Complete cell has a Y in it. Eg M3 for L3 (Start date)

If any of them flags it, to send the above format email with updates on all the dates
I am intending to run the excel sheet in task scheduler upon boot, so it can then auto send emails to remind whoever/me at the start of the day.

[dflak]

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

[dflak]

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.

[tw1ggy5]

Brilliant thanks! Works exactly how I hoped, tho I have noticed it only seems to process and send for the last line.

Having thought about it more, ideally I would like the option to send one per line so we can separate for projects, send based on the project leader and have an overview sent of all lines to the Monitoring officer.

[tw1ggy5]

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

[dflak]

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?

[tw1ggy5]

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?

Yes only if any within 7 days or overdue.
For that project I want all dates reported even if completed.

So if project 1 has no dates are within 7 days, due or overdue then no email.
If project 2 has 1 date due it sends all dates for that project.

[dflak]

OK, I think I have it this time.

[tw1ggy5]

Thank you, I had time put aside to try this on Friday but not a great idea of where to start, will be trying to reverse engineer how this all works to be able to change it when necessary. That seems to be sending an email per line, only if there is a monitor point that has not got a Yes by it and is either within 7 days, due today or overdue. So exactly what I was after. Well once I worked out where to uncomment the email send line, I am fairly new to macros, I have also commented out the msgbox so it cycles a bit faster.

An option to include all rows in one email is an additional feature that would be useful, as well as the option to bundle multiple project reports by the same Mnitoring Officer or Lead Officer. I am thinking I should be able to figure this out from here now you have done the structure of the thing. Thanks again.


It does seem that outlook each time has a moan about another application trying to send a message and then I have to give permission but I think that is just the system security here.

[dflak]

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.