I need to extract an Excel email attachment from a signed email that I fetch via Microsoft Graph.
Postman shows the contentType
as multipart/signed
and the name
as smime.p7m
so I guess I somehow need to unpack this attachment first.
Can you help me figure out what the general procedure in such cases would be and maybe what python packages can deal with it and turn it into an Excel file?
You know an email has been digitally signed and content has been encrypted for security when you receive it with a multipart/signed content type. You must first validate the digital signature on the email before extracting and decrypting the encrypted content in order to extract the Excel email attachment from the signed email.
The general procedures are as follows:
Get the signed email using the Microsoft Graph API.
Extract the email's MIME content. To parse the MIME content and extract the attachment, use a Python MIME library like the email package.
Verify the digital signature and decrypt the content using a Python library like M2Crypto or OpenSSL.
The decrypted data should be saved as an Excel file.
import base64, email
from M2Crypto import SMIME, X509, BIO
msg1 = email.message_from_string(mime_content)
# extract the attachment
attachment = None
for part in msg1.walk():
if part.get_content_type() == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
attachment = part.get_payload(decode=True)
break
if attachment is None:
print("Attachment not found")
else:
# verify the digital signature and decrypt the content
p7 = SMIME.load_pkcs7_bio(BIO.MemoryBuffer(mime_content))
signers = p7.get0_signers(X509.X509_Stack())
if len(signers) == 0:
print("No signers found Pass.")
else:
signer = signers[0]
bio1 = BIO.MemoryBuffer(str(attachment))
smime = SMIME.SMIME()
smime.set_x509_store(SMIME.X509_Store())
smime.set_x509_stack(X509.X509_Stack())
smime.load_key('path/to/private_key.pem', 'path/to/certificate.pem')
dec = smime.decrypt(bio1, signer)
excel_content = base64.b64decode(dec)
# save the decrypted content as an Excel file
with open('path/to/excel_file.xlsx', 'wb') as f:
f.write(excel_content)
print("Excel file saved successfully")