Send email based on the status of a field

[Grand]

Hi,
I have been unsuccessfully trying to do the following:
Background: I have an Excel list with some data in 4 columns. Colum A is the status, columns B contains dates, column C contains some activities (some text) and column D contains some names:

[TABLE="width: 397"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Open
[/TD]
[TD]12-02-2018
[/TD]
[TD]Do this
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]Closed
[/TD]
[TD]10-01-2018
[/TD]
[TD]Do that
[/TD]
[TD]James
[/TD]
[/TR]
[TR]
[TD]Closed
[/TD]
[TD]09-03-2018
[/TD]
[TD]Do not do this
[/TD]
[TD]Jim
[/TD]
[/TR]
[TR]
[TD]Open
[/TD]
[TD]12-02-2018
[/TD]
[TD]Maybe do this
[/TD]
[TD]Jack
[/TD]
[/TR]
[TR]
[TD]Open
[/TD]
[TD]10-01-2018
[/TD]
[TD]Look at that
[/TD]
[TD]Jim
[/TD]
[/TR]
[TR]
[TD]Open
[/TD]
[TD]20-12-2017
[/TD]
[TD]seat over there
[/TD]
[TD]Jessi
[/TD]
[/TR]
[TR]
[TD]Open
[/TD]
[TD]25-04-2018
[/TD]
[TD]Wash my car
[/TD]
[TD]Jim
[/TD]
[/TR]
</tbody>[/TABLE]


What I need in general is that we click a button, and upon clicking some emails will be sent out using MS Outlook. In detail; the code shall search in column “A” and find instances of “Open” and then it should look in column D for those who have an open case. When that is found, anybody who has an open case shall receive an email. The email body then shall contain what is in column C for corresponding recipient (column D contains the recipients).
The tricky part (and important part) could be that if there are more than one open case (e.g. 3 cases) for e.g. Jim, then the program shall send only one email (and not 3 emails) to Jim. That email of course shall contain the content of column C for those rows that are of interest (open). An example for Jim based on the above table could look like:

******************************************
Dear Jim,
Please address the following:
1-look at that
2-Wash my car
Thanks Jim.
****************************************
I woudl really appriciate any help. If it is difficult to make it in one row, we maybe can do it in steps
Thanks in advance

 

[RobertSF]

Hi! This is not trivial because you have to open Outlook from Excel. Here's a page with info about that.
https://www.rondebruin.nl/win/s1/outlook/mail.htm

Also, when you click the button, the code has to sort the data by Column D and Column A so that all of Jim's open tasks are grouped together. I'm not like a VBA genius or anything, but I'll try to post some code after work.

 

[Grand]

Hi.
Thank you for the information. I cheerfully wait for any help

 

[RobertSF]

Alright, sorry about the delay. Got crunched at work. Here's the code. It assumes that your Excel list is the only thing on that particular sheet and that the list is a single, uninterrupted range. It also requires email addresses in column D, but I'm sure you had figured that. You can have an email address column plus a name column if you want to personalize your emails, but that would require more code.

Option Explicit

'Go to Tools, References, and add Microsoft Outlook ? Object Library
'where ? is a version number depending on your Excel version

'Enumerating values makes code easier to read and understand
Enum Cols
    Status = 1
    Date = 2
    Task = 3
    Person = 4
End Enum

'This is the first data row, allowing for one header row
Const FirstRow = 2

Sub Notify()

    Dim rng As Range
    
    'First define and sort the range to group the opens together by person
    Set rng = ThisWorkbook.Sheets(1).UsedRange
    rng.Sort _
        Header:=xlYes, _
        Key1:=rng.Columns(Cols.Status), Order1:=xlDescending, _
        Key2:=rng.Columns(Cols.Person), Order2:=xlAscending, _
        Key3:=rng.Columns(Cols.Date), Order3:=xlAscending
        
    Dim LastRow As Integer

    'find the last row of open tasks
    LastRow = rng.Columns(1).Find("closed", , , , , False).Row - 1
    
    Dim appOutlook As Outlook.Application
    Dim newMail As Outlook.MailItem
    
    'get Outlook ready
    Set appOutlook = New Outlook.Application
    
    Dim curPerson As String
    Dim tasks As String
    Dim ndx As Integer
    
    'we prime the pump with the first person
    curPerson = rng.Rows(FirstRow).Cells(Cols.Person).Value

   'loop through the rows
    For ndx = FirstRow To LastRow
        tasks = tasks & rng.Rows(ndx).Cells(Cols.Task).Value & vbNewLine
        'is the next row for a different person?
        If rng.Rows(ndx + 1).Cells(Cols.Person).Value <> curPerson Then
            'yes, so lets send the mail to this person
            Set newMail = appOutlook.CreateItem(olMailItem)
            newMail.Recipients.Add curPerson
            newMail.Subject = "Your to-do list"
            newMail.Body = _
                "Hi, please address the following:" & vbNewLine & _
                tasks & _
                "Thanks!"
            newMail.Send
            'and we move to the next person
            curPerson = rng.Rows(ndx + 1).Cells(Cols.Person).Value
            tasks = ""
        End If
    Next ndx
    
End Sub

I don't know what your level of knowledge is, but I commented the code hoping to make it more clear. Let me know.

 

[Grand]

Thank you very much. You know that you don't owe me anything, right?
I will give it a try tomorrow and let you now.
I have some basic knowledge and generally copy paste codes from here and there to come by your code seems readable to human specially with those comments.
And you are right about the email addresses. In my case column D actually contains initials where I will append "@myemail.com" (which is the same for all recipients) to them inside the code. There might be some empty rows in between but we can fix that later.
I am so excited....

 

[Grand]

Hi,
I have tested your code and it works just fine
I noticed that you filter my column A. Can the filter be undone after this macro has run its course? If not, is fine too.
Any ways, thanks a lot

 

[RobertSF]

Thanks for letting me off the hook, but I did say I'd post some code after work, so I did owe you. Not to get all dramatic, but it doesn't matter if others can't hold us to our promises. We know what we promised!

Anyway, I'm glad you're not flying blind. I'm sure you can change the code to create the email addresses.

I'm glad the code worked, but I actually don't filter the data. I only sort it. Do you want a way to return it to its previous order? Unfortunately, things macros do cannot be undone with Ctrl-Z, but not all is lost. If you want to return this or any data back to the original order after sorting it, create a column and assign every row a sequence number according to its unsorted order. Then, to return the data to its original order, sort it by that column.
You're very welcome, and I hope you keep coming back. There are questions you can answer too!

 

[Grand]

Yes, thank you; I know what you mean.
I have changed a few things and it is running as expected. I have a few questions and extra wishes which I will formulate later. Meanwhile I thought that we could copy the content into a new sheet, do the stuff on it and then delete that sheet. It is not the smartest way but then the original data is untouched and unsorted. Does that make sense?
Regards.