1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Emails from Excel using various dates

Discussion in 'Business Applications' started by bobbynova, Apr 24, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Hi all,
    I've spent a good few days reading posts on this site it seems between everyone here there a whole host of knowledge!

    I'm here as I am having trouble with an excel sheet and I'm unsure how to solve it.
    I am using excel 2007 on my laptop.

    I've searched both this site and google for a few days now but I haven't found anything that seems to be what I need.
    There is a thread on here that was helped by Keebellah and that is the closest I can find to what I am trying to do however I can't seem to edit the code in such a way that gets it working in my sheet.

    Let me explain what I am trying to do.

    My sheet is a training tracker which has a column of names of employees and some dates.
    Specifically the dates are: Ideal WK4 date, Ideal WK8 date, Ideal WK12 date. These all have dates inserted.
    There is also a column next to each of these that either says completed, or is empty.

    I am trying to get Excel to email me when one of these dates is 7 days away or less, unless the column says completed. Then I don't want an email.
    I want the email to basically say:
    EMPLOYEE NAME is due for their WK? meeting within 7 days. Please schedule this in.

    The employee name and WK4/8/12 should be pulled from the sheet.

    Logically speaking, this is what I am looking for...
    IF M(ideal date column wk4) = less than 7 days, send email with row data. If O=Complete don't send

    IF Q(ideal date column wk8) = less than 7 days, send email with row data. If S=Complete don't send

    IF X(ideal date column wk4) = less than 7 days, send email with row data. If Z=Complete don't send

    O, S and Z are all columns that either say Completed or are blank. If they say completed thereis no need for the email to send as obviously its already done.

    I have a code that literally sends the whole sheet as an attachment which has to be done manually, I have to go in to excel and open up the code and press run, it then sends to my email address the predefined message and the entire sheet. Close but not what I'm looking for.

    I haven't seen many posts about reading multiple date columns for this during my search so I hope someone can help.
    I'm also speaking to an excel guru via email to see if he can help. (I think he is on this site), I am hoping that I will be able to get this sorted with some help.

    Look forward to any assistance.

    Regards
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Hi, welcome to the forum.
    What you are asking has already been doen in one or other way. I've helped with some of these.
    I think it will make it easier to help if you can attach a asample file with dummy data.
     
  3. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Sure Hans, I'll post that in a few minutes.
     
  4. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Here is the sheet with dummy data, hopefully it will explain in more detail what I posted in the first post. I have seen a post that you helped with earlier that is similar to what I need, I couldn't get the code to work myself however when I tried to edit it to suit my project. I have had a good attempt before having to post for help, I don't like to admit defeat :)

    Many thanks for your reply.
     

    Attached Files:

  5. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    I have managed to get the code you wrote for another workbook to work however only for the week 4.
    It is only part working, by that I mean it is trying to send an email regardless of if the box says complete or not. This is because I am unsure how to add that statement to the code. I am also unsure how to get it to read which update is required, be it week 4, 8 or 12 and insert that in to the subject box and also the message body.

    It is opening up lots of emails when I run the macro, I have to manually press send on all of these. If we could make that an automatic progress it would be ideal. I have pasted the code below, its an adaption of what you used in a previous workbook but with my cells inserted.

    Option Explicit


    Public Sub CheckAndSendMail()
    Dim lRow As Long
    Dim lstRow As Long
    Dim toDate As Date
    Dim toList As String
    Dim ccList As String
    Dim bccList As String
    Dim eSubject As String
    Dim EBody As String
    Dim ws As Worksheet

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

    Set ws = Sheets(1)
    ws.Select

    lstRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "L").End(xlUp).Row)

    For lRow = 3 To lstRow
    toDate = Cells(lRow, "M").Value
    'toDate = Replace(Cells(lRow, "M"), ".", "/")
    If Left(Cells(lRow, "M"), 4) <> "Mail" And toDate - Date <= 7 Then

    toList = Cells(lRow, "AG") 'gets the recipient from col D
    eSubject = "Induction Update " & Cells(lRow, "E") & " " & Cells(lRow, "D") & " is due on " & Cells(lRow, "M").Value
    EBody = "Dear " & Cells(lRow, "E") & vbCrLf & vbCrLf & "An update is due. Please book this with your Manager."

    MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList


    End If
    Next lRow

    ActiveWorkbook.Save

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

    Function MailData(msgSubject As String, msgBody As String, Sendto As String, _
    Optional CCto As String, Optional BCCto As String, Optional fAttach As String)

    Dim app As Object, Itm As Variant
    Set app = CreateObject("Outlook.Application")
    Set Itm = app.CreateItem(0)
    With Itm
    .Subject = msgSubject
    .To = Sendto
    If Not IsMissing(CCto) Then .Cc = CCto
    If Len(Trim(BCCto)) > 0 Then
    .Bcc = BCCto
    End If
    .Body = msgBody & Chr(13) & Chr(13)
    .BodyFormat = 1 '1=Plain text, 2=HTML 3=RichText -- ISSUE: this does not keep HTML formatting -- converts all text
    'On Error Resume Next
    If Len(Trim(fAttach)) > 0 Then .Attachments.Add (fAttach) ' Must be complete path'and filename if you require an attachment to be included
    'Err.Clear
    'On Error GoTo 0
    .Save ' This property is used when you want to saves mail to the Concept folder
    .Display ' This property is used when you want to display before sending
    ' .Send ' This property is used if you want to send without verification
    End With
    Set app = Nothing
    Set Itm = Nothing
    End Function


    Sub testing()
    Dim toList As String
    Dim ccList As String
    Dim bccList As String
    Dim eSubject As String
    Dim EBody As String
    toList = "[email protected]"
    eSubject = "test mail sent " & Now()
    EBody = "Hello there," & vbNewLine & _
    "This is a test message." & vbNewLine & vbNewLine & _
    "Regards," & vbNewLine & vbNewLine & _
    "Dan"
    MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList

    End Sub
     
  6. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Have spent the morning working on this today and have ended up no further forward lol. Thinking about this, Is the sheet going to need a column to indicate if the email has been sent already so it doesn't send the same email multiple times?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Yes, you will need an extra column to chekc sent and i suggest a date sent too.
    It's a pity you don't attach a macro embedded file.
    I'll have to copy and psste the code you've used. :eek:
     
  8. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Ok,
    Ideally those columns could be added at the very end so it doesn't break all of the conditional formatting, there is quite a bit!

    I have attached the macro enabled file to this post...I think. Hopefully the file attached makes it easier for you to see what I have done without you having to copy and paste?
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I'll take a look and get back to you.
     
  10. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Many thanks! :)
     
  11. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    Here is a file with the columns we spoke about added.
    Macro should be included too.
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I knew the vba code was familiar, it looks like the code I also used and modified to make it better to use for all circumstances.
    I assume you're using Outlook?
    I see the added columns and will work with the last file you attached.
     
  13. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    It is the same code you used for another project on this forum, very helpful!
    I am using outlook, that is correct. Many thanks Hans.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Since you added the columns, the original question and columns have changed just to make sure I'm getting it rigyt and this based in file DUMMY2:

    (1) M < 7 days and O <> Complete then send mail
    (2) S < 7days and W <> Complete then send mail
    (3) AB < 7 days and AF <> Complete then send mail

    You also say mail with row data, the entire row? what data exactly.
     
  15. bobbynova

    bobbynova Thread Starter

    Joined:
    Apr 24, 2015
    Messages:
    33
    That looks correct to me however I think O should be Q? As below...

    (1) M < 7 days and Q <> Complete then send mail
    (2) S < 7days and W <> Complete then send mail
    (3) AB < 7 days and AF <> Complete then send mail

    To be honest, if it sends the data as it was when I was testing then the row won't need to be sent.
    If the email will tell if the meeting is for a week 4, week 8 or week 12 then that is enough.

    The way I modified the code it included:
    Name, employee ID and date the meeting is due in the subject box, The message body included the name, and just that an update is due. If that could also include the type of update (wk4, 8 or 12) then it would be perfect and no need to send the whole row from the sheet.

    The original idea was as I have just wrote but also to attach the 1 row of the sheet to the email, so if Colleague 1 has their week 4 meeting it would send an email as above and also attach the entire row from the sheet for COLLEAGUE 1. I have however realised this may be un-needed as the email will say all it needs to without the attachment of the row.

    I hope that makes sense.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1147158

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice