import smtplib
from openpyxl import load_workbook
wb = load_workbook('USA.xlsx')
sheet = wb.get_sheet_by_name('test')
smtpObj = smtplib.SMTP('smtp.mail.ru', 587)
smtpObj.starttls()
smtpObj.login('forproject1505@list.ru','python1505')
sheet.cell(row=1, column=4).value
name,mail,money,date,avrmon,monsent,msent=[],[],[],[],[],[],[]
for i in range(1, 2999):
    money.append(sheet.cell(row=i+1, column=4).value)
    date.append(sheet.cell(row=i+1, column=5).value)
    avrmon.append(sheet.cell(row=i+1, column=9).value)
    monsent.append(sheet.cell(row=i+1, column=10).value)
    msent.append(sheet.cell(row=i+1, column=8).value)
l=0
k=0
deposit=0
account=0
card=0
loan=0

findname=[]
findmail=[]
for j in range(0,len(money)):
    if money[l]<monsent[l] and (date[l]<2018 and msent[l]>20):
        vs1=(sheet.cell(row=l+1, column=1).value)
        vs2=(sheet.cell(row=l+1, column=2).value)
        text=("Hello, "+vs1+" we today we have a special offer to you: you can make a deposit with an increased rate of 8%")
        findname.append(sheet.cell(row=l+1, column=1).value)
        findmail.append(sheet.cell(row=l+1, column=2).value)
        smtpObj.sendmail("forproject1505@list.ru",vs2,text)
        k=k+1
        deposit=deposit+1
        
    elif money[l]<monsent[l] and date[l]<2018 and avrmon[l]>4999:

        vs1=(sheet.cell(row=l+1, column=1).value)
        vs2=(sheet.cell(row=l+1, column=2).value)
        text=("Hello, "+vs1+" we today we have a special offer to you: you can register a brokerage account and get 200 dollars for it for free")
        smtpObj.sendmail("forproject1505@list.ru",vs2,text)
        account=account+1
        k=k+1
        
    elif msent[l]>50 and date[l]>2019 and avrmon[l]>6999 and money[l]>99999:
        
        vs1=(sheet.cell(row=l+1, column=1).value)
        vs2=(sheet.cell(row=l+1, column=2).value)
        text=("Hello, "+vs1+" we today we have a special offer to you: you can issue a card with 10% cashback")
        smtpObj.sendmail("forproject1505@list.ru",vs2,text)
        card=card+1
        k=k+1
        
    elif money[l]<monsent[l] and date[l]<2017 and msent[l]<5 and avrmon[l]<1000:
        vs1=(sheet.cell(row=l+1, column=1).value)
        vs2=(sheet.cell(row=l+1, column=2).value)
        text=("Hello, "+vs1+" we today we have a special offer to you: you can get a loan at a reduced rate of 7%")
        smtpObj.sendmail("forproject1505@list.ru",vs2,text)
        loan=loan+1
        k=k+1
    else:
        vs1=(sheet.cell(row=l+1, column=1).value)
        vs2=(sheet.cell(row=l+1, column=2).value)
        text=("Hello, "+vs1+" we today we have a special offer to you: you can get more cashback, download our new app, and chose category wich you like more and get increased cashback ")
        k=k+1
        
    l=l+1
    

print('mails successfully sent:',k,sep=" ")
print('mails about: cashback -', k - deposit - account - card - loan)
print('mails about: deposit - ',deposit)
print('mails about: account - ',account)
print('mails about: card - ',card)
print('mails about: loan - ',loan)