filter and copy from excel into an e-mail (Body text).

Hi,

I am struggling with a filter and copy from excel into an e-mail (Body text).
I have a spreadsheet with 213 rows in it and I would like this to be filtered by column G. There are 90 different numbers in column G, so I am thinking that a macro would need to run alongside a vlook up to find and filter the data. But if there is another way of doing it then that could be ok instead.
Then for each filtered data to be copied in to the body text of an e-mail and then sent. This could be sent as an attachment but body text would be best.

I can make the e-mail part, but I am having trouble with the filter and copy part. After looking around and getting lots of help from Yudlugar (thanks again), I just don’t seem to be able to work out how to filter the data and copy it into the body of an e-mail.
If anyone has any ideas, It would help me very much.
Below is what I have so far.
Thanks in advance
Rob

Please Login or Register  to view this content.

Can you better describe the nature of your data, the criteria of the filtering, and what part(s) of the data you want copied? If you can post an example data file, that could help.

Hi Alphafrog,

I have attached a sample data file, this only has three different number types in column G, however, there could be 90 different ones.

The spread sheet attached contains the data I would get from a report containing 213 rows (this is only a small sample) This data I would like filter by column G,
and then send the filtered rows or rows to different people.

Please see the attached Jpeg's of the sample 3 e-mails.

Regards

Rob

Wow AlphaFrog, that is amazing.

Thank you so much, this works perfectly.

Rob

Originally Posted by Robert110

Just one thing would it be possible if I could use the vaule in column G for the subect? I tried using

Please Login or Register  to view this content.

But this didn't seem to work
Sorry for the extra question

Rob

"didn't seem to work" is not a helpful description of the problem. Does it error, put the wrong value, does nothing?

Try using just...

Please Login or Register  to view this content.

Sorry I should have explained further:-

When I used

Please Login or Register  to view this content.

and debug is says G2 = empty.

then I tried your suggestion:-

Please Login or Register  to view this content.

and when I debug, it says "Range(G2).Value = <Method 'Range' of object'_Global' failed>

Rob

Sorry. Missing the quotes around G2.

.Subject = Range("G2").Value

Hey Alphafrog,

Sorry to be a pain again, but could I ask one last question?

The 'Lookup Email by ID in column H', would it be possible to move this.

I have made a serperate tab called "E-mail ad" where all the email addresses are stored and updated, would it be possible to look up e-mail address from there?

I tried adding 'Sheet (E-mail ad")' to the code, see below but it didn't seem to work

Please Login or Register  to view this content.

Sorry for the extra question, I didn't think of this until now

Rob

Hi,

Thanks I copied in the new code in, but the line below was highlighted in red

Please Login or Register  to view this content.

Then when I ran it, I got a "compile error: Sub or Function not defined" when I click ok, it highlights the 'Sheet' in the below code

Please Login or Register  to view this content.

Did I do something wrong?

Rob

Hi AlphaFrog,

I can not thank you enough for all your help, this works perfectly

Just for your inforation, I removed the extra ) in the below code. Just incase you needed to know

Please Login or Register  to view this content.

Thanks again

Rob