Create and Distribute Excel Files using Python and Outlook | Automate Excel with Python 

@JMan Tam Thanks for watching. In the video, I have used an HTML body with variables. So, I am not sure if I understand your question. Can you perhaps elaborate on what it is you're actually trying to achieve? Please provide some additional info. Thanks!

@JMan Tam That is very similar to what I have demonstrated in the video. Check it out here: 05:17min

Great video. I hope your channel will grow. It's almost exactly the workflow I am trying to automate. However I am looking for the code to run on Mac OS, which doesn't support win32.

@@dimaua1830 Thank you very much! You are right; win32 will only work on Windows. For implementing something similar on macOS, you might want to check out the following solution: stackoverflow.com/questions/61529817/automate-outlook-on-mac-with-python I hope this helps!

Hi Sven awesome 😍! If could change year to daily reports, i could i do it automatically? Thanks in advance Abdul

Thanks for the positive feedback and your comments! I appreciate it! 🙏

For me, it is always a pleasure reading your valuable comments. Seeing your comments really motivates me. Thank you so much!

Thank you! 😃❤

Wow, thank you for the positive feedback! I'm so glad to hear that the video was smooth and helpful for you. It's always great to know that my explanations were clear and useful. Thank you for taking the time to leave a comment and share your experience with me. :)

@@CodingIsFun How can we format each excel file before distributiion? i.e., setting columns width, number formatting, adding totals etc.?

@@ahmedsalman3294 When you export the files, you can access the files using the xlsxwriter package: www.screencast.com/t/w2nPwpRmzY21 xlsxwriter.readthedocs.io/working_with_pandas.html Happy Coding!

@@CodingIsFun Thanks a ton. I did and I managed to get the result I wanted.

Thanks a ton, Rajeev Menon! 💪🏽 I appreciate your support!

Thanks for the kind comments, and happy to hear that you are enjoying the videos! Happy Coding & Automating! 🦾

Hey there! Appreciate the love! 🙏 For Outlook on Apple, you might want to explore using PyUNO or unotools, which are tailored for macOS. However, I don't have a macOS setup, so I can't test it or provide you more information on that. Hope this points you in the right direction! Happy Coding! :)

*I am glad to hear you are enjoying them, thanks for watching and for the comment!*

Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!

Glad you liked it. Thanks for watching.

Thank you! Glad you liked the video :)

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

Thanks for watching the video & your question. When you are saving the attachments (excel files), you could specify a password. Have a look at the following example: Credit: stackoverflow.com/questions/36122496/password-protecting-excel-file-using-python from pathlib import Path import subprocess import pandas as pd # pip install pandas openpyxl # Locate examples files & create output directory EXCEL_FILE_PATH = Path.cwd() / "Financial_Data.xlsx" ATTACHMENT_DIR = Path.cwd() / "Attachments" ATTACHMENT_DIR.mkdir(exist_ok=True) # Load financial data into dataframe data = pd.read_excel(EXCEL_FILE_PATH, sheet_name="Data") # Get unique values from any particular column column_name = "Country" unique_values = data[column_name].unique() def set_password(excel_file_path, pw): excel_file_path = Path(excel_file_path) vbs_script = \ f"""' Save with password required upon opening Set excel_object = CreateObject("Excel.Application") Set workbook = excel_object.Workbooks.Open("{excel_file_path}") excel_object.DisplayAlerts = False excel_object.Visible = False workbook.SaveAs "{excel_file_path}",, "{pw}" excel_object.Application.Quit """ # write vbs_script_path = excel_file_path.parent.joinpath("set_pw.vbs") with open(vbs_script_path, "w") as file: file.write(vbs_script) #execute subprocess.call(['cscript.exe', str(vbs_script_path)]) # remove vbs_script_path.unlink() return None # Query/Filter the dataframe and export the filtered dataframe as an Excel file for unique_value in unique_values: data_output = data.query(f"{column_name} == @unique_value & Year==2021") output_path = ATTACHMENT_DIR / f"{unique_value}_2021.xlsx" data_output.to_excel(output_path, sheet_name=unique_value, index=False) set_password(output_path, "password")

Thanks for watching. You could schedule your Python script to run daily using the Windows Task scheduler. Have a look at the following tutorial: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-n2Cr_YRQk7o.html I hope it helps! Happy Sending!

Thanks for watching. Sorry, but I am not sure what you are trying to achieve. Please provide some additional information and the code you have tried. Thanks!

Glad you liked it. Thanks for watching. 👍

Thanks a lot!

Thank you for your video suggestions

Happy to hear that it was useful. You could try to embed the image using the image html tag.

I am happy to hear that you have been finding the videos helpful. Regarding your question: Instead of the Outlook signature, you could create your own using HTML. Therefore, insert a new column in the email distribution list in Excel. In the new column, insert your HTML signature. An easy way to generate the HTML code is to use the following free online tool: html-online.com/editor/ When creating the email (5:17 min), you want to concatenate your signature column with the actual body. I have not tested it out, but I assume that this should work. Happy Coding!

Thanks!

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

@@CodingIsFun no worries! I totally understand! In the meantime I believe I solved my question with some further search!

Thanks for watching. In this video, I will explain the different options to export the dataframe to an existing workbook: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-DroafWQXqDw.html Happy Coding!

Glad to hear that you liked the video. Thanks for watching and taking the time to leave a comment. Yes, the pywin32 library also works on a 64bit system. Also, thank you for the video suggestion. I already have one video on my channel on automating Word using Python (and Excel). Perhaps it might be helpful to you: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-T3meVMaV8AA.html Happy Coding!

That might be possible. Feel free to try it out

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

Yes, that is possible. After your selection (data validation), save the workbook and run the script.

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you!

2:27 min. It is creating a list with all unique items in a column. Let's say you have column A: A1: Fruits A2: Apple A3: Banana A4: Kiwi A5: Apple A6: Banana The unique list will look like this: Fruits = ["Apple", "Banana", "Kiwi"]. In the loop, I am then iterating over each item. I hope it helps!

@@CodingIsFun yes i got it! Thank you!

Thanks so much for watching the video and leaving a comment! Your request is definitely noted. However, I get a ton of requests for custom solutions and, as much as I'd love to help everyone out, I just don't have the time in my schedule to develop and test all of them. I hope you can understand. Happy Coding!

Thanks for watching the video & your question. Yes, there are many ways to send out emails using Python. You might want to check out the following blog article: realpython.com/python-send-email/ I hope this helps! Happy Coding! 😀

Thanku so so much 😍👍

Glad you find it helpful. If you have already a signature, you could modify the code. Have a look at the following example: stackoverflow.com/a/43331219 I hope it helps! Happy Coding!

@@CodingIsFun Very helpful answer Sven! :D... However, I can't make that the code gets the image that I have in the signature

Thanks for watching and for your question. Hard to tell from a distance why you are facing an error. Sorry that I cannot help.

@@CodingIsFun Hey thanks, turns out it was just a syntax error in the for loop in the mailing part. I have one other query, is there any way to keep the source formatting of the original excel file? Once again, thank you so much. This video just reduced 3 hours of my workload

Thank you & happy coding! :)

@@CodingIsFun Hi Sven, as mentioned I have modified this script for my costumer based purpose. Now I have following problem. In my Email-List there are 30 customer-mail addresses. But if I have less then 30 excel-lists, for example 25, I get an error-message that python can't not find the attachments. Do you have an idea how to solves this?

@@mikekaspari1357 Hi Mike, you might want to check if the file/attachment exists before sending it. Example below (original script: github.com/Sven-Bo/create-distribute-excel-files/blob/master/create_send_reports.py ): attachment_path = str(ATTACHMENT_DIR / "YourExcelFile.xlsx") if Path(attachment_path).is_file(): mail.Attachments.Add(Source=attachment_path) I hope this helps!

@@CodingIsFun thank you very much. I try it tomorrow on work. I give you a feedback

Hi Sven, it works. Thank you very much

Use backticks to escape the empty space in the column name df.query("`Month Number` == 9")

@@CodingIsFun Ok, now I see what you mean by "Backticks". At Github aren't displayed since are taken as code... Thanks for the great support Sven :D!!🤗

@@SaulEduardoo2 I edited the GitHub comment ;)

Good suggestion, but I do not have a mac.

When exporting the dataframes, you could also reformat the Excel files. One solution is to use xlsxwriter. Check out the docs here: xlsxwriter.readthedocs.io/working_with_pandas.html

Thanks for watching the video. Could you send over the example dataset to: contact@pythonandvba.com? I will look at your dataset, but I cannot make any promises. Thanks!

Thanks for watching. Please give it a try and see if you get an error message. I assume, you might need to implement a bit more error handling. Happy Coding!

Thank you very much for watching the video and your comment. Your requirements are well noted. Yet, I receive many requests for creating individual solutions. As much as I want to help, I simply do not find the time in my daily schedule to develop & test all the different requests. I hope you can understand. Thank you! Cheers, Sven ✌️

Thanks for watching the video & your question. To send out the emails via Outlook on a Mac, you could use the Python library "py-appscript": appscript.sourceforge.net/py-appscript/doc_3x/appscript-manual/03_quicktutorial.html Example on how to send emails with attachments: stackoverflow.com/a/62089384 I hope this helps!

Thank you, I tried this already with no luck, but I will have a go at it again.