Sending email notification from excel


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Sending email notification from excel

  1. #1
    Join Date
    Aug 2009
    Posts
    37

    Sending email notification from excel

    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.

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego
    Posts
    202
    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,
    Michael Sanchez
    Managing Technical Editor
    Forum Moderator
    FreeVBCode.com

  3. #3
    Join Date
    Aug 2009
    Posts
    37
    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
    Last edited by Talat; 01-16-2010 at 01:06 PM.

  4. #4
    Join Date
    Aug 2009
    Posts
    37
    OK. I have made some progress with this on my own:

    Here is the code:

    ------------------------------------------ oooo -------------------------------
    Code:
    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
    Last edited by Hack; 01-19-2010 at 08:18 AM. Reason: Added Code Tags

  5. #5
    Join Date
    Aug 2009
    Posts
    37

    Angry

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

  6. #6
    Join Date
    Nov 2004
    Posts
    293
    are you still having trouble with this ???

  7. #7
    Join Date
    Aug 2009
    Posts
    37
    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.

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Talat View Post
    I can put the solution up here if it would help anyone else.
    Yes, please do. Posting your solutions will always benefit someone.

    Thanks.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  9. #9
    Join Date
    Dec 2009
    Posts
    13
    Quote Originally Posted by Hack View Post
    Yes, please do. Posting your solutions will always benefit someone.

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

  10. #10
    Join Date
    Sep 2013
    Posts
    1
    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

  11. #11
    Join Date
    Aug 2009
    Posts
    37
    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


    Code:
    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

Similar Threads

  1. Desperate help in sending an email
    By Talty09 in forum .NET
    Replies: 2
    Last Post: 04-06-2009, 05:03 PM
  2. Email notification service architecture.
    By v_ganeshraju in forum .NET
    Replies: 0
    Last Post: 09-26-2007, 04:05 PM
  3. Sending email in html format from database
    By hatakastation in forum Database
    Replies: 2
    Last Post: 07-05-2007, 10:22 PM
  4. Replies: 0
    Last Post: 12-14-2006, 01:40 AM
  5. Sending an email not working?
    By geeta in forum .NET
    Replies: 2
    Last Post: 09-27-2001, 10:09 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center