How to send email based on criterias of the spreadsheet

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
I would like to send an email for each person in a table based on the confirmation column.
Meaning if is True, that person will receive the email. Otherwise not.

I would like also to choose to send to everyone (Confirmed as True) or to send individually.

NameEmailTypeConfirm
John Doejohn@mail.comTO:True
Marymary@mail.comCC:False
Anneanne@mail.comTO:True

<tbody>
</tbody>







Ps.: The column type defines the recipient, if will be "To" or "CC" Carbon Copy.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
number of routines out there for the actual email, but taking your example a for next loop would work, something like

For n = 1 to LastRow STEP 1
If Col D = True then capture the email address call the email routine and set with the email address and send
DoEvents
next n

basically you loop down the column with True in it and then do some action if the matches, the DoEvents just gives the programme time to process, you'll also need to have a routine to ensure your email programme is open, before you first send otherwise it will probably fail
 
Upvote 0
And how about the Column C where is define if the email recipient will be To or CC? These will be parameters for the email routine right???
Also I need to define on my code if the email will be sent individually or for everyone.

Understood?
 
Last edited:
Upvote 0
i thought c was excluded as you already had false, if they need to be assessed then a simple AND could be used to identify rows to by pass
 
Upvote 0
Below the code using Dictionary where I can store the CC and To recipients.

Code:
Sub dicionario()       

    Dim ws As Worksheet
    Dim rg As Range
    Dim strTo As String
    Dim strCC As String

    Dim objDic As New Scripting.Dictionary


    Set ws = ThisWorkbook.Worksheets("MailList")
    Set rg = ws.Range("A1").CurrentRegion


    For i = 1 To rg.Rows.Count Step 1
        If rg(i, 4) = True Then
            objDic.Add rg(i, 2), rg(i, 3)
        
            DoEvents
        End If
        Next i


    For Each Key In objDic.Keys
        If objDic(Key) = "To" Then
            strTo = strTo & ";"
        Else
            strCC = strCC & ";"
        End If
    Next Key


    'strSubject = EmailSubject


    Set objDic = Nothing
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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