Automatic Extract Distribution VBA

[accountant124]

Maybe a bit of a long shot but I thought I'd give it a go anyway!

I have a set of data, from which there are a number of (formula driven) extracts in other sheets within the same workbook. I'm looking for a macro which will go down the contacts list, see which extracts each contact needs and send the relevant one to them in a separate email with an attachment.

A couple of issues:
1) The extract will need to be hard coded/valued before it is saved/sent.
2) The extracts/attachments which are sent need to be saved in an archive folder location.

I've attached a set of data which replicates the data I have.

Thanks in advance for your help!!

 

[accountant124]

Just realised I can't attach data sets...

The first sheet has the base data in Col B to F.
The second sheet is called 'Finance Extract' and links cells in Col B:F with direct cell links (='Base List'!B3).
The third sheet is called 'CONTACTS' and in Col A has the name of the extract required (Finance Extract) and in Col B is the email address of the recipient.

As mentioned above, when it is exported and saved, the formulas in the extract (Finance Extract) need to be broken.

Hopefully that helps.

 

[DanteAmor]

I have various doubts:
- The data to be sent are those of the "Finance Extract" sheet, but which part or what data?
- Email is outlook?
- What is the file going to be called?
- If several files are going to be generated for each email, how will each file be called?
- Where to get the email address?
- Subjet?
- Body?

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[accountant124]

Perfect, thanks! Here you go.....

https://www.dropbox.com/s/k1cuwqd4vmzcx68/Product List.xlsm?dl=0

In answer to your questions:

- All of the data in the extract needs to be sent.
- Yes, I need to send in outlook as an Excel attachment.
- The file can just be called 'Extract *Date*'
- It will be one file per email.
- The email address is in Col. B of the CONTACTS sheet
- Subject should be 'New Product List'
- Body can be generic 'Hi, please see attached.'

 

[DanteAmor]

I did not understand your file. In the "CONTACTS" sheet there is only one record, then only one email and one file will be created.
I also don't understand why the "Finance Extract" sheet has the same information as the "Base List" sheet.
You could explain more in depth what you need. Maybe you need to send a file with more data and explain what you need inside the file.

 

[accountant124]

I did not understand your file. In the "CONTACTS" sheet there is only one record, then only one email and one file will be created.
I also don't understand why the "Finance Extract" sheet has the same information as the "Base List" sheet.
You could explain more in depth what you need. Maybe you need to send a file with more data and explain what you need inside the file.

Sorry for the confusion - hopefully the updated file here makes more sense.... https://www.dropbox.com/s/62qhiunydu7ex2b/Product List.xlsm?dl=0