Remove the error and clean my code to send mail automatically from excel

Dear outlook Experts,
I want to send the mail from excel automatically on a particular day on birthday. The program is working fine but only I have to open the outlook first and then excel to send the mail automatically. Actually I don't want to open the outlook before sending the mail from excel. I want to open the excel file only and the mail should deliver automatically from excel only and no need to open the outlook first.

Please see the file and solve my problem. Please don't change the program because it's giving me desire output. Only change the code to send the mail from excel without opening the outlook first.

Thanking you in anticipation.

Mukesh

I dont think you can do that.
If you want to send an email without Outlook being opened, you will need to use the SMTP object or the CDO object.

Hello jason_lee_91,
Thank you for guidance. Please tell me which is the best option to send mail from excel without opening outlook automatically. Is there anything wrong in code? Tell me the setting to achieve the target.

Waiting for your guidance.

Thanking you in anticipation.

Mukesh

I dont think there is anything wrong with your code as much as it requires Outlook to be open while you do not want Outlook to be open.
Just slightly different from what you need.

It depends a little bit on your needs.
Mainly, if you need security, this might not be such a good way because it requires you to write your password in the code.
Alternatively, you could have it ask the user for a password.
but thats not fully automated.

In anycase, this is the way I send my emails.
If you are at home, you can use for example gmails smtp server.
If you are at work, you can use the company's smtp server.
the VBA. Not whatever "Blat" is.
http://www.dbforums.com/microsoft-ac...t-outlook.html

One thing I myself might do differently is instead of using On Error GoTo lblLoop, I would probably find out why it throws an error and use an if statement.
Its just that currently, any errors caused by that formula would stop the email from being sent.

there could be many things that trigger an error.
It could just be that the input was in the wrong format.
If thats the case, maybe the emails will never get sent and you might not know that there is actually a problem in your code.
But thats entirely just my opinion/preference.

Also, I have heard that people feel that GoTo statements are slightly harder to read in many cases.

Hello jason_lee_91,
As per your suggestion it's not a safer way. At present only I have to open the outlook before opening excel file to send the message. It's better than inserting password in code. I think that I shall continue the present code and system to open outlook first then excel.

Give me your opinion on it so that I will close this thread.

Thank you.

Mukesh

Sounds like a good idea to me.
You could even have Excel open up Outlook and then run the macro after waiting a certain time (since you need to give outlook a chance to open)

If you put this inside the same module as your "Anniversary" sub-routine, it shoudl work.

Please Login or Register  to view this content.

Hello jason_lee_91,
Really it's good idea to open the outlook from excel but I want some modification in it. I don't want to run 2 macros for a single task. I want to click on anniversary macro then first the macro will open the outlook and run anniversary then. After certain time for example 1 minute the outlook will automatically closed. It will save my time to close the outlook after running the macro manually and speed up the task.
Please see the attachment.

Mukesh

First, about putting your code together.
The Shell command tells your computer to open Outlook.
And Outlook takes a couple seconds to load.
So you have to wait a bit before you run the code.
If you look at the Application.OnTime method, what it is doing is it calls another macro at a certain time.
It does not pick up where it left off.
So if you want to put the two pieces of code together, you would have to think of a new method of waiting for Outlook to open.
I have seen some people do this with loops but I have never used methods like this before.
But if you Google it, there are tons of results.
I just personally dont like them is all.

Second, for closing Outlook.
Unfortunately, there doesnt appear to be a close command for Outlook.

These are some of the reasons I usually use the SMTP object.
The way I deal with passwords, is I put an input box for the user to enter an email and a password.
Then, if there is any sort of error during the send command, I check for the error code and if it is a connection issue, I just stop the macro.
That way, the password isnt actually stored in the code.
The caveat of this is that you have to actually be at your computer.
But since you are going to run the Anniversary macro yourself anyways, I dont think this is a problem.

Hello jason_lee_91,
Thank you for your guidance. Now my problem is solved.

Mukesh