Recently one of my friend ask me to write a script which can download a file from from urls saved in a excel file and the downloaded files to a bunch of email address saved in a separate excel file.

So I decided to write a script in python and here’s how I did it.

Flow of the program

• Read URL from the excel file
• Download file from the URL
• Read recipient email address from excel file
• Create an email
• Send the email
• Stitching all pieces together

Read URL from excel file

To read excel file in python we will use the openpyxl library. Alternatively you can use other libraries like pandas, openpyxl, xlsxwrite, xlwt, xlutils, but some doesnot support the xlsx file format so I used openpyxl. Leave a comment if you tries using other libraries to read your xlsx file.

To install xlrd library run command in cmd

pip install openpyxl

Now we need to read oepn the file and read the cell of which contain the url from where the files will be downloaded.

Read cells in an excel .xlsx file using openpyxl

import openpyxl
#path of file containing url
url_file="url.xlsx"

#load the excel file
workbook = openpyxl.load_workbook(url_file)

#load the first worksheet
worksheet = workbook.active

#find max rows which has data
max_rows= worksheet.max_row

#list which stores the urls
url_list=[]

#print all data in row
for row in worksheet.iter_rows(max_row=max_rows):
    for cell in row:
    	url_list.append(cell.value)
    	
print(url_list)

Now that we have read the excel file and get the list of urls we ready to download the file from the url .

Downlaod file from url

To download file from the url we will be sending a get request to the url and in response receive the file which can be saved or can be used within without saving.

Code to download file using a url

import requests

#request the image url
downloaded_file=requests.get("https://upload.wikimedia.org/wikipedia/commons/thumb/4/46/Olhos_de_um_gato.jpg/1280px-Olhos_de_um_gato.jpg")


#create a new file with jpg extension
save_file= open("1.jpg",'wb')

#save file with content received from response 
save_file.write(downloaded_file.content)

#close file
save_file.close()

Note: Response from the request url is in binary format which can only be written by opening the file in “wb” mode.

You might need to debug depending on the format of response you get.

Reading recepients email address from Email

This is similar to reading url from excel file read as explained above.

Now, we have the downloaded file, and recipient email address, lets create an email.

Creating an email using python

To send emails python already provide a built-in email, smtplib and ssl library.

To create a email we will need the following things

• sender address
• receivers address
• subject
• body

To enable your gmail to send email using smtp you need to enable 2-factor authentication and a different “app password”

Follow this guide to generate app password use any device name .

Code to send email with image attachment

#code from https://realpython.com/python-send-email/

import email, smtplib, ssl

from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

subject = "Python Script can sent emails too"
body = "This is an email with image attachment sent from Python"
sender_email = "[email protected]"
receiver_email = "[email protected]"
password = "app password"

# Create a multipart message and set headers
message = MIMEMultipart()
message["From"] = sender_email
message["To"] = receiver_email
message["Subject"] = subject
message["Bcc"] = receiver_email  # Recommended for mass emails

# Add body to email
message.attach(MIMEText(body, "plain"))

filename = "1.jpg"  # In same directory as script

# Open PDF file in binary mode
with open(filename, "rb") as attachment:
    # Add file as application/octet-stream
    # Email client can usually download this automatically as attachment
    part = MIMEBase("application", "octet-stream")
    part.set_payload(attachment.read())

# Encode file in ASCII characters to send by email    
encoders.encode_base64(part)

# Add header as key/value pair to attachment part
part.add_header(
    "Content-Disposition",
    f"attachment; filename= {filename}",
)

# Add attachment to message and convert message to string
message.attach(part)
text = message.as_string()

# Log in to server using secure context and send email
context = ssl.create_default_context()
with smtplib.SMTP_SSL("smtp.gmail.com", 465, context=context) as server:
    server.login(sender_email, password)
    server.sendmail(sender_email, receiver_email, text)

Explaining all each line of code will make this blog post too long you can check out Realpython’s send simple email blog post to know more about the above code.

Stitching all pieces together

Finally we have all the pre-requisite, now its time to put them all in one script and start bombarding emails to the recepients.

Sending downloaded images to recipients email address

import openpyxl
import requests
import uuid

import email, smtplib, ssl

from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

#path of file containing url
url_file="url.xlsx"
recepient_file="rec.xlsx"

#load the excel file
workbook_rec = openpyxl.load_workbook(recepient_file)
workbook_url = openpyxl.load_workbook(url_file)

#load the first worksheet
worksheet_url = workbook_url.active
worksheet_rec = workbook_rec.active

#find max rows which has data
max_rows_url= worksheet_url.max_row
max_rows_rec= worksheet_rec.max_row

#list which stores the urls
url_list=[]
rec_list=[]

#print all data in row
for row in worksheet_url.iter_rows(max_row=max_rows_url):
    for cell in row:
    	url_list.append(cell.value)

for row in worksheet_rec.iter_rows(max_row=max_rows_rec):
    for cell in row:

    	if cell.value!= None:
    		rec_list.append(cell.value)

	
# print(url_list)
# print(rec_list)

def download_file(url):
	
	downloaded_file=requests.get(url)
	file_name= str(uuid.uuid4().hex)
	save_file= open( str(file_name + ".jpg"),'wb')
	save_file.write(downloaded_file.content)
	save_file.close()

	return str(file_name+".jpg")

def send_email(rec_address,attachment_file):

	subject = "An email with attachment from Python"
	body = "This is an email with attachment sent from Python"
	sender_email = "[email protected]"
	receiver_email = str(rec_address)
	password = "generated app password"

	# Create a multipart message and set headers
	message = MIMEMultipart()
	message["From"] = sender_email
	message["To"] = receiver_email
	message["Subject"] = subject
	message["Bcc"] = receiver_email  # Recommended for mass emails

	# Add body to email
	message.attach(MIMEText(body, "plain"))

	filename = attachment_file  # In same directory as script

	# Open PDF file in binary mode
	with open(filename, "rb") as attachment:
	    # Add file as application/octet-stream
	    # Email client can usually download this automatically as attachment
	    part = MIMEBase("application", "octet-stream")
	    part.set_payload(attachment.read())

	# Encode file in ASCII characters to send by email    
	encoders.encode_base64(part)

	# Add header as key/value pair to attachment part
	part.add_header(
	    "Content-Disposition",
	    f"attachment; filename= {filename}",
	)

	# Add attachment to message and convert message to string
	message.attach(part)

	text = message.as_string()

	# Log in to server using secure context and send email
	context = ssl.create_default_context()
	with smtplib.SMTP_SSL("smtp.gmail.com", 465, context=context) as server:
	    server.login(sender_email, password)
	    server.sendmail(sender_email, receiver_email, text)

	# print(attachment_file,"sent to",receiver_email)

# down load all the files 
file_name_list=[]

for file_url in url_list:
	print(file_url)
	if file_url!= None:
		file_name=download_file(file_url)
		file_name_list.append(file_name)

print(file_name_list)

for recepient in rec_list:
	for attachment_file in file_name_list:
		send_email(recepient,attachment_file)

The above script reads the recipient one by one and send all the downloaded file to each recipient in separate email.

You can download the file from Download Now

Feel free to comment if you want to add extra feature or need help in debugging your version of the code.