Automatically Email Rows Based on Cell Contents and Conditions, Possibly Via Mail Merge

[dmcgetti]

Good Afternoon!

I have a spreadsheet where I track return requests and this is how I envision the process working:

A new row is enter into the sheet, column "W" is automatically populated via a formula with either "YES", "NO", or "Need Manager Approval". If the column contains "YES" an email is generated that contains info from the row formatted one way, if it contains a "NO", a different email is created that has info from the row, like a mail merge. If it contains "Need Manager Approval" nothing happens until it is manually changed to "YES" or "NO". I would also like to be able to change an already "processed" "NO" to a "YES" and generate the "YES" email, if for some reason management changes their mind.

I guess what I want to do is to monitor column "W" and once the row contains "YES" send the email and stop monitoring.

I hope this makes sense, it has been a long day and my head is throbbing....

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

InvoiceNumber

PartNumber

Quantity

ReasonForReturn

SalesmanEmail

CustomerName

CustomerNumber

Zone

ShipTo

ShipToAddress

ShipToCity

ShipToState

ShipToZip

Bin

PartNumber2

PartDescription

EmployeeNumber

InvoiceDate

SalePrice

Cost

PO

OK

MRANumber

D40609HM0A

NEW RETURN

TEST@EMAIL.COM

VANGUARD CAR RENTAL USA INC

Z3204000

2NDRUN NCR PHILA

7001 ESSINGTON AVE

PHILADELPHIA

PA

3330C

D4060-9HM0A

PAD KIT-DISC BR

17VMH21173

Need Manager Approval

NEW RETURN

TEST@EMAIL.COM

PARKWAY A B OF NUTLEY

Z6629000

1330C

APPLIQUE

YES

100001

NEW RETURN

TEST@EMAIL.COM

** RELIABLE AUTO BODY

Z8260603

321D3A

BRACKET

NO

T6653AA

CORE

TEST@EMAIL.COM

BOB HOPE AUTOMOTIVE

Z8885844

C114C C115C C114D

T6653AA

TRANS

2012 ECONOLINE

YES

100002

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: #339966"]Date[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffcc]#ccffcc[/URL] , align: right"]3/13/2018[/TD]
[TD="align: right"]3238785[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]66[/TD]

[TD="align: right"]00000[/TD]

[TD="align: right"]574[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100000[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]3/13/2018[/TD]
[TD="align: right"]3285415[/TD]
[TD="align: right"]95486483[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]00000[/TD]

[TD="align: right"]95486483[/TD]

[TD="align: right"]139[/TD]
[TD="align: right"]1/26/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10080[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3/13/2018[/TD]
[TD="align: right"]3353137[/TD]
[TD="align: right"]95245217[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]00000[/TD]

[TD="align: right"]95245217[/TD]

[TD="align: right"]139[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2115[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]3/13/2018[/TD]
[TD="align: right"]3351080[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]00000[/TD]

[TD="align: right"]2230[/TD]
[TD="align: right"]3/6/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

 

[dmcgetti]

No takers, or is what I am looking for not possible?