VBA to Split data into several reports and email via outlook

**Skjoldborg** · 07-06-2009, 07:20 AM

Hello VBA Expers and novices, and everyone in between

Six months ago I startet looking into VBA, and this forum basically taught me all I know of VBA

I have always been able to find answers here.. However here is my first post on a problem I can’t solve on my own or seem to read the answer to.

Here is the case:

I am extracting data from a excel file and then I am arranging the data in my own excel file. My own excel file contains “contracts” - each line in this sheet contains a contract. Within the line I have all the information needed. I want to create several reports with contracts - they have to be split depending on who owns the contract. The contract owner is defined within each line of the data. The “contract owner” can have multiple contracts so I need to sort the reports so that each "contract owner" only gets 1 email with and attachment with 1 excel file with all his contracts.

So basically I want to split my excel file into several reports depending on a certain criteria (cell value = contract owner).

And… I basically created a code that does all this for me.. however I need to tweak the code.
This is important, as you can see in my code, it is long and depending on the names within the VBA code are correct.
(Right now i have 46 owners and 250 contracts, so i would have to doublicate the code below 46 times!!).
Further I am possiblely not the one to maintain the VBA code - so when contract owners change (promted/hired/fired) the code needs to be update manually.. which is a huge pitfall. (or impossible with the code in its current form).

This is were you come in! There are so many experts out there, I am sure one of you can crack this case – and enlighten me

Please Login or Register  to view this content.

Hope to see some inputs - thank you in advace. Peace out!

(Before you suggest I put this into Access I have to add that I know nothing of Access and my department insists to keep our database as an excel-sheet until a proper contract management system is in place)

//Skjoldborg
VBA student

**Skjoldborg** · 07-07-2009, 08:16 PM

Problem solved at this tread:

http://www.mrexcel.com/forum/showthr...94#post1991594

I have attached solution. Explanation is given in above link.

2 modules in attached workbook:
Hardcode = old code
Smartcode = new code

Smartcode is the solution i chose that works

credit to http://www.rondebruin.nl/sendmail.htm
(where i found the solution)