VBA custom format adjustment

[tyxanu]

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)

[Greg M]

Hi there,

See if the attached version of your workbook does what you need - it uses the following code:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your requirements.


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

Regards,

Greg M

[Greg M]

Hi again,

The attached version should ensure that colour schemes are copied correctly from the source workbook.

The highlighted line has been added to the existing routine:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[tyxanu]

Hello Greg,

This seems fine. I would have one more requirement if you would be able to assist:

I would like to remove Column R (e-mail address) and have the code perform a vlookup directly by looking for the Names (from Column B) in Sheet "Addresses" and generate the e-mail accordingly (with the relevant data) to the Name's contact stated there.

Therefor, the code should attach the xlsx containing Sheet "List" in whatever form and format I adjust it (even if I add extra columns/remove columns/change format/etc).

Huge thanks for this

[Greg M]

Ok, I'll see what I can do tomorrow

[Greg M]

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

[tyxanu]

Hi Greg and thanks again ,

I don't see the attachment in this most recent post. Was it intended to be here?

[Greg M]

Oops - mea culpa!

[tyxanu]

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!

[tyxanu]

Hi Greg

Have you got the chance to review the above reply?

[Greg M]

Hi again,

Hey, nobody said anything about nine thousand rows!

That sort of number makes a BIG difference to the approach used in producing what is required!

The original approach deleted unwanted rows on a row-by-row basis - it takes quite some time to individually delete the large number of rows involved!


The attached version prefixes each instance of the current Addressee Name with an "AAAAA" prefix and then sorts the worksheet to bring the current Addressee to the top of the list. The prefix is then removed.

When this has been done, the unwanted Addressee rows can be deleted in bulk - this takes far less time than deleting them individually.

The following code is used:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your requirements.


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

Regards,

Greg M

[tyxanu]

Hi Greg,

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

If you run the code, for example:

- to "[email protected]" it attaches John.xlsx and "Dear John..."
- to "[email protected]" it attaches Richard.xlsx and "Dear Richard..."
- to "[email protected]" 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!

[Greg M]

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.

[tyxanu]

Greg, you're a god!

Let me play around with it and test all the scenarios and I'll get back to you.