Send Email Based on Multiple Conditions

[scubab]

Good Afternoon!
I have a complex request that I'm not sure is possible, but I figured I'd look into it.
I am building a workbook that tracks customer phones sent to repair, as well as contacting them about loaner phones.
I have been using this workbook for six years, but I am rebuilding it from scratch with new features. I will upload an unformatted sample of what I'm working on.
In cell D4 I have the customer phone number. To text these customers from our email we add @email.XXXX.net to the end of their phone number, so [email protected]. In cell J4 and N4 there are dates to text the customer. Is there any formula that would extract the number from D4, convert it to [email protected], then post a specific message in the SUBJECT line (no body) depending if it's in reference to J4 or N4 (the pick up time frame will be different [i.e. "Pick up your phone within 15 days" and "Pick up your phone within 5 days"]). If this is even possible with a formula, I'm assuming this will require a hyperlink? This will be shared with multiple coworkers, so I'd prefer to avoid macros as some people may disable them by accident, or IT may not allow them.


Thanks so much for your time!

EDIT: This is for Excel 2010
EDIT2: Whoops, forgot to attach the file

[vamosj]

Yes this is possible.. We can make the formula a little more relative to the situation such as date calculations and IF statements, but here would be an example of what can be done.

=HYPERLINK("mailto:"&D4&"@email.xxxx.net?subject=Pick%20up%20your%20phone","Email")

[vamosj]

Okay, here is what I did. I may have gone a little above and beyond but here is the formula.

=HYPERLINK("mailto:"&D4&"@email.xxxx.net?subject=Pick%20up%20your%20phone%20within%20"&IF(P4-NOW()<=5,"5%20Days",IF(P4-NOW()<=15,"15%20Days",P4-NOW()&"%20Days")),IF(P4-NOW()<=5,"5 Day Email",IF(P4-NOW()<=15,"15 Day Email",P4-NOW())))

What this is doing is it's calculating the date from P4 (Bill Date) and Now to determine if you are within the 15 day or 5 day window (doesn't account for anything that's past due). If it's within 15 days it shows "15 Day Email" under the email column, same with the "5 Day Email" when it hits that point. If it is beyond the 15 day email it shows exactly how many days until the bill date.

[scubab]

Hooyah, thanks FC1!
The second post doesn't seem to change relative to different dates. When I checked the new column it stated 36 days past due in the email. I then updated the Date sent to repair and the date back from repair as today; the email still stated 36 days.

I am on a conference presentation atm and have to do an inventory count, but I am going to try out the first formula once I get some spare time this morning. This one seems very straight forward, I just need to find a place to add it now .

EDIT:
If it helps, the loaner due date is exactly 15 days from the date the phone is back from repair. Even though it'd be awesome to have just one formula to send an email (so that the email can say exactly 12 days, 10 days, or whatever the time may be if we end up sending an email a day or two late), it might make things kind of confusing. I say this because we also have another worksheet that tracks loaners given out of courtesy while people wait for an insurance replacement.

[vamosj]

I based the dates off of the bill date (P4) instead of the two dates in L and N. When you said the 2 referenced cells I looked at the dates you showed I assumed that J4 was 15 days prior and N4 was 5 days prior to the bill date, so instead of basing the formula off of two cells I went with the one in P4. If the Bill date is always 15 days after the Date Back date then we should be good. If not let me know and I'll adjust the formula to match when the phone comes back from repair.


Hooyah!

[scubab]

Alright, I played around with the first formula a bit and I think it will work perfectly for want I want to do. Once I get the book a little more formatted I will share it with some coworkers and see how they like it. If they request the second formula then I will reach out to you again for some help.
Thanks again, FC1, you made my life a lot easier

[vamosj]

Welcome, if you do post for more assistance shoot me a PM and I'll jump to that thread and take a look.


Fair Winds & Following Seas.

[scubab]

I do have an additional request for this formula. If you don't report back here I'll assume you're not subscribed to the thread and I'll shoot you a PM.
Is there a way to leave the cell that houses your formula blank, only showing text if "Date Back" has text in it? I can make this happen with other formulas, but since this one is more complex, I'm unsure how to add an IF statement. I'll attach an updated copy of my progress so you can see how it's performing. The formula works exactly as I wanted, but it's bothering me that I have 100 rows with "Send Text" displayed while the rest of the row is blank. I hadn't even put this into consideration for my original request

Ideally, I'd like to have the Date Back cells hyperlinked to the Send Email formula to clear up a couple columns, but I don't think that's possible without making the workbook too difficult to manage for other associates.


NEW LOANER TRACKING.xlsx

[vamosj]

Right after the = in K4 and P4 put this

IF(J4="","",

Then put another ) at the end of the formula.

What this does is it checks to see if J4 is empty, if it is TRUE then the value comes back as "" If it's false then it will work out the rest of the formula.


K4:
=IF(J4="","",HYPERLINK("mailto:"&D4&"@email.uscc.net?subject=Your%20phone%20has%20returned%20from%20repair,%20please%20stop%20by%20USCC%20within%2015%20days%20with%20all%20loaned%20equipment.","Send Text"))

[scubab]

Yet again you deliver exactly what I need, thanks!