Send email based on the status of a field

Grand

Board Regular
Joined
May 11, 2017
Messages
52
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:

A
B
C
D
Open
12-02-2018
Do this
John
Closed
10-01-2018
Do that
James
Closed
09-03-2018
Do not do this
Jim
Open
12-02-2018
Maybe do this
Jack
Open
10-01-2018
Look at that
Jim
Open
20-12-2017
seat over there
Jessi
Open
25-04-2018
Wash my car
Jim

<tbody>
</tbody>


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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.

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.
 
Upvote 0
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....
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
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! (y)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top