emailing from Excell based on cell date

Microsoft Office 2003 basic edition
December 24, 2009 at 11:55:07
Specs: Windows XP

I have a new query.

I want to be able to send an email alert from excel to an email address based on date criteria:


if Cell , say, K14 (a date field) is older than todays date ( defined by a cell , say Q14 (=now() ) and cell S14 is =0 then the email gets sent and a flag in cell S14 is set to 1.

It may be that I modify the code later to send a second reminder when the condition is met as date is date+7 and S14 code is 1, followed by changing the flag in S14 to 2.

I have seen some codes on the internet that will just send the excel sheet.
Also they use Outlook, whereas the email application we use in the office is Lotus Notes. Can anyone help with the code and the server side of emailing?

Thanks and Merry Christmas to all.


See More: emailing from Excell based on cell date

Report •

December 25, 2009 at 09:34:55

Here is a suggestion for an automated e-mail from Excel.

To automate this, the code is placed in the Worksheet_Change event of the worksheet with dates in cells K14 etc.

The aim is that when there is a change on the worksheet, this code is automatically run (its part of Excel's event handling). The code tests if the changed cell is the one containing the latest update date. So this code only runs if the update date is changed.

The code tests that the last e-mail date was earlier than today to avoid repeat e-mails. The date the new e-mail is sent is added after the e-mail has been sent.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
'only run this code if K14 has changed (the update date)
Set rngUpdate = Application.Intersect(Target, Range("K14"))
If Not rngUpdate Is Nothing Then
    'But only send if last e-mail date is earlier than today
    If Range("S14").Value < Range("Q14").Value Then
        'create the e-mail object
        Set objEmail = CreateObject("CDO.Message")
        'set e-mail SMTP configuration
        With objEmail
            With .Configuration.Fields
                .Item("") _
                    = "XXXXX"
                .Item("") _
                    = "30"
                .Item("") _
                    = "2"
                .Item("") _
                    = "25"
                .Item("") _
                    = "XXXXX"
                .Item("") _
                    = "1"
                .Item("") _
                    = "XXXXX"
                .Item("") _
                    = "XXXXX"
            End With
            'create e-mail message
            .To = "XXXXX"
            .From = "XXXXX"
            .Subject = "ExcelTest"
            .TextBody = Range("A1").Text
            'optional add attachment
            '.AddAttachment ()
            'send e-mail
        End With
        'remove the e-mail object
        Set objEmail = Nothing
        'e-mail sent so flag e-mail sent with today's date
        Range("S14") = Today
    End If
End If
End Sub

To enter this code, right click on the worksheet name tab and select 'View code'
Paste the code into the code window.
Then click inside the code and you should see Worksheet and Change at the top of the page.

The e-mail part of the code requires several items to be completed. The text on each line identifies what has to be completed. The Authentication line and the two following it may not be required if your e-mail provider doesn't require outgoing authentication.
It is possible that the smtpserverport line will need to be changed, but 25 is the standard smtp port.

I put the contents of cell A1 as the text of the message, but obviously you can change this.
I have commented out the Add attachment line. I have never tried adding an attachment, so if you need to use this try a Google to get more details.

Hope this helps


Report •

January 7, 2010 at 07:51:48
Thanks Humar.

I have been looking at the code, and now think that it will not work for me.... I don't think I have explained clearly what I want to do. ... my fault!

The excell file I am using is a growing file. Anytime I have to arrange a print of some document, the title, code (A) and the reminder date (D) will be added as well as some other paramaters to the worksheet.

I need to code to look at each entry (row) and decide if the criteria is met to send an email. The email subject will make use of text from A(row), B(Row) and C(Row)

The columns are

A: Title of document B:product _id C: Print Quantity D: Reminder Date E: Date Re-print arranged For

The Rows will then contain the data.

I need the code to check each row and see if D(row) reminder date = today and and E(Row) is empty

Additionally, to avoid sending an email every day I previously suggested a flag, but may be that is not necessary if the system after sending the email changes the D(Row) to todays's date+1 week.

I am sorry I was not clear before.

Report •

January 18, 2010 at 03:45:40
OK. I have made some progress with this on my own:

Here is the code:

------------------------------------------ oooo -------------------------------
Sub checkdate()

Dim myRange As String
Dim Ws As Worksheet
Dim oRow As Long
Dim Mailsubj1 As String
Dim Mailsubj2 As String

Set Ws = ThisWorkbook.Worksheets("RePrintSchedule")
oRow = Ws.UsedRange.Rows.Count + 1


For i = 2 To oRow

If Range("D" & (i)).Value = Range("H1").Value Then

Mailsubj1 = Range("A" & (i)).Value
Mailsubj2 = Range("B" & (i)).Value

'MsgBox Mailsubj2 & ": " & Mailsubj1 & " //eom"

Application.Run "SendNotesMail"
End If

End Sub

Sub SendNotesMail()
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Dim myArr As Variant, i As Long

Dim Mailsubj1 As String
Dim Mailsubj2 As String

Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
Else: Maildb.OpenMail
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "emailname" 'Nickname or full address
'MailDoc.CopyTo = Whomever
'MailDoc.BlindCopyTo = Whomever

MsgBox Mailsubj2 & ": " & Mailsubj1 & " //eom"

MailDoc.Subject = Mailsubj2 & ": " & Mailsubj1
'myArr = Range([a2], [a65536].End(3))
'For i = LBound(myArr) To UBound(myArr)
'myArr(i) = Right(myArr(i), Len(myArr(i)) - 1)
MailDoc.Body = "Put mail message body here ....."
'Replace("As a result of a review of your AWP collections that" & _
' "I have carried out,@@I have asked Leisure Link to replace your ????? " & _
' "AWP.@@@@I or your Leisure Link Business Account Manager will try" & _
' "@@to phone you to discuss this within the next couple of days." & _
' "@@However if you have any immediate comments,@@please do not " & _
' "hesitate to contact either of us." & _
' Join(Application.Transpose(myArr), "@") & _
' "@@With kind regards", "@", vbCrLf)
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo Audi
Call MailDoc.Send(False)
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
Exit Sub
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
End Sub

-------------------------------------- ooo ----------------------------------

I should say that the Sub SendNotesMail() is someone elses work whose name I can't remember now. It was taken from a forum.

The code as it is works and sends emails to a valid email address for each row data that matches query. But now I am having problems with passing teh right parameters to Sub SendNotesMail() each time, namely Mailsubj1 and Mailsubj2. Can someone help wit this?

Once I sort this out, I might need help to change the date in each row D(i) that matches the criteria, to Todays Date + 3 to prevent further emails being sent. And the criteria should also check that row E(i) is empty. ie

If Range("D" & (i)).Value = Range("H1").Value and range ("E" & (i)) isEmpty

But I think the syntax is not altogether correct here.

Many thanks to anyone taking the time to look at this for me.


Report •

Related Solutions

January 25, 2010 at 08:34:00
Is there no one else that can help with this query?

How does one pass values from one sub routine to another? I have this almost working except that it will not pass parameter values from the first subroutine to the emailing subroutine.

I am desperate to get this working.... please help if you can.


Report •

January 25, 2010 at 09:11:50

To pass values to a subroutine, include the variables in the subroutine heading, rather like dim statements, identifying what they are:

Sub MySub(intn As Integer, strText As String)
End Sub

Now when you type Call MySub( in the main program
you will get prompts for the variable that the sub requires.

You don't need to use the same names in the main program, so Call MySub(n, strTextForSub) is OK.
In the subroutine use the variables intCount and strText as these will have the values of n and strTextForSub


Report •

February 8, 2010 at 04:24:06
Thanks Humar.

I have now sorted this out. Making some small minor improvement to teh code now, but the gap in major lack of knowledge in this area has now been bridged.

Your contributiosn are much appreciated.


Report •

February 8, 2010 at 05:59:47

Pleased to hear you got it working.


Report •

Ask Question