i am trying to save outlook email body content to dataframe then to csv/excel, we usually get prices from vendor for different indices in tabular format, i tried using Body_content = message.HTMLBody but didnt work as intented. Thus i am ok with using Body_content = message.Body and print (df.To_string()). Now the next step is to store those values in excel sheet even if its open by an anlyst in below format. we keep getting index prices from different vendors, if this one works perfectly, i will follow the same method for others as well.
Output
This is how it ends up getting a single column dataframe
import pandas as pd
import win32com.client
import re
import os
import pandas
import datetime
from datetime import date
EMAIL_ACCOUNT = '[email protected]'
EMAIL_SUBJ_SEARCH_STRING = 'MSBPSCSP Index Price'
EMAIL_CONTNT = {'Ticker': [], 'TickerLevel': [], 'DATE': []}
out_app = win32com.client.gencache.EnsureDispatch("Outlook.Application")
out_namespace = out_app.GetNamespace("MAPI")
#lastWeekDateTime = dt.datetime.now() - dt.timedelta(days=1)
#lastWeekDateTime = lastWeekDateTime.strftime('%m/%d/%Y %H:%M %p')
root_folder = out_namespace.GetDefaultFolder(6)
out_iter_folder = root_folder.Folders['Email_Snapper']
#char_length_of_search_substring = len(EMAIL_SUBJ_SEARCH_STRING)
item_count = out_iter_folder.Items.Count
Flag = False
cnt = 1
if item_count > 0:
for i in range(item_count, 0, -1):
message = out_iter_folder.Items[i]
if EMAIL_SUBJ_SEARCH_STRING in message.Subject:
#message = message.Restrict("[ReceivedTime] >= '" lastWeekDateTime "'")
Body_content = message.Body
Body_content = Body_content[:Body_content.find("Disclaimer")].strip()
df = pd.DataFrame([Body_content])
print(df.to_string())
The final objective is to insert those prices in oracle database either through saving them in excel first or if possible directly from the dataframe (that's even better)
CodePudding user response:
You can export the dataFrame
to excel
df.to_excel("output.xlsx", index=False)
or CSV -
df.to_excel("output.csv", index=False)
CodePudding user response:
The Outlook object model supports three main ways of customizing or getting the message body:
- The Body property returns or sets a string representing the clear-text body of the Outlook item.
- The HTMLBody property of the
MailItem
class returns or sets a string representing the HTML body of the specified item. Setting theHTMLBody
property will always update theBody
property immediately. For example:
Sub CreateHTMLMail()
'Creates a new e-mail item and modifies its properties.
Dim objMail As Outlook.MailItem
'Create e-mail item
Set objMail = Application.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>Enter the message <a href="http://google.com">text</a> here. </BODY></HTML>"
.Display
End With
End Sub
- The Word object model can be used for dealing with message bodies. See Chapter 17: Working with Item Bodies for more information. So, you can get the required information using the Word object model.
It is up to you which way is to choose. But I suppose the Word object model can help you with extracting the required information.