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

[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]

Hi,

Here are the elements that you will need in order to code a solution.

1. A trigger to analyze the sheet. Could be a BUTTON, for instance.

2. Logic that defines the range on the sheet to process.

3. Logic for analysis. What condition(s) exist that will flag an item for eMail.

4. The eMail data elements required for each flagged item.

5. The eMail code. That can be supplied here.

6. Logic to identify that an item has been processes, in order that when you run it again, another eMail will not be sent.

We can do #5. You need to supply the remainder.

Please describe each of the unanswered items above.

Skip,

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

 

[wbow]

Hi Skip,
Thanks for thr promt response.Iinfo as follows;
1.Plan for a command button to be inserted into the sheet.
2.Could this be simply the Sheet1!A1:ZZ999 range?
3.I have two reference cells which contain date info. One simply todays date, the other todays date + 90 days[Cells S2 and T2 respectively]. Each cell containing qualification dates is compared to these reference cells and if a date is <Today, cell turns red, if it's <Today + 90 goes amber.
4.Plan to either create a lookup list on a second sheet in the workbook and get the user to allocate an engineer to this contact, or a new column in the main sheet containing the e-mail address to which the alert massage should be sent. The latter is probably easiest for the user to maintain.
6.Could the VBA code which sends the mail, populate a column with a tick. This could be adjacent to the cell being used as the trigger.

Sorry some of this is a little vague, but the user asking for this is away so unable to define what he needs / is easiest.

 

[SkipVought]

2.Could this be simply the Sheet1!A1:ZZ999 range?

Is this a static range that ALWAYS has ALL the data and no empty rows or columns?

Is there ONE table on this sheet?

Is the data contiguous?

The data in what columns get compared to S2 & T2?


4.Plan to either create a lookup list on a second sheet in the workbook and get the user to allocate an engineer to this contact, or a new column in the main sheet containing the e-mail address to which the alert massage should be sent. The latter is probably easiest for the user to maintain.

The elements of an eMail are:

From
To
Subject
Message
Attachments

6.Could the VBA code which sends the mail, populate a column with a tick. This could be adjacent to the cell being used as the trigger.

And what column would that be?

What are ALL the column headings?



Skip,

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

 

[wbow]

Hi Skip,
There is one sheet currently in the workbook.

There will be blanks where the engineer holds no qualification.

The data compared is a date in dd/mm/yy format.
Not sure what you mean by contiguous. There will be no blank rows, but there may be blank cells within the row. does this help?

Column headings are;
LastName
FirstName
NG
Alert Sent
LPG
Alert Sent
GS Card
Alert Sent
CCN1
Alert Sent
CCLP1
Alert Sent
CKR1
Alert Sent
CKRLP1
Alert Sent
HTR1
Alert Sent
HTRLP1
Alert Sent
WAT1
Alert Sent
WATLP1
Alert Sent
CEN1
Alert Sent
CENLP1
Alert Sent
DAH1
Alert Sent
DAHLP1
Alert Sent
LAU1
Alert Sent
LAULP1
Alert Sent
MET1
Alert Sent
METLP1
Alert Sent
LEI1
Alert Sent
LEILP1
Alert Sent
OFTEC101
Alert Sent
PD
Alert Sent
C&G6084
Alert Sent
CONGLP1
Alert Sent
Part P - A
Alert Sent
Part P - C
Alert Sent
Part P - B
Alert Sent
Unvented
Alert Sent
CPA1
Alert Sent
Alert Recipient [email address]
Todays Date
3 Month Alert Date

The "Alert Sent" columns would be the holder for the tick.

Email format would be;
From; a static user name held in the lookup sheet or the e-mail name of the person opening the workbook. Not sure if this is do-able as we use a hosted mail exchange.
To; from the lookup sheet
Subject; LastName, FirstName qualifications expiry alert.
Message; Based on the trigger qualification [column header for cell triggering message] expires/expired [content of cell triggering message].

Thanks.

 

[SkipVought]

You previously stated that processable data would be A1:ZZ999.

1. I assume that this is 2007
2. The columns you posted are from A to BM
3. Columns S & T (that hold your dates in row 2) are
HTRLP1 Alert Sent
Is that true?

4. Each column heading ought to be UNIQUE. Although this is not absolutely required, it can and probably will bite you at some time in the future.

5. "There is one sheet currently in the workbook.",
"To; from the lookup sheet"
That adds up to TWO sheets, I think.

What is the structure of the lookup sheet table?

Skip,

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

 

[wbow]

Hi Skip,
1. Using Office 2003 [apologies for not mentioning ths before].
2. Lookup sheet for e-mail addresses not created yet, but I think it would consist of either just the e-mail address of the person the engineer reports to OR a shortname and the e-mail address. It would also hold the senders details if required.
3. The dates are held in cells below the headers
Todays Date and 3 Month Alert Date.
4. Will amend the Alert Sent headers to be NG Alert Sent, LPG Alert Sent, etc. etc.
5. Columns are indeed A to BM my comment A1:ZZ999 merely served to establish if I was correct in my assumption that we could use a data range as you normally would in Excel.

Thanks,

 

[SkipVought]

Take me thru the logic for Row 2. Please be very detailed, as you have 65 columns to process.

What possible condition(s) would trigger an eMail?

Skip,

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

 

[wbow]

Logic for row 2.
For each qualification date, test against S2 and T2.

If date is less than S2 then
e-mail
From: [static e-mail address]
To: e-mail address in [Alert_Recipient_email address column]
Subject: [Static] "Qualifications Alert"
Message: [LastName, FirstName cell contents for current row], [cell column header], "Qualifiaction expired" [Cell content]

After mail sent, place tick in adjacent cell

If date is less than T2 then
e-mail
From: [static e-mail address]
To: e-mail address in [Alert_Recipient_email address column]
Subject: [Static] "Qualifications Alert"
Message: [LastName, FirstName cell contents for current row],[cell column header], "Qualification due to expire" [cell content]

After mail sent, place tick in adjacent cell.

Hope this is adequate.

 

[SkipVought]

"For each qualification date..."

What is that? Please remember, I know almost NOTHING about your workbook and I cannot read your mind. I asked you to be very detailed.

If there is no difference the the process, why do you repeat? Otherwise the reader, that's ME, has to very carefully read BOTH processes LINE BY LINE and COMPARE to try to determine if there is a difference. THINK! That's one 'detail' that could have been COMBINED as...

If [i]qualification[/i] date is less than S2 or T2 then
...

And please notice the editorial addition that I made. You may think that qualification is assumed. But it could be argued that date MIGHT be today's date, or maybe another date that may pop up later in the description. DETAIL, DETAIL, EXCRUCIATING DETAIL.

Is there more than ONE qualification date in the row?

What happens if there are 2 or more qualification dates that meet the criteria? Do they get multiple eMail's or what?

Please answer ALL questions clearly, concisely and completely.


Skip,

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

 

[wbow]

Apologies,
The row's columns are engineer's LastName then FirstName then 61 colums containing alternately expiry dates for qualifications held and Alert Sent tick, finally a column for the e-mail address of the person he or she reports to.
I was trying to get one row to represent a record of an engineer's entire qualifications.

The headers of these 61 columns are the qualification names and a corresponding Alter_Sent
eg; CCN1, CCN1_Alert_Sent, WAT1, WAT1_Alert_Sent and so on.

Cell S2 contains "=Now()"
Cell T2 contains "=(S2+90)"

It is likely that there could be multiple "qualification expired dates" and "qualification due to expire in three month dates" which from a common sense point of view should only generate a single e-mail.
The user will probably run the e-mail generation code manually weekly or monthly, this has yet to be decided.

The code was repeated as the message changed from "Qualifiaction expired" to "Qualification due to expire", but if this can be done in a single statement as I adduce from you reply, then thats OK.

Column header example:
LastName | FirstName | NG | NG_Alert_Sent | CCN1 | CCN1_Alert_Sent |

 

[SkipVought]

So if there are multiple 'qualification expired dates' then

1. you only want a mark (no Notification Date) in Alert_Sent
2. each 'qualification expired date' set, that meets the criteria, gets a mark in Alert_Sent
3. only one eMail notification is sent.

Does the eMail notification include a list of ALL the qualifications that are due to expire? Your requirement was not clear about this.

What happens as the year rolls to the next?

What happens on the sheet when the engineer re-quailifies?

A much better way might be to have a different kind of table...

EngLast EngFirst QualID QualDate NotifyFlag NotifyDate

A simple query or PivotTable gets you a list of all engineers needing notification (NotifyFlag as simple spreadsheet formula)

I assume that when an engineer re-qualifies, you get notified and change the QualDate somehow?

Skip,

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

 

[wbow]

1. you only want a mark (no Notification Date) in Alert_Sent
2. each 'qualification expired date' set, that meets the criteria, gets a mark in Alert_Sent
3. only one eMail notification is sent.
1 I take your point a date would be better than just a simple tick.
2 and 3 correct.

Does the eMail notification include a list of ALL the qualifications that are due to expire? Your requirement was not clear about this.
Yes with the date of expiry. Similarly a list of all the expired qualifications and their date of expiry

What happens as the year rolls to the next?
As S2 contains "=Now()" this should take account of the annual roll-over?

What happens on the sheet when the engineer re-quailifies? The manager will enter the new expiry date and remove the date from the corresponding Alert_Sent cell. At a later date this may well require automation.

A much better way might be to have a different kind of table...

EngLast EngFirst QualID QualDate NotifyFlag NotifyDate

A simple query or PivotTable gets you a list of all engineers needing notification (NotifyFlag as simple spreadsheet formula). Agree, but have users wihout much PivotTable experience and would like to avoid using one unless really necessary. Agree with the EngLast EngFirst QualID line of thought and additionlly makes things easier to manipulate and identify.

I assume that when an engineer re-qualifies, you get notified and change the QualDate somehow?
Yes the Manager the engineer reports to, arranges re-qualifying and then the date he re-qualified is entered into the sheet.

Many thanks,
WBOW

 

[SkipVought]

Here's the outside loop, and given you an idea about how I'd 'build' an application...

sub Main()
  dim r as range

  with sheets("YourSheetName")
    for each r in .range(.[A2], .[A2].end(xldown))
      'now loop across
      debug.print r.value & "," & r.offset(0,1).value
    next
  next
end sub

If you display the immediate window, you'll see a list of your engineers that debug.printed.

Now the inside loop across the sheet...

Sub Main()
  Dim r As Range, iCol1 As Integer, iCol2 As Integer, iCol As Integer
  Dim bSend As Boolean, sSend As String

    With Sheets("YourSheetName")
        iCol1 = 3
        iCol2 = .UsedRange.Columns.Count
        
        For Each r In .Range(.[A2], .[A2].End(xlDown))
            'now loop across
            Debug.Print r.Value & "," & r.Offset(0, 1).Value
            
            bSend = False
            sSend = ""
            For i = iCol1 To iCol2 - 1 Step 2
                If r.Offset(0, i - 1).Value > [YOUR_DATE_REF] Then
                    bSend = True
                    sSend = sSend & .Cells(1, i).Value & vbTab & Format(r.Offset(0, i - 1).Value, "yyyy/mm/dd") & vbLf
                    r.Offset(0, i).Value = Now
                End If
            Next
        Next
        If dSend Then
            'send the email here
            CdoSend _
                r.Offset(0, iCol2).Value, _
                "FromEmailAddress", _
                "ATTENTION: Requalification", _
                sSend
            
        End If
    Next
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function CdoSend( _
    MailTo As String, _
    MailFrom As String, _
    Subject As String, _
    MessageText As String, _
    Optional CC As String, _
    Optional BCC As String, _
    Optional FileAttachment As String) As Boolean
On Error GoTo CdoSend_Err

' This example use late binding, you don't have to set a reference
' You must be online when you run the sub
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
 
        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "[b][red]yourmailserver.com[/red][/b]"
            .Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
            .Update
        End With
 
    With iMsg
        Set .Configuration = iConf
        
        .To = MailTo
        .CC = CC
        .BCC = BCC
        .FROM = MailFrom
        
        .Subject = Subject
        .TextBody = MessageText

        
        If Len(FileAttachment & "") > 0 Then
            
            '## Last make sure the file actually exists and send it!:
            Dim fso
            Set fso = CreateObject("Scripting.FileSystemObject")
            If fso.FileExists(FileAttachment) Then
                .AddAttachment FileAttachment
            Else
                'otherwise return that the send failed and exit function:
                Debug.Print "[CdoSend.Error]=> File attachment path does not exist, quitting..."
                CdoSend = False
                Exit Function
            End If
        
        End If
    
        '## Send zee message! ##
        .sEnd
    
    End With

    Set fso = Nothing
    Set iMsg = Nothing
    Set iConf = Nothing
    
    CdoSend = True

CdoSend_Exit:
    Exit Function
    
CdoSend_Err:
    Debug.Print "[CdoSend.Error(" & Err.Number & ")]=> " & Err.Description
    CdoSend = False
    Resume CdoSend_Exit
End Function

[red]
Make sure that your mail server is correctly specified.[/red]


Skip,

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

 

[wbow]

Skip,
Can't thank you enough for your efforts. Will load up a test next week and trial it.

Thanks again.
WBOW

 

[wbow]

Hi Skip,
As promised incorporated the code into the Private SubCommanButton1() and ran it.

Debug found a few anomalies, but those I fixed. The only issue I cannot address is that the code puts dates in the alert_sent columns for every qualification date it finds.
E.G In the tset copy of the spreadsheet in cell I2 I have a CCN1 qualification date of 16/03/13 which is not less than [S2 =Now()] or less than [T2 =S2+90] yet a date is placed in the corresponding CCN1_Alert_Sent cell. Indicating an e-mail has been sent.
It is as if the test for Less Than [S2] or [T2] is being ignored.

 

[SkipVought]

Please post YOUR code for this segment of MY code

            For i = iCol1 To iCol2 - 1 Step 2
                If r.Offset(0, i - 1).Value > [YOUR_DATE_REF] Then
                    bSend = True
                    sSend = sSend & .Cells(1, i).Value & vbTab & Format(r.Offset(0, i - 1).Value, "yyyy/mm/dd") & vbLf
                    r.Offset(0, i).Value = Now
                End If
            Next

Skip,

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

 

[wbow]

Hi Skip,
My code for the section described as follows
For i = iCol1 To iCol2 - 1 Step 2
If r.Offset(0, i - 1).Value > [S2] Or [T2] Then
bSend = True
sSend = sSend & .Cells(1, i).Value & vbTab & Format(r.Offset(0, i - 1).Value, "dd/mm/yyyy") & vbLf
r.Offset(0, i).Value = Now
End If
Next

Initially thought the ">" symbol should have been "<" for date comparison.

Many thanks,

 

[SkipVought]

You must code EACH or like...

If A > B OR A > C Then

But is that not the same as saying...

If A > Application.Min(B, C) Then

So if S2 is NOW and T2 is S2 + 90, then this would be sufficient...

If r.Offset(0, i - 1).Value > [S2] Then

Skip,

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

 

[PHV]

Skip, I'd test for <= and furthermore I'd test for the already sent check ...