Generate E-mail from within Excel based on conditional format outcome. 1

[wbow]

Hi,
Origianlly posted in MS Office forum;

I have a created a spreadsheet [Excel 2003]for a non-technical user which lists engineers, who they report to and a listing of their qualification along with the qualification expiry dates.

I have used conditional formatting to change the colours of the dates when they are 6 months from exipry [amber] and expired [red].

I have been asked if it is possible to e-mail each record where either of the two conditions above are met, to whom they report to from within Excel.

Can anyone suggest a method [VBA maybe?] to achieve this?
Many thanks.

 

[SkipVought]

This IS the MS Office Forum.

You need to post VBA questions in Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Non VBA:
Add a column "flag" with a formula to identify those that meet the two conditions:
=(Expiry-Today())<365/2

Create a pivot table with page fields of Flag and ReportsTo.
Row Fields for all the different data from your table (name, Qualification)
Data field of Expiry date

Set Row fields not to sub-total
In the Qualification field properties, Advanced choose to sort by Expiry date, Ascending

Set format for date field to date.

Select "True" from the dropdown for the Flag column - only data that meets the conditions will display.

From the PivotToolbar choose ShowPages of ReportsTo .

You should now have one sheet per manager showing the data you want, with the closest (or expired) qualifications listed first.

Without VBA you must now move each sheet to a new workbook and email it.

You could of course record yourself doing that and then post in the VBA forum to get help modifying the code to do exactly what you want.

Gavin