Creating Macros - Copy Alerts generating from Email Attachment to another workbook


Hello everybody,

Suppose I get an alert on my Email (e.g an excel file attached on the mail) and I need to copy the values to another workbook on specific sheets and specific cell. How can I do that ??

Thank you in advance.



It sounds like you want to write a custom macro to copy/paste specific data form one workbook to another? But are you wanting this "Alert" to trigger that copy? If so, you need to research a little about using macros with Outlook and start there. I, at least, am not going to create a macro from scratch for you for this; at least try first!
don (rep: 1745) Jul 12, '19 at 8:10 pm
Add to Discussion


Selected Answer

The short answer to your question is: You will need VBA.

In order to write any progam you need to be very clear about the steps you wish to automate. It is possible to let your email browser react to the alert, check if there is an attachment, if so, check if it's a qualifying workbook, and then call up Excel to open it, extract data, open your master workbook and paste the new data there. You may not be able to design this process, let alone write the progam. Therefore I suggest you forget about automating the email.

Instead, when you receive a workbook, save it to your disk. Then manually open the master workbook. Let that workbook contain the program to extract data from the one just received. In this way you would need to deal with only one application (Excel). The programming would be only about 10% of that required in the first plan. That may still be one or two days of work but it's a task you have a chance of accomplishing by yourself.

The first step would be to make your master workbook macro enabled. Then you need to write a program to find and open the new file, the incoming one you just saved to your disk. Search for the code online. It's rather simple. Just use Windows' own File-Open dialog. (Google for "VBA for File Open dialog")

From there on, you need to find the data you want to extract, find the place in the master where they should be pasted, and complete the transaction. When done, close the source workbook. This may be easy or difficult depending upon your worksheet design. Nobody can help you with the design because nobody knows what you need. Also, nobody can help you with your programming without knowing your designs, both for the source and master worksheets.

However, when you need help along the way you will find it here on this forum. Avoid asking hypothetical questions (as you have done here) because you will get only hypothetical answers which don't help you much. Don't ask follow-up questions (as you may be tempted to do here). Once you receive an answer that helps you build that new knowledge into your project and ask another, more advanced question.


Answer the Question

You must create an account to use the forum. Create an Account or Login