Filter an email list by two fields before send email from Excel

Let’s take the frustration out of user forms

Become an Affiliate and earn 25% on Course Sales

For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting

I am able to provide online help on your computer at a reasonable rate.

I use a Blue condenser Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

code
Sub searchandcopy()
Dim reportsheet As Worksheet
Dim stype As String
Dim erow As Integer
Dim r As Long

Set reportsheet = Sheet2
reportsheet.Range(“A2:h1000”).ClearContents
sales = Sheet4.Range(“C2”).Value
stype = Sheet4.Range(“C5”).Value

row = 2
Do While Sheet3.Cells(row, 1).Value (does not equal) “”
Sheet3.Activate
If Sheet3.Cells(row, 9).Value = sales Then
If Sheet3.Cells(row, 6).Value = stype Then
Sheet3.Range(Cells(row, 10), Cells(row, 11)).Copy
reportsheet.Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).row + 1
reportsheet.Cells(erow, 1).PasteSpecial xlPasteFormulasAndNumberFormats

End If
End If
row = row + 1
Loop
Call Send_email
End Sub

Sub Send_email()
Dim edress As String
Dim subj As String
Dim message As String
Dim name As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim r As Long

r = 2
subj = Sheet4.Range(“F1”)
Do While Sheet2.Cells(r, 1) does not equal “”
Set outlookapp = CreateObject(“Outlook.Application”)
Set outlookmailitem = outlookapp.createitem(0)
edress = Sheet2.Cells(r, 2)
name = Sheet2.Cells(r, 1)
With outlookmailitem
.To = edress
.cc = “”
.bcc = “”
.Subject = subj
.Body = “Hello” & ” ” & name & vbCrLf & _
“Please Note that returning Parts Customs will receive 15% off of purchase price” & vbCrLf & “Best Regards”
.Display
‘.send
End With
‘clear your email address
edress = “”
r = r + 1
Loop
‘clear your fields
Set outlookapp = Nothing
Set outlookmailitem = Nothing
End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English