VBA code for automatic emails from database entries & extraction from a cell into email

[JDP_excel]

I am trying to make a quote spreadsheet for my friends business. It is a bicycle tour business that involved entering in 12 customers per tour and tracking the progress of payments made and contact details. A few emails need to be sent to each customer depending on a dates in certain cells. The emails themselves are always the same except for some text such as the persons name, dollar amounts and dates which are to be extracted from other cells. This is my problem! If there is anyone out there that knows how to do this?

I've scoured the internet looking at various posts but none seem to cover everything here.

I have attached the spreadsheet I have made so far to help for others to understand.
-------------------------

A few notes about the workbook:
The database of customers is on a spreadsheet labelled 'Clients'.
The database of emails is on a spreadsheet labelled 'Emails'.
The customers email address is always in column F.

Email #1 - Deposit recieved
The deposit amount is entered manually into column N as well as the date of the deposit in Column M. Therefor the email #1 needs to be sent on the date shown in Column M.

Email #2 - Total Payment Due
This email is only sent to the customer if there is still an outstanding amount in Column P. (i.e. the amount outstanding =0). The date to send will always be seven days before the tour start date. (Tour state date is in C1, thus =C1-7).

Email #3 - Marketing follow up email after tour
This email will always be sent three weeks and four days after the tour, i.e. =C1+25

I am still awaiting the email templates from my friend but I will be able to change the correct text in the code at a later date. Currently I need help getting the emails to extract the data from cells so that each customer is addressed by their name and with the correct date and amount regarding their payment. Examples to be used below:

Email 1:
Subject - "Name from Column C", your deposit has been received.
body - Hello "First name from Column C",
Just a short note to let you know that we have received and processed your deposit of "Amount from Column N". We look forward to seeing you on the tour soon!
If you could please make sure that the remainding amount of "Amount from Column P" is finalised by "Date from column T", as this is our final cut off for payments. (Three weeks prior to the tour start date). Blah Blah Blah....

Email 2:
Subject - "Name from Column C", Final Payment is now due.
Body - Hello "First name from Column C",
Just a short note to let you know that the remainding amount of "Amount from Column P" is now due as today is our final cut off for payments. Could you kindly make payment as soon as possible. Blah blah blah…..

Email 3:
Subject - "Name from Column C", thanks for making it a wonderful tour!
Body - Hello "First name from Column C",...Blah Blah Blah…

------
The other thing is that he has told me that for some reason or another customers will not give their email addresses. If this is the case and Column F is empty (where the email address is filled in) AND Column C contains something (Where the customers name is filled in) and email address should be sent to info@blahblahblah.com (can be changed later in the code) as email #4.

Email 4:
Subject - Call "Name from Column C"
Body - Attention! Check the records against "Name from Column C" on Tour date "Date from Cell Reference C1" as no email address has been provided. Their phone number is: "Number from column E".

[JDP_excel]

Anyone?!?!? Really stuck in a hole here!

[cjo]

This project is appears doable, however, I have some questions.
1) How do you plan on having this fire off? My suggestion would be for your friend to manually decide to send out emails, either by pressing a button on the sheet or using develop--macro.
2) A lot of your time-based rules, as stated, seem brittle. If, for some reason, no one is around on the designated send date (or your electricity/internet goes out), I'd think that you would still like to send the email at the next available opportunity. This suggests that you'll need a sheet to log sent emails in order to make sure all emails are sent, and none are sent more than once.

[jaslake]

Hi JDP

Are you using Outlook?

[JDP_excel]

Hi thanks for the response.
I was thinking of having it auto send when the workbook was opened. Is this an option, if so how is this achieved? Is it possible to link it to a pop3 account such as hotmail or gmail? I will have to double check what he has as I can only imagine that it would be easier to setup if it was outlook...

I agree, there are holes in it which I did not see. Thanks for picking that out. I think it is a great idea on having a separate log sheet for the emails.

[jaslake]

Hi JDP

The attached code interfaces with Outlook...I have no experience with this

Is it possible to link it to a pop3 account such as hotmail or gmail?

The code runs from Buttons, not upon Workbook Open...however, that can be modified.

Email 4 is generated automatically from the other 3 when an email address is missing. All emails are set to Display. To automatically send change this line of code in each procedure from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Let me know of issues.

[cjo]

For your questions; There is a workbook open event that can cause code to be ran, of course, the problem is that it's only ran when the wokbook is opened, and of course nobody ever leaves a workbook open on their machine for days at a time . Fully bullet-proof automation can be difficult to do correctly.

It is possible to send via a Gmail account, and most likely hotmail, although I've never looked into hotmail before.