Python Read Email copy to Excel, launch macro and copy value.

#The script opens the last receive email from outlook, and looks for a few elements like amount, ocr-code, and date. Then it copies those from the email to an excel, and launches a macro. The macro in the excel sheet generates some text in column G, which we loop through and stores in our variable SqlCode.

#!/usr/bin/python
import re
import os
import win32com.client

outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

inbox = outlook.GetDefaultFolder(6) # "6" refers to the index of a folder - in this case,
                                    # the inbox. You can change that number to reference
                                    # any other folder
messages = inbox.Items
message = messages.GetLast()
body_content = message.body
print (body_content)
paymentmsg = body_content

#paymentmsg = "Hej 2 Jval01eff2rTEST01ey 1200,00 kr. +test2 +71<066825614704683+86870762Betalingsdato 18-07-2017"

# Delete Python-style comments
#num = re.sub(r'#.*$', "", phone)
#print ("Phone Num : ", num)

# Remove anything other than digits
#num = re.sub(r'\D', "", phone)  
#print ("Phone Num : ", num)

# Remove anything other than digits
#num = re.sub(r'\D', "", phone)  
#print ("Phone Num : ", num)

#phone1 = "xxx Your number is <b>123</b>  fdjsk"
#Clean the message for easier identifying elements
paymentmsg = paymentmsg.replace(" ", "")
paymentmsg = paymentmsg.lower()
#print (paymentmsg)

amount = re.search("(\d+\D\d+)kr", paymentmsg)
if amount:
    print (amount.groups()[0])
    #Store Beløb
    amountStr = (amount.groups()[0])
else:
    if amount2:
        amount2 = re.search("(\d+)kr", paymentmsg)
        print (amount2.groups()[0])
        #Store Beløb
        amountStr = (amount2.groups()[0])
 
OCR = re.search("\\+71<(\d+)", paymentmsg)
if OCR:
    print (OCR.groups()[0])
    #Store OCR
    ocrStr = (OCR.groups()[0])

paymentdate = re.search("(\d+-\d+-\d+)", paymentmsg)
if paymentdate:
    print (paymentdate.groups()[0])
    #Store Dato
    paymentdateStr = (paymentdate.groups()[0])

env = re.search("(val\d+)", paymentmsg)
if env:
    print (env.groups()[0])
    #Store
    envStr = (env.groups()[0])
else:
    if env:
        env = re.search("(test\d+)", paymentmsg)
        print (env.groups()[0])
        #Store Miljø
        envStr = (env.groups()[0])

path = "C:\\Users\\jelai\\Betalingsfil Generator V2.xlsm"

if os.path.exists(path):
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename=path, ReadOnly=1)
    ws = xl.Worksheets("MENU")
    ws.Range("B3").Value = amountStr
    ws.Range("C3").Value = paymentdateStr
    ws.Range("D3").Value = "'" + ocrStr
    #Run macro
    xl.Application.Run("'Betalingsfil Generator V2.xlsm'!Module3.GogoCREMUL")
    #sqlCode = ws.Range("G1").value
    sqlCode = ""
    #identifying last used row
    lastRow = ws.UsedRange.Rows.Count
 
    col = ws.Range("G1:G" + str(lastRow))
    #Loop through each cell in sql statement
    for c in col:
        #cell.Offset(1,2).Value = "W0000%s" % cell.Value
        if str(c.value) != "None": #Do not include empty cells
            sqlCode = sqlCode + str(c.Value)
 
    print (sqlCode)
   # xl.ActiveWorkbook.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
   # xl.Quit() # Comment this out if your excel script closes
    del xl