Search text in a column and send an email with all the data in that row

[Philippecolass]

Hi all,

I have a file with around 55 columns and 400 rows.

All I need is that the data from columns A, AA, AG, BA, BB to be sent to my email including the headings of those columns that are located in row 4. This is based on the data in column BB that has text "Delivered". So those with "Delivered" should be copied.

A AA AG BA BB
4 No. ATA PORT ATA DEM DEL
5 123 12-09-2016 12-02-2016 2 Delivered
6 234 12-08-2016 12-03-2016 5
7 567 12-07-2016 12-04-2016 7
8 890 12-06-2016 12-05-2016 8 Delivered

Thanks a lot!

Phil

[sourabhg98]

Assuming your data is in sheet named "Data" try this macro-
(remember to enter your email in the macro)

Please Login or Register  to view this content.

[Philippecolass]

Hi!

Thanks a lot for this. Unfortunately it gives me a 400 error.

I tested it by replacing this :

tr = Sheets("rough").Range("A" & Rows.Count).End(xlUp).Row
Sheets("rough").Select
Range("A1:E" & tr).Select
Selection.NumberFormat = "@"

For:

"ActiveSheet.Range("A1:E112").Select"

to select the data in the "rough" sheet. It worked very well. However, the selection is not flexible as you can see. So, I assume that the error is in that part that I replaced.

Additionally, the sheet you call "Data" changes its name every week. Is there any way to work around this?

[sourabhg98]

I don't know why it gives an error for you. I have tested it and it is working perfectly.

Can you attach the worksheet where it is giving an error?

[Philippecolass]

Here

Thank you!

[sourabhg98]

Try this-

Please Login or Register  to view this content.

[Philippecolass]

Thanks! It works now

[sourabhg98]

Glad to know that helped!
Have a nice day