[vasu0505]

Hi All,

Let me take the pleasure to introduce myself as Vasu, beginner in this forum.

I know that there are many on going threads related to my this new thread. But, actually I had gone through some of the posts (like Rollin, OBP, and Diego) as per my need and I did saw OBP used to share some links which already covers this my new thread, but since I am totally beginner to MS Excel, so I could not understand many of the things. So, with left chance I thought initiating the new thread, so that I can aware of step-by-step to "automatically send an email from excel on date basis". Hope you all fine with this.

So, here is what I need, I have a sheet (which contains columns Request No, Owner, Run Date, Due Date to Close Request). Usually sometimes we miss to close the requests as per the due dates.

So, could you please share detailed information on how can my excel automatically send an email whenever the "Run Date" crosses??

As per my understanding after reading the existing posts, I thought of giving you some sample data from my side. In my attached workbook, there are two sheets ("Request Tracker" and "Email"). "Request Tracker" sheet contains the base data on which "Email" sheet contains what I need in my email when excel send an email.

I would be more than happy to give you any additional information if required.

I use MS Outlook and MS Excel on Windows.

Thanks for your assistance and help to get my problem solved.

Regards,
Vasu

[slurpee55]

A few things. First, I deleted your first rows on both pages. Excel looks for headers in that row but not particularly well in any other row (nor in multiple rows) and you are using headers, so it is always best to have them in the top row. Similarly for the two columns A - Excel looks for data at the far left first. (Yes, I can hear some other old hands saying, "you can work around that", but why make trouble for yourself? You said you were a beginner in Excel.)
I added a simple formula in column G. It is:
=IF(TODAY()>D2,1,"")
Simply put, if today's date (the "TODAY()" part) is greater than the date in D2, it shows a 1, otherwise it shows a null (a blank) - that is, "".
Given that, someone can write some code that looks in column G for any 1s that appear and write the emails based on that.
However, once the date has passed, the email will be sent each time the code is run, even if it has been done before, unless the code also alters the date in column D.

Oh, and I converted it down to a 2003 file because a lot of us still don't have upgrades....

Oh, and welcome to the forum!!!

[vasu0505]

Hi slurpee,

I am really happy to see your quick response.

Though I am new to this forum, but really I like your initial guidelines about the "headers and all"

I completely understand your intention behind your small code in column "G". Now I have added one more column "I" to the sheet named as "Request Tracker".

Now, can anyone please suggest me the code to automatically send an email based on following criteria:

• As slurpee said, based on column "G" value (whenever its "1") and column "I" (if it's in "Open" status), I need an automatic email to be sent to someone/some group email-id with the following details.
  • The entire row from column "B" thru column "F" with the email (a sheet "Email" with sample email format is attached here)
  • Please see NOTE mentioned in sheet named "Email" from my attached workbook.

Below points needs to be considered for request:

• Excel should automatically send the said email with the said content even if the workbook is closed. Is this possible? From the existing post it seems it is possible using some VB macro (but, not sure what is this) I guess.
• Also I need email to be sent on daily basis till the status of column "I" (of sheet "Request Tracker") changes from "Open" to "Closed"
• Please mention the steps in detail so that i can understand it better and can execute it on my own. I know spending that much time will be big problem for anyone, but I really appreciate your efforts.

I am trying to give as much as clear information as per my need and the best I can. But, please tell me if I need to provide any other information.

I am in hurry to get this work done ASAP. So, I will spend as much as time that I can this weekend as well.

I am Really happy by being a member of this nice/interesting Forum...!

Thanks in advance!

[Keebellah]

Hi Vasu,
As were others I was asked to take a look.
If I understand the basics all you need is a code that will check the Excel sheet even if closed to mail the Tracker that teh request for which he / she is reposnsibel must be updated or closed, right?

Any type of code is welcome as long as it is understable. Right?

I'll see if I can put something together as a simple test and get back to you, but you'll have to be patient.

[Zack Barresse]

I agree with slurpee on the data structure - which was actually done up very well, and I certainly wouldn't call you a beginner (it's very difficult to get the thought paradigm of 'good' data structure through to people sometimes). New to this forum, yes, but you're sitting well as far as I can see, with your grasp of data [structure].

This is absolutely possible, let me preface with that. But let me pose the question - why use Excel? Is it just where the data originated from? Do you also have this in Outlook, say, as appointment items? Does the data come from somewhere else? Usually, from my experience, either it originates in Excel, because that's what people are comfortable with, or it came from another source and is merely being manipulated in Excel. Not that creating a solution for you would matter with that information, but there's always the possibility of 'cutting out the middle man'.

You can do this automatically, everyday, with either a batch file or a VBScript file. I generally use the latter, just because I have more of a background from VB, and my DOS-days were a long time ago. Not to say those are the only ways to accomplish what you're asking, just some of the most common, especially with a Windows-based machine.

Now let me ask a few questions. It looks like Keebellah is going to start on your coding, and I don't want to duplicate efforts, but the information will help.

• Which Windows version are you using
• Which version of Excel and Outlook are you using?
• Do you have Outlook Redemption, ClickYes, or an equivalent?
• If not (above), do you have admin rights to install such a program?
• I'm assuming you only want to send the email once, and if the due date is today?
• The 'Email' sheet, so I understand this correctly, only houses the details of the outgoing email?
• Also, the data on the 'Request Tracker' sheet is what should be looked at, and sent (along with the specific details on the 'Email' sheet)?

Whenever I've done this before I generally use Windows Scheduler, which will run a VBS file, which will execute code to open Excel and run a macro in a particular file; this macro will check the data, create whatever emails need to be created, send anything applicable, then close the Excel file. An example is here. Some other useful links are here and here.

HTH

[vasu0505]

Hi Keebellah,

Thanks for the information and your efforts that you will be putting to test the code for me!

To answer your 1st question, Yes, you are right. and for 2nd one is, yes, I am OK with any code that will serve my purpose and as you said, I can understand it.

Note: There was a typo in my below post. It is "based on column "H" value (whenever its "1")" instead of based on column "G" value (whenever its "1").

Also, I just slightly modified the column "B" of sheet "Email" and attached here the same.

Yes, I will wait patient

@Zack: Thanks for your time and assistance.

Yes, data might come from different sources. Since, other people can also update this sheet, I would need to use an excel. I hope I answered your set of questions.

Here is my response for your questions:

• Which Windows version are you using Answer: Windows 7
• Which version of Excel and Outlook are you using? Answer: MS Office 2007
• Do you have Outlook Redemption, ClickYes, or an equivalent? Answer: I have no idea of this. I will update you by tomorrow. I am continuously seeing about these while going thru the existing threads.
• If not (above), do you have admin rights to install such a program? Answer: No. But, for this I can check with my Admin and get back to you by tomorrow. But, what I was wondering is, is it mandate to have anyone of these installed on each resource's (whoever will be using the sheet) machine?
• I'm assuming you only want to send the email once, and if the due date is today? Answer: I want to send an email till the status column gets changed from "Open" to "Closed" (but, once in a day). If the Due Date is today, then also I should get an email. I need an email once in a day till it gets to "Closed" status.
• The 'Email' sheet, so I understand this correctly, only houses the details of the outgoing email? Answer: Yes, you are right. It only contains the outgoing email.
• Also, the data on the 'Request Tracker' sheet is what should be looked at, and sent (along with the specific details on the 'Email' sheet)? Answer: Yes. You are correct.

I will be going thru your provided links and get back to you with what I have/haven't understood.

@ All: Thanks for the support.

Regards,
Vasu

[Zack Barresse]

If you cannot install a third-party program (I recommend the ClickYes, it works very well), then you could always use the CDO method for emailing. Since this file is going to be opened by other people, and emailed using different clients, it's going to be difficult to program for all environments. Since using the CDO method you can set your own email server settings, it makes it easier as it sends from your account regardless. One problem with it, however, is you need to supply a password in your code, which will not be hidden. You can of course put a password on your vba project, but they're not the strongest passwords and can easily be hacked, especially by somebody who knows what they're doing, in a matter of mere minutes. I think I've done it in something like 30 seconds before. Excel is NOT a secure environment.

Now if you know that everyone who will open this file and use the email functionality of it will have the same environment, i.e. Outlook 2007, Excel 2007, Windows XP, etc, then you can code for it. Another drawback is if people do not enable macros. There are workarounds for that as well though, but yet another layer of difficulty, which IMHO means another piece of code to maintain and worry about going awry. Of course I'm sure others may disagree.

Also, if you do want to do this via code, there is no need to setup any additional columns of data in regards to the date. This can be done via code.

[vasu0505]

Hi Zack and Keebellah,

I have put here Keebella as well, as I think below points might be helpful for him.

Yes Zack, I can not install a third party program.

Few things I would like to share in detail are:

• Yes, I know who all are going to access the workbook and they use the same setup as me, ike MS Office 2007, etc...
• But, one thing is, I definitely need to know how I can add/remove an email id from the sending (existing) list once my need gets tested.
• As per your reply, it seems I really might not be in a position to give some work around for issues if any. But, I do feel we no need to worry about it as I know the people who access the workbook.
• Also, as you said, I would like my code to be password protected. But not sure about what you have mentioned like VBA and all, will try to find some information.

About redemption, ClickYes, etc.., I am in a process of getting that informaiton.

Any additional suggestions are deeply appreciated.

Thanks!
Vasu

[Zack Barresse]

The program ClickYes, and it's equivalents, are only for automatically bypassing Outlook's security measures for an external program to send mail automatically without the end-user pressing the Send button. If it's acceptable to you, you could display the email and just tell the user an email will be created and they'll need to click the send button on it. Maybe tell them to verify the data first. When I usually code automatic emails from Excel, I don't want the user messing with it, or give them the potential to mess it up or skew the information. Of course if it's just a simple note, there may not be much to worry about. My faith in the end-user isn't the best. I tend to generally think that if something can be screwed up, the end-user will do it. Heck, I think half my code is usually based on the "what if" of what users may do to break my code. LOL!

There is also the large issue of running on other machines. It's great that you know all those who use this spreadsheet will be using the same version of Excel and Outlook, that's a great start. The other thing is the macro security though, which generally presents a problem. They could have the same everything when it comes to software, but if their macros are disabled, the code created will be nothing more than a backseat ornament that nobody will see.

So if you do want to use Outlook, and their mail settings to send an email, you won't need to put your password in the code. That would only be for sending via the CDO method, which could require you to put in your server settings in the code, which is generally reserved for containing code where the machine running it doesn't have a mail client installed at all. Having Outlook would really negate that issue entirely. There would, however, be the assumption that their email client is setup and running, and they can send emails to outside domains (sometimes this can be an issue with security, generally in larger corporations or those who use Exchange server and hamper the end-user with settings).

About how many people will be using this file? And where would you store the file? Are you certain they all have working Outlook on their computer?

Also, any deleting or altering can be done via code. So long as you provide the logic for it, it can be done. So if you want to get rid of an email address once an email has been dispatched, you'd just need to outline under what circumstances you'd want that done.

[Gupta]

Hi gentlemen.. I was browsing ..believe me just in search of finding solution of similar problem and came accross this site and similar problem.I immediately registered on site and now I am talking to you guys.In fact I was looking for the solution in question by Vasu for the last 2-3 yrs but could'nt.Now I am confident that my problem might be over.If my this problem is solved it would be a great help to me.I am familiar with excel but not with VBA/MACRO etc.So pl write the codes step by step so that persons like me can do on their own. Waiting eagerly

Thanx in advance

[Keebellah]

Hi Gupta, Welcome to the board.
Which codes are you interested in?
Why not create a new post for yourself?

[Keebellah]

Hi Vasu,
I offer my sincere apologies for my long silence. Due to personal reasoms I spent very little time coding etc.
I don't know how far you are now.
What I was thinking is once the Excel vab code is written, and can be called upon via a macro, a vba script triggered by a scheduler like Zack mentioned would do the job, Click yes would overcome the haveing to click a Yes as confirmation and your messages would be sent automatically.
Let me / us know how far you've come so far.

[Keebellah]

Better late than never.

I can manage the following, please disregard the color in cell A2 because that will take quiet some coding.

This would be the result:

Subject line: AOTS CD Reminder: Request 003MR needs to be closed: Due Date: 7-9-2010

Body:
Request Information:
Request No: ER0012345
Description: Request 003MR
Date Created : 25-8-2010
Run Date: 1-9-2010
Owner of the Request: Vasudev


Please verify if the tas is completed sucessfully. If so close the same ASAP.

[Keebellah]

Here's the sheet that is automatically created and could be attached to the emal