Send Email With Macro

  • I'm using the send keys funtion to send a workbook attachment from excel through Outlook. I want to loop until the message is sent. This is what i have for that part, but it doesn't work...I can't figure out how to tell vba that the message was sent. Any help is greatly appreciated.


    Code
    Do Until Windows(Subj & " - Message").Close = True
            Application.Wait (Now + TimeValue("0:00:01"))
            Application.SendKeys "%s"
        Loop


    Thanks,
    Jason

  • Re: Sending Email With Vba Loop Function


    That was just the section of the code I was having a problem with...and I'm stuck with using send keys because I am using a work computer and I don't want to get the alert messages when I try to automatically send emails. This is a bit more of the code:


  • Re: Sending Email With Vba Loop Function


    You can use CDO and avoid outlook entirely


  • Re: Sending Email With Vba Loop Function


    I tried that but it gives me an error message...I think it is because I am on a work computer. Any other suggestions. The send keys function works about 80% of the time...but I want to put a loop in there to keep sending the Alt&S until the message is sent. Thanks for the help.

  • Re: Sending Email With Vba Loop Function


    You probably need to add a reference to the Microsoft Forms 2.0 Object Library


    That single piece of code shouldnt give any errors... what error was it?

  • Re: Sending Email With Vba Loop Function


    Quote from jstevens

    That was just the section of the code I was having a problem with...and I'm stuck with using send keys because I am using a work computer and I don't want to get the alert messages when I try to automatically send emails. This is a bit more of the code:




    Wish somebody could fix this code too. tried others and it seems this one is the only one that can work for me as well, as i'm on a company system too and need to send through outlook just as he ran the code above.


    works just fine for me too... attatches the xls, just doesnt enter the subject, and stalls before send. after i send it it goes back to excel with a error message. if somebody knows how to make this work would be very cool

  • Re: Sending Email With Vba Loop Function


    btw, unless youre setting 'Subj' to a value... you're not setting anything for the subject.


    Try: "mysubjecthere" with quotes, dont do it as a variable

  • Re: Sending Email With Vba Loop Function


    Quote

    For a start I don't see any code there that's sending an email.

    Can't make it too easy for you Norie ;)


    Jason, in addition not assuming we have a Crystal ball, don't use Thread Titles to state what you THINK is your answer!

  • Re: Send Email With Macro


    Sorry, my bad...I think the reason that I can't use CDO is because there are going to be people on many different servers using this. I know send keys isn't the most reliable but I'm stuck at this point. I was just wondering if there is anyway to tell excel that the email was sent and if not keep trying until it is sent.

  • Re: Send Email With Macro


    This seems to work 100% of the time now...not sure if it is the most efficient though.


  • Re: Send Email With Macro


    "Sorry, my bad...I think the reason that I can't use CDO is because there are going to be people on many different servers using this. I know send keys isn't the most reliable but I'm stuck at this point. I was just wondering if there is anyway to tell excel that the email was sent and if not keep trying until it is sent."


    Well, if theyre all running windows 2000 or later you shouldn't have a problem with it. I do something similar at my work - I have an application where when the user is finished and closes the excel file it sends a log file to me via CDO... this has worked on every computer at my work for 20+ people... and these people all have restricted user access on their PCs as well, and no problems at all. (I have the outlook method as the backup, which the log would make note of when used, but it hasnt had to be used once.)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!