VBA Excel Outlook Email Filter/Sort no Duplicates

A

Anvo88

New Member
Joined
Jul 14, 2014
Messages
5
Hi Everyone,

I need a little assistance with the VBA code for sending emails from Excel via outlook.
I am still learning VBA coding and I would like to filter or sort values before sending out the automated email without duplicating the values.

For example:-
The email should go out when value in the Cell in the Due days column reaches 3 - In this Example, tom has 3 days due to submit his report on 4 instances in the table. So instead of TOM getting 4 different or separate emails, I want tom to get only One email with all 4 line items.
So the email that tom would get would look something like this -

[TABLE="class: cms_table, width: 463"]
<tbody>[TR]
[TD]Sales Person[/TD]
[TD]Mail Send Yes or No[/TD]
[TD]Due days[/TD]
[TD]Mail address[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table, width: 463"]
<tbody>[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
</tbody>[/TABLE]


The same would apply for Ron and Debra . Is this possible in VBA ?
I would appreciate any kind of help for this issue.
Thank you.
Sample table.

[TABLE="class: cms_table, width: 463"]
<tbody>[TR]
[TD]Sales Person[/TD]
[TD]Mail Send Yes or No[/TD]
[TD]Due days[/TD]
[TD]Mail address[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]dave@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]debra@something.abc[/TD]
[/TR]
[TR]
[TD]Gord[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]gord@something.abc[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]tom@something.abc[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]debra@something.abc[/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]debra@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]5[/TD]
[TD]ron@something.abc[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]Not Sent[/TD]
[TD]3[/TD]
[TD]tom@something.abc[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Hi Anvo88,

None of this seems to difficult, but what I am missing in your example, is what the four lines in Tom's email is going to say. Your sample table is fine except for what four things are we telling Tom are due in three days. Additionally, do you expect the vba to change the "Mail Send Yes or No" column from "Not Sent" to "Sent" or something else such as a Date Sent. Some Column and row identifiers on your sample table would go a long way as well...

igold
 
Upvote 0
Hi igold,

Thank you for helping out.
The additional column that should have been there is that of the error number.
So Tom has to submit a report for each error number.
So if Tom is handling 4 Error numbers, Then Tom should get the error number from the Error number column, the due days left in a table.
Also you can change the mail send Yes or No column to the date it was sent.
The Email to tom should tell him
all the data in the table - Due days, Error Number

Does this help?

i appreciate your time and efforts .

Regards,
 
Upvote 0
If you could just supply some column letters with the data that would be great. Especially the "error number" column and what might the error number look like.

When you say "All the data on the table", is it just the due days and error numbers. Also are the headers on Row 1 and the data starts on Row 2.

Do you have a particular "Subject line" you would like to use. If you want something included in the email, now is the time to tell me.

The more info I have the easier it will be to get something useful back to you with fewer corrections.

igold
 
Upvote 0
Hi igold,

I apologize for the delayed response.
I am attaching a file with the sample data.
Please let me know if this works.

[TABLE="width: 677"]
<tbody>[TR]
[TD]Sales Person[/TD]
[TD]Mail Send Yes or No[/TD]
[TD]Error Report #[/TD]
[TD]Due Days[/TD]
[TD]Mail address[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]AB123456[/TD]
[TD]5[/TD]
[TD]tom@Something.com[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]Not Sent[/TD]
[TD]AB123457[/TD]
[TD]5[/TD]
[TD]dave@Something.com [/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]AB123458[/TD]
[TD]5[/TD]
[TD]ron@Something.com[/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]Not Sent[/TD]
[TD]AB123459[/TD]
[TD]5[/TD]
[TD]debra@Something.com [/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]AB123460[/TD]
[TD]5[/TD]
[TD]tom@Something.com[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]AB123461[/TD]
[TD]5[/TD]
[TD]tom@Something.com [/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]Not Sent[/TD]
[TD]XY123456[/TD]
[TD]5[/TD]
[TD]debra@Something.com[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]XY123457[/TD]
[TD]5[/TD]
[TD]tom@Something.com [/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]XY123458[/TD]
[TD]5[/TD]
[TD]tom@Something.com[/TD]
[/TR]
[TR]
[TD]Gord[/TD]
[TD]Not Sent[/TD]
[TD]XY123459[/TD]
[TD]5[/TD]
[TD]gord@Something.com [/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]PQ123456[/TD]
[TD]5[/TD]
[TD]tom@Something.com[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]PQ123457[/TD]
[TD]5[/TD]
[TD]tom@Something.com [/TD]
[/TR]
[TR]
[TD]Ron[/TD]
[TD]Not Sent[/TD]
[TD]PQ123458[/TD]
[TD]5[/TD]
[TD]ron@Something.com[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]PQ123459[/TD]
[TD]5[/TD]
[TD]tom@Something.com [/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Not Sent[/TD]
[TD]PQ123460[/TD]
[TD]5[/TD]
[TD]tom@Something.com [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 825"]
<tbody>[TR]
[TD="colspan: 5"]TOM should get one One email with the table below highlighting all of his error reports due.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 370"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Tom[/TD]
[TD]AB123456[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]AB123460[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]AB123461[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]XY123457[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]XY123458[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]PQ123456[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]PQ123457[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]PQ123459[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]PQ123460[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 422"]
<tbody>[TR]
[TD]Subject Line : Follow up Required[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes there are headers on row 1 and the data starts from row 2. .. Would this also work if there were drop down lists? for the names of the employees ?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thank you :-)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Hi Avno88,

You kind of changed things up on me here so I improvised a little. Your initial post said your "Due Days" = 3 but in the last table you sent me, everyone had a 5 which would not have generated a email as per your first post. I changed a couple of the fives to threes to get some action.

Additionally since you did not tell me which columns to find the data, this code assumes that the SalesPerson Header is in cell A1 through to the Email address Header in E1.

As long as there is a value in the cells with a drop down list, I don't see a problem (famous last words).

Code: 
Sub eml2()

    Dim lRow As Long, nCt As Long
    Dim strbody As String, strTo As String, SP As String, _
    ER As String, DD As String, sen As String
    Dim Due As Integer, i As Integer

    sen = Date
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    Due = 3
    
    For nCt = 2 To lRow
    strTo = ""
        If Cells(nCt, 4).Value = 3 Then
            strTo = Trim(Cells(nCt, 5))
            GoTo sem
        End If
gtg:

    Next
    End
    
sem:
    For i = nCt To lRow
        If strTo <> Trim(Cells(i, 5)) Then GoTo NextName
        If strTo = Trim(Cells(i, 5)) And Cells(i, 4).Value <> 3 Then GoTo NextName
        If Cells(i, 2) <> "Not Sent" Then GoTo NextName
        SP = Cells(i, 1)
        ER = Cells(i, 3)
        DD = Cells(i, 4)
        Cells(i, 2) = "Email sent " & sen
        strbody = strbody & SP & vbTab & ER & vbTab & DD & vbNewLine & vbNewLine
        
NextName:
    Next
    If strbody = "" Then GoTo gtg
    GoTo GenerateMail
        
GenerateMail:
    
    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With objMail
        .To = strTo
        '.cc = Range("C3").Value
        '.bcc = Range("E3").Value
        .Subject = "Follow Up Required"
        .Body = strbody
        '.Attachments.Add rngAttach.Value
        .Display                            'Instead of .Display, you can use .Send to send the email _
                                                or .Save to save a copy in the drafts folder
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    strbody = ""
    strTo = ""
    'Set rngAttach = Nothing
    GoTo gtg

End Sub

See if this works as requested...


igold
 
Upvote 0
Hi,

For some reason when I changed the values of all the cells in the due days column for all the employees,
It generated an email with the details for all the Employees and not just an email for Tom with his stuff and for Debra with her stuff.
Each Employee should get an email with the email body having error report #'s only pertaining to them .

And will this work when the values go to zero or '-1' or '-2' ?
 
Upvote 0
It should only trigger an email if the Due days = 3. It could be changed to 3 or less or perhaps we could add an option where at the beginning you input how many days you want to trigger on.

Could you post a small sample of the data that you say it is misfiring on.

igold
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Control text formatting (e.g. font, color, etc.) when using Excel VBA to automate emails via Outlook
Replies
2
Views
263
Roland Hoelscher
R
F
  • Question Question
VBA Send a formatted mail without Outlook
Replies
4
Views
679
Forsberg
F
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
126
mumps
mumps
L
  • Question Question
Skip Sending an Email If Status is Yes (already sent)
Replies
3
Views
219
Trebor76
T
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
265
cokeoverpepsi123
C

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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