Email lines based on column value

This is my first post so I hope I have the protocol etc correct ....

I am administering an event that about 30 people are attending. I'm accepting scores of payments each month from various sources, each payment assigned to a specific participant.

I have a Workbook with two Worksheets, Participants and Payments. I export details of all the payments into the latter.

I want to be able, for each row in Participants, send the participant all the rows in Payments that relate to him/her.

So in the example, I want to email to Tony Woodcock at a at b.com rows 1 and 4 from the Payments sheet; and to Amelia Earhart rows 2 and 3 (preferable all with a standard wrapper explaining what it all is).

Is this possible in Excel (or anything else?!).

All inspiration gratefully accepted.

Thanks
N

This is fantastic! Thank you!

Can you indulge me with a couple of follow-up questions? Presumably I can try to learn a bit of VBA programming to a) learn how to omit some columns and b) produce a total at the bottom?

And, this creates 30 emails within Outlook for me to press SEND on - no way of by-passing the need to do that?

cheers
N

Originally Posted by Mumps1

Regarding your questions, I'm not sure if you are asking for help to do what you described in the file that you posted.

Hi Mumps1. I have a few more things to iron out which I am struggling with, hence the questions:

1. The sample workbook which I provided, into which you so kindly inserted the macro, was a slightly simplified version of the one I actually have. I am having problems adapting the macro in the sample file to make it work with the actual one, which has a couple more columns.
2. I'm also quite confused about how to copy a macro from one worksheet to another.
3. I only want to display some, not all, columns in the email.
4. Finally, I'd like to present a total of the lines on each statement.

Small things and if I spend long enough I may be able to work them out for myself.

Originally Posted by Mumps1

When a macro is designed, it is based on the available data. Any change in the data or in the way it is organized, will create confusion for the macro. A macro that works with a sample file most often will not work on an actual file. If you could post a copy of your actual file (de-sensitized if necessary) and explain in detail what you want to do, I can have another look at it.

Hi Mumps1. Real file but anonymised file attached. I am so grateful for your help. It's a charity event so you are doing Good Works

Pretty much as per your previous macro I'd like to, for each participant (other than 999), create an email listing each of his/her payments and if possible a total at the bottom. I only want columns Date, Description and Amount from the payments worksheet.

Thanks again
N

Originally Posted by Mumps1

In the file you posted, column B in the Participants sheet has only one name (John), repeated multiple times. Can I assume that column B in the Participants sheet should have a list of many unique different names? If this is true, please post an updated file with column B in the Participants sheet containing the unique names.

Yes that's right. Column B can have many different name, but they won't necessarily be unique - every chance there would be two Johns for example.
Interested to know why this matters though, as the link between the worksheets is on the reference number, eg 405.
Updated sheet attached.
Thanks!
N

Originally Posted by Mumps1

If we use Charlie as an example, does that mean that he would get two separate emails, one based on Ref# 463 and another based on Ref# 660? This would result in 10 rows of data from the Payments sheet in one email and 8 rows of data from the Payments sheet in the other email. Or do you want one email for Charlie that includes both Ref numbers?

No. 463 isn't really called Charlie Vestey, and 660 isn't called Charlie Vestey either. The references (463, 600 etc are unique identifiers of one and only one person. The fake first and surnames I have assigned are completely immaterial.

Shall I redo the sample?

Here you go.

Originally Posted by Mumps1

Just to confirm: some of the names can be repeated more than once and each occurrence of the name will always have the same Ref number in column A of the Participants sheet. Is this correct?

It's possible that there could be several Johns, and there could be several Smiths, and there could even be several John Smiths. But they would all be different people. The Ref in Column A uniquely identifies one and only one person.

Originally Posted by Mumps1

Just to confirm: some of the names can be repeated more than once and each occurrence of the name will always have the same Ref number in column A of the Participants sheet. Is this correct?

Is there any more info I can provide to help? So grateful for the help so far - I'm 90% there!
Thanks
N

Thanks!
It failed with an error, see attached.
I'll try and work it out myself but if it's obvious please do shout! ;-)
Thanks
N

Originally Posted by Mumps1

I tested the macro on the file you posted and it worked properly. Are you using the macro in the same file or in a different file? If in a different file, please post a copy of the file that is generating the error.

No, I popped it into the WSJ Anonymised v0.3 file which I uploaded a couple of days ago by using Macros->Create-> then cut and pasted your suggestion. I think!

Originally Posted by Mumps1

Because I cannot reproduce the error, it is difficult to find the problem. Could you post a copy of the file that is generating the error and I will try again?

Hi. Thanks for persevering! I downloaded my WSJ Anonymised v0.3 file, posted on 06/08/22 to make sure I had the exact version, and pasted the macro you uploaded on 10/08/22. It comes up with the same error. Here I post WSJ Anonymised v0.4, which contains your macro.

Thanks
N

Thanks, but it still fails:
Attachment 791887

Same error. Here's the file with that change made.

Originally Posted by Mumps1

Try formatting column I in the Payments sheet as 'Number' with no decimal places and try this line of code again:

Please Login or Register  to view this content.

Sadly, same result.

Yes, that's it! In fact removing the filter and running any of the recent versions works fine now. Thank you so much!

I'm furiously trying to learn VBA so I can maintain this. Would you mind explaining briefly what this line is doing?:
With paySH
.Range("A1").AutoFilter 9, v(i, 1)

Finally, is there an easy way to run the macro so that it addresses just one or some rows in the Participants sheet, rather than all? Doesn't really matter if not, I can cancel them in Outlook.

Thanks Again!

Originally Posted by Mumps1

The variable 'v' holds all the values in column A of Participants. v(i,1) represent the first value in column A and it is used to filter column I in the Payments sheet so that only the relevant rows are visible. As the value of 'i' is increased in the loop, the macro processes the next row.
If there is a way of identifying which particular rows the macro will process, then that would be possible. If not, the macro has no way of knowing which rows you want to process.

I could include a new row A which would have an 'X' for those I wanted to process, and blank for all the others I suppose. I'll play with that when I am better at VBA.

Thanks again!

Here's v0.4 again, with an X in the first column. (Would it have been easier to make it the last?)

Thanks. It doesn't quite work but I'll try to work it out myself.

That works beautifully!

I'm using it in anger now!

Ideally the total would be to decimal places. Currently it looks like this:
TOTAL: 1549.5

(rather than TOTAL: 1549.50).

Do you know why Outlook is not displaying my signature, as it does when I create a manual mail?

Thanks!

Run Time Error:13 Type Mismatch
tot = tot + x