mail sheet triggered by date

I have uploaded a sheet, that tracks drivers lic. expiration dates, I need it to send e-mail when the drivers lic is 30 days from expiration. I have been here

http://www.rondebruin.nl/mail/folder2/mail2.htm

I read through how to copy and paste this code in my workbook but I don't understand where / how I point this at a cell so when the cell = 30 it sends the mail. I can see all the other where the email address goes, TO, CC, Subject, Body, but van't figure out the trigger part or where it is or if I have to get more code for that.

Thank you
Ken

Hi thanks for getting back, I have two different drivers Lic. I must track. I realize that makes things difficult and the I decided that if I move Columns H, I, J under E, F, G then your code would only have to look in one column for the value 30. So I have attached new spread sheet and I would like a break beteen the two groups of driver Lic but if that will screw with the code, I could place them so there is no break and just color code them to make it easier to read.

I do not have a mailsubroutine, I don't even know what it is, I am guessing a mail macro. Do I need this code? There are no e-mail address on the spreadsheet, is it easier if I do that? That is not problem some of these coloums are going to be hidden anyway, F, H. I columns will be hidden, will the code work if column is hidden? I guess I am asking what do I do next? I have not pasted your code into the sheet need to find out if the way it is now works better, I have uploded a new sheet.

Thank you
Ken

Hi Ken,

I'll answer the second paragraph first! The 'mailsubroutine' I referred to in my previous post was the mail macro you had spoken of in your original post. You had said that you had read through how to paste the code into your workbook, so I assumed it had been pasted in as a single subroutine. For the purposes of my code, I named it 'mailsubroutine'. In the page you linked to, it's defined as Mail_ActiveSheet.

You still haven't specified exactly who the spreadsheet is to be sent to - if it's just being sent to yourself, then you can specify a single email address in the mail macro. However, if it's to be sent to the individual whose licence is expiring, then you need to have the email addresses on the sheet so the macro can select the relevant address for that individual. These addresses can be placed in hidden columns, yes.

I personally don't see a need to put all the licences in a single column - the original code was fine for searching both columns, and doing it this way prevents your needing to have the same name entered twice. I assumed you'd need to mail the spreadsheet when either column had a value of 30 present.

Well I have pasted the code in but I am not doing something correctly? I have never pasted code in before in Excel so I realy don't know what I am doing.

here is the code:

Option Explicit

Sub TestFor30()

Dim DateLimit As Range
Set DateLimit = Range("F:F, I:I").Find("30", LookIn:=xlValues)
If Not DateLimit Is Nothing Then Call Mail_ActiveSheet

End Sub


Sub Mail_ActiveSheet()
'Working in 2000-2007
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 2000-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007, we exit the sub when your answer is
            'NO in the security dialog that you only see  when you copy
            'an sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With

    '    'Change all cells in the worksheet to values if you want
    '    With Destwb.Sheets(1).UsedRange
    '        .Cells.Copy
    '        .Cells.PasteSpecial xlPasteValues
    '        .Cells(1).Select
    '    End With
    '    Application.CutCopyMode = False

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " _
                 & Format(Now, "dd-mmm-yy h-mm-ss")

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add Destwb.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Sheet attached

Thank you
Ken

Bump no response