[Python] Saving Outlook Email Attachment to Image File

[Requirements]

Everyweek, the canteen vendor send a meal plan for the week. The email goes to a specific folder thanks to Outlook email rule. But still human power should engage to copy the specific cell in the meal plan Excel file then save as an image to update canteen application.

- Save meal plan Excel file which is in a weekly meal plan email

- Copy meal plan area and save as image file

 

[OS] 

Windows 10

 

[Development Language]

Python/VBA

 

[IDE]

PyCham/Microsoft Visual Basic for Application

 

[Code]

- SaveAttachmentToDisk VBA

Option Explicit
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)
    '''' Variables Declaration''''
    Dim objShell As Object                              'CMD Shell
    Set objShell = VBA.CreateObject("WScript.Shell")    'CMD Shell
    
    Dim oAttachment As Outlook.Attachment               'Email attachment
    Dim sSaveFolder As String                           'Excel file save folder
    Dim sFileName As String                             'Excel file name
    Dim sScriptPath As String                           'Python script path
    Dim sPython As String                               'Python exe path
    Dim iRtn As Integer                                 'Return value
    
    '''' Variable set up ''''
    sSaveFolder = "\\kryanser03\Shared-Data\컴퓨터드라이버\석식조사\Excel"
    sScriptPath = "C:\SRE\ExcelSectionToJPEG\ExcelSection2JPEG.py"
    sPython = "C:\Python310\python.exe"
    
    '''' Execution ''''
    For Each oAttachment In MItem.Attachments           'Check all email attachemnt, if excel file then save
        If Split(oAttachment.DisplayName, ".")(1) = "xls" Or Split(oAttachment.DisplayName, ".")(1) = "xlsx" Then
            sFileName = sSaveFolder & "\" & oAttachment.DisplayName
            oAttachment.SaveAsFile sFileName
            Exit For
        
        End If
    Next
                                                        'Run Python script to save Excel area as image  file
    iRtn = objShell.Run("cmd /s /k " & sPython & " " & sScriptPath & " " & sFileName, vbNormalFocus)
     
    
End Sub

- ExcelSection2Image Python

import sys
from datetime import datetime
import excel2img

#Get date
now = datetime.now()

#Image path
img_name = "\\\\kryanser03\\Shared-Data\\컴퓨터드라이버\\석식조사\\MealPlan\\MealPlan_"+now.strftime("%y%m%d")+".png"

#Function for saving Excel area as an image
def excel2jpeg(file_name):
    try:
        excel2img.export_img(file_name, img_name,"", "'주간식단표'!A1:F26")
    except Exception as e:
        print(e)


if __name__ == "__main__":
    excel2jpeg(sys.argv[1])

excel2img is a powerful Python module that can copy cells in Excel file and transform it to an image file.

💪

 

[Test]

 

Check email rule - email from canteen vendor hits the mailbox then run VBA script

Send a test email

Check whether Excel file is saved properly

Check whether image is saved

 

Check canteen app for meal plan image

[Lesson Learned]

- VBA is big pain in the ass because it does not return any error message when the script goes wrong

- Python can do anything 👍