Automated mailing of data from a spreadsheet

[DozyBurgher]

The starting point is a report in Excel that groups data by customer in rows as attached (I can't change this), each report (monthly) has a different number of transactions per customer and therefore number of rows and what I hope to do is email the data per customer to that customer.

The format of the report is as attached with each discrete data set defined by a cell in Column A which has an account code, a comma and then the company name. The immediately following rows have the transactional details which I need to email, the email address for each account would be held in a separate sheet as attached.

I am seeking to build a macro driven sheet that this data would be pasted into with simple controls that send each customer's subset of data to the appropriate address but am unsure of the best methodology to do this. The nuisance is that there are about 60-70 customers so am seeking the lowest manual input and computational load for the finished sheet.

Any thoughts as to how to design a sheet to accomplish this would be much appreciated, I have some Excel macro experience but not as much as I need to complete this. The account code and company name does not change and I don't mind sending a mail with no data if a company has no transactions for the month.

TIA for any help!

[maniacb]

Give this code a try;

Please Login or Register  to view this content.

[DozyBurgher]

Hi maniacb and many thanks for the code, I am, however, struggling with the ema line:

ema = WorksheetFunction.VLookup(ad.Cells(Ra.Row, 1), ea.Range("A1:B" & lea), 2, 0)

I get a RunTime error '1004' - Unable to get the VLookUp property of the WorksheetFunction class

Being a very inexperienced user, it is well beyond my ability to understand the expression and how to get it to work. Please would you have a quick look and if you can point me in the right direction I would much appreciate it. Thank you for your extraordinary generosity!

[maniacb]

I received this error too when one of the emails was missing in the list, or the name of the entity was misspelled. I corrected those things in the file I uploaded.

[DozyBurgher]

Ah, yes, many thanks again, an excellent solution to my problem, it is very much appreciated!

[maniacb]

If that takes care of your original question, please select*Thread Tools*from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon (Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.