Sending email notification from excel

[Talat]

Hi,

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:

Basically,

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.

Talât.

[msanchez]

This can be dependent on which version of Windows you are running, but you can send mail from VB scripting editions using CDONTS.

http://msdn.microsoft.com/en-us/libr...CHG.10%29.aspx

Good luck,

[Talat]

Anyone feeling brave to tackle this one?

My first post was a bit vague, but below is what I really want to be doing. I think I am OK on the emailing side of things, but I need help on getting the code to check each row of data and decide if an email needs to be sent.


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.

Thanks.

Talât

[Talat]

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 'row number on BatchSheet
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
Next

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

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@somewhere.com" '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)
'Next
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
Audi:
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.

Talât

[Talat]

I am still struggling with this. is there no one in this forum who can help, or say that it cannot be done?

[AM003295]

are you still having trouble with this ???

[Talat]

Not anymore. I have sorted this out with contributions from another help forum. I can put the solution up here if it would help anyone else.

Thanks for asking.

T.

[Hack]

I can put the solution up here if it would help anyone else.

Yes, please do. Posting your solutions will always benefit someone.

Thanks.

[everzcool4]

Yes, please do. Posting your solutions will always benefit someone.

Thanks.

it will be very useful to me. so please post it

[shorttony]

Hi Talat

this is exactly what I want to do with my spread sheet could you please post the final code. Unfortunately I am a dunce when it comes to applying these codes and making them run... so is there any chance you could give me a step by step idiots guide in placing it in my spread sheet and making it work. Thank you in advance

[Talat]

Hi

The solution below works for me, but it depends on your email client. This one uses Lotus Notes, which may not be the one you are using as the email client.

There is a very good article on this by Ron de Bruin which I suggest you read here http://www.rondebruin.nl/win/section1.htm

The code I used which work for me is below

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

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 = "emailaddress@somewhere3.com"

MailDoc.CopyTo = "email address:somwhere2.com"

MailDoc.BlindCopyTo = "email address@somewhere.com"

MailDoc.Subject = "ALERT: " & MailSubj2 & " :  " & MailSubj1

MailDoc.Body = "Please arrange a Section Reprint or Request team copies asap for the above document and update excell sheet with the arranged reprint date. " & " Sent by M&S Team - Admin Automailer."

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
Audi:
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
End Sub

Hope this helps.

Talât