VBA to Download & edit excel file from Outlook

[sanket_sk]

Hi All,


I am getting 2 different mails with attachment early morning on daily basis in outlook, I want to download these files , rename sheet name as well as workbook name and save as .xlsx and keep it in particular folder

Is it possible to perform this activity using VBA? Please help

1st file

Receiving from – Dailyupdate@gmail.com

Subject line :- Closed Open Call Report Created Successfully

File Name :- Closed_Open_Call_Report_IN_*.xls – Here “*” start stands for date which is getting change on every day

Folder location :- Inbox

I want to perform following

1. Open The file, it prompts for “ The File Format & Extension of “Closed_Open_Call_Report_IN_*.xls” don’t match. The file could be corrupted or unsafe. Unless you Trust its source, Don’t open It. DO you want to Open it anyway?”
2. Click on “Yes”
3. Then comes Protected View here I want to click on “ Enable Editing”
4. Then Select entire Column “T & U” convert it in Number with zero decimal
5. Then Select entire column “AR” convert it in Number with zero decimal
6. Rename Sheet as “ Sheet1”
7. Save as file as (.Xlsx) Closed + Todays Date in C:\Users\OneDriv \Office Backup\Daily Update

2nd File

Receiving from – Dailyupdate@gmail.com

Subject line :- Open Call Report Created Successfully

File Name :- AYS_Open_Call_Report_IN_*.xls – Here “*” start stands for date which is getting change on every day

Folder location :- Inbox

I want to perform following

1. Open The file, it prompts for “ The File Format & Extension of “AYS_Open_Call_Report_IN_*.xls” don’t match. The file could be corrupted or unsafe. Unless you Trust its source, Don’t open It. DO you want to Open it anyway?”
2. Click on “Yes”
3. Then comes Protected View here I want to click on “ Enable Editing”
4. Then Select entire Column “T & U” convert it in Number with zero decimal
5. Rename Sheet as “ Sheet1”
6. Save as file as (.Xlsx) Pending + Todays Date in C:\Users\OneDriv \Office Backup\Daily Update

 

[Trevor_S]

I hadn't posted a response to this before, in the hope that there would be someone here who could give you a full answer. But as you haven't had any replies yet, here are my thoughts...

Rather than write VBA in Excel, write it in Outlook. That VBA can then be triggered by the receipt of an email, and check the sender and attachment names to see if they're either of the two you want to process.

Then I would re-order the steps. Save the attachment as a .xlsx file straight from the email, rather than opening it first. That should prevent you getting the warning message on the first file.

Then write the code to open the excel files you've created, make the required amendments and re-save them. This can still be done as part of the Outlook VBA, you'll just need to create an object that is an excel application, and use that where you would have used the word Application had you written the VBA in Excel.

Now the tricky bit ... writing the outlook VBA! Although I've done a small amount in the past, it's not really enough to be able to write this for you - I've mainly just adapted code that I've found from internet searches. You may be able to find what you need by doing a search for "outlook VBA to save attachments on receipt of email" - if not, you may be better trying a post on an outlook forum. Bear in mind that most forums have rules on cross-posting, so post links to other thread on both forums.

If you manage to get code for saving the attachments, but not making the changes, post it here, and someone should be able to help with that part.

Good luck!