VBA custom format adjustment

Hi,

The current attached VBA generates an e-mail per each Name from sheet "List" and attaches an xlsx in each e-mail with the associated data for that particular Name.

Can someone help by adjusting the code so the xlsx file attached in each e-mail will look exactly like "desired result.xlsx" attached in this post?

(the other functionalities of the VBA should remain the same)

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

The attached workbook is a slightly modified version of the one I posted previously.

I've added two named cells on the "Addresses" worksheet - the columns which contain these cells may be hidden if required. I've also deleted column "R" from the "List" worksheet.

The code involved is as follows:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Hi Greg,

Sorry, I was away for a couple of days.

So I have adapted the content and attached my workbook with your code, to be as close as possible to my official data:

a) column K contains a dropdown for the person to pick some options
b) based on column K option, column M will get populated accordingly (formula based on hidden Sheet2)

-----I have filled rows up until 9000 with the above data validation and formula because this is needed to cover as much rows as the user might input------

Problem: Something is not working right as it seems Excel freezes when I hit "Generate emails". Can you also test based on the attached file?

Does it have something to do with the 9000 empty cells filled with data validation and formula? (K and M)
(However, the code should stop and generate/send emails only up until row 7 in our case, based on data from A to I)

Also, I would need to understand whether the code vlooksup the name from column B, into column A or column D from "Addresses"?

Appreciate your help!

Hi Greg,

1. Seems to work fine, except for the part with the addresses.

If you run the code, for example:

- to "Ben@text.com" it attaches John.xlsx and "Dear John..."
- to "John@text.com" it attaches Richard.xlsx and "Dear Richard..."
- to "Richard@text.com" it attaches Ben.xlsx and "Dear Ben..."

The thing is that in Sheet "Addresses" columns A and B will contain around 2000 addresses, like a contact book. A static list, prone to review once in a while.

The code should only lookup the Name (column B, Sheet "List") into Sheet "Addresses" column A and generate the e-mail text to the address (column B) matching that name.

Is this possible? I think it's a much more clean and simple approach, what do you think?

2. Second point I've wanted to ask you:

- is the code able to generate/send more than let's say 50 e-mails at once? What about 80?

Many thanks!

Hi again,

Thanks for the feedback, and sorry, that was my fault!

To try to speed things up I had disabled automatic calculation, but forgot that the email address of the current UserName must be determined (by calculation) each time. The highlighted additional line in the following routine will do what is required.

Please Login or Register  to view this content.

The code can create as many emails as you like, but a possible "bottleneck" is the number of emails which your email service provider will accept. In a different application (using Gmail via Outlook) I've restricted the size of a "batch" of emails to 80, and have not encountered problems to date.

It should be a relatively straightforward job for you to send your emails in appropriately-sized batches.


Regards,

Greg M



P.S.

The thing is that in Sheet "Addresses" columns A and B will contain around 2000 addresses, like a contact book. A static list, prone to review once in a while.

The code should only lookup the Name (column B, Sheet "List") into Sheet "Addresses" column A and generate the e-mail text to the address (column B) matching that name.

Is this possible? I think it's a much more clean and simple approach, what do you think?

This is the approach used in the current and previous workbooks.