Automated Email VBA from Excel - Freezes before send

[ELeGault]

This may be a lot to ask of anyone to investigate. I have a Macro that I built up over a couple weeks that does exactly what I need. Refreshes my data, Creates an image of a range, Pastes that image into an email that is structured in the VBA and sends it to desired people with appropriate fields. Updates the last run time on the main sheet, Saves the open book, then saves it to a shared location without the macros, Then Closes.

Runs beautifully when I hit play. The issue comes when I allow windows task to run it for me at 1 in the morning. For some reason it is freezing before sending the paste command. I come in and the email is sitting open waiting to be sent without the image pasted!? Sometimes (Most of the time) the email will then send without the image before I have a chance to click/edit and/or close it.

My question is first blanketed without looking at the code - are there known causes of this behavior?
If not, or even if so... feel free to peruse through my code and poke holes in it so that it can be enhanced please.

Thanks,

So to start everything Windows has a task to open the document at 12:59a

Then I have in "ThisWorkbook" the following Private sub to run on time value XX

Please Login or Register  to view this content.

Then in Module 1 is the macro list of actions as follows

Please Login or Register  to view this content.

[ELeGault]

Let me know if you have questions about any of the Sub's within that code - should be straight forward and I added some comments within it so shouldn't have any but always glad to answer questions. Just hoping someone here will have a finger to point at what I did wrong that would cause the full automation to fail/freeze like it does. Like I said it runs beautifully when I force run the macros, which is why I cannot wrap my head around why it keeps failing..

[ELeGault]

Someone has got to know VBA enough to see the issue with that. I know it is a working model, but something is killing the automation and I myself just cannot see it. Even if your throwing noodles at the wall, please share your thoughts!

Thanks

[cory0789]

May I ask why you are using sendkeys??? is the data that is going into the email not in excel??? I will continue looking at your code to see what is going on.. I am trying to get it to run on my computer now...

I think it is the sendkeys or the image that is causing it to run poorly automated... not positive until I see the workbook I would try using the code I provided and instead of copying and pasting using sendkeys just let vba do all of the sending... if you must create an image make the image on a new sheet and hide the sheet like you do with the other one and simply call the image to be pasted into the BODY section of the email below... not sure if this helps

Please Login or Register  to view this content.

I really need your document to figure out why you are using images and all of that... it is really confusing the way you have it setup unless we can see what exactly you are trying to do...

[ELeGault]

Absolutely - Let me scrub the doc so it is not any company specific so I don't get in trouble then I can share the actual file - Will change save paths to a Folder on desktop so that it can run fine. The send keys are to paste on the fourth line of the email body. And I make the image of the range so that when some of our execs read it on their different mobile devices they can zoom without it playing games and wrapping odd like it does with a straight range paste. The send keys was my answer as well as getting it to paste on the fourth line in the middle of the HTML body.

[ELeGault]

Here's hoping you find an error in the way I code this. It's technically the first actual VBA I wrote without recording. so I would not be shocked if there are issues with it and/or if there are better approaches.

Thanks

TEST.xlsm

[ELeGault]

Will be in and out throughout the day to see if you have had a chance to play with the file. Let me know if it does not run on your pc... Should (As long as you update the save path in the SaveCopy Sub).

Thanks

[cory0789]

sorry wrong post reply I will look more into later

[ELeGault]

NOOO lol got me all excited - cool cool, thanks Cory

[cory0789]

So the only thing I can find is it doesnt like the copy picture command when running from task scheduler... why I have no idea... I will continue to try to help you out but hopefully someone with a little more knowledge of this can chime in.

[ELeGault]

Awesome, well glad it worked after scrubbing for you, I love how it functions as it can inject the image to the right line... just hate that it is locking up under an automated run... About to test running a separate workbook to see if task scheduler can launch a "Schedule book" to then open up another sheet and run from there...

Fingers crossed, Thanks again for taking a look, if you have recommendations on any of my code feel free to put it on blast. Anything to make it more efficient is always great.

[cory0789]

TEST.xlsm


Try that out, you will have to edit your email into the send email portion and uncomment out your other stuff in the workbook open part but I think this will do what you are wanting...

You can resize the picture that is sent also...

Let me know if it works or not for you

[ELeGault]

So it failed (My Schedule run utilizing a second sheet)

Your version this chart (While it does seem to run), I have/had a working model of this setup as well, the visuals when it came to a mobile device was less than optimal in an email body. There has to be a way to use an actual image or send that to an email body... The next step or option I think is to preform the email in Outlook and make a macro pull the image after excel exports/saves a copy of it. I have not created VBA in outlook yet so that will be fun...but may be the last resort answer.

[ELeGault]

gah still racking my brain... coming to the conclusion that it cannot be done as an actual Image file and they are going to have to deal with a blurry chart... still hopeful someone will have a workaround and/or know why task scheduler differs from force running the macro

[ELeGault]

So I am utilizing the SendRange to HTML through temp file until I can figure this out... - Still not ideal as this looks bad on the Mobile devices -

Hopeful that someone here has faced this or a similar situation and will have a solution for inserting an actual IMAGE into the body of an email (Not through Chart Objects) -

[ELeGault]

There is a method to take a clipboard and save it to image (Essentially export your range as an image file) - worked out for 32 or 64 bit Office - see here