Code to send Email wont send everytime

**tanktata** · 09-11-2011, 03:29 PM

I have the following code to obtain details from a userform, and then email these details.

Sometimes the email will send, and sometimes the email just sits in the background and wont send.

Most of this code I have borrowed from other sources and tweaked to suit my needs.

Please Login or Register  to view this content.

Any ideas why it doesnt work every time?

**Leith Ross** · 09-11-2011, 03:35 PM

Hello tanktata,

How have you confirmed the email is not being sent?

**tanktata** · 09-11-2011, 03:41 PM

When the code runs, the email pops up and displays, then sends.

When it doesnt work, the email is created, but it stays behind excel, and doesnt send. It is then sendable when I exit excel, or switch to the email.

**Leith Ross** · 09-11-2011, 03:47 PM

Hello tanktata,

Outlook will continue to run in the background when sending emails. It does this because servers don't always process an email request immediately when sent. The delay may be only a few seconds or could be minutes or some cases longer if the server is down. There is no why to know how long it will take a server to process your request.

**tanktata** · 09-11-2011, 03:55 PM

I understand that the email sending is dependant on the server, but I dont think the code is actually sending the email. It creates the email, but never actually sends it.

I think this peice of code is the line which sends the email but sometimes this doesnt seem to work.

Please Login or Register  to view this content.

When the email is visible, this code sends the email. However, when the email isnt visible, this code is unable to send the email.

**Leith Ross** · 09-11-2011, 04:04 PM

Hello tanktata,

The SendKeys method is not very reliable, especially in this instance. You should remove that line of code and user the .Send method in the Outlook email. Here is the revised macro.

On a side note, you should handle errors rather than ignore them. The On Error Resume Next statement should be used to trap an error that may occur when the next line is executed. You then then need to check the Err.Number to see if there was error or not. If there was error, was it one you expected to see? If it is unexpected error then you need to alert the user and exit the macro. Otherwise deal with error. Summarily catching and passing over errors is a very bad practice.

Please Login or Register  to view this content.

**tanktata** · 09-11-2011, 04:33 PM

Thanks for your help Leith. I am not very good with VB and have no experience with error handling. Most of what you have seen I have taken from other sources.

Regarding the SendKeys method, I was using this because without it, there is a security message that pops saying that an email is trying to be sent. I wanted a way around the user having to deal with this security warning.

I could just insert a message box asking the user to click yes to this security message I suppose, but I wanted to avoid it.

Thanks again.

**Leith Ross** · 09-11-2011, 04:44 PM

Hello tanktata,

If you weren't send out emails in HTML, you could have used the Workbook.SendMail method or API SendMail method. Unless you use third party software to send your emails or CDO, which is not easy to configure, you are stuck with the security dialog when send HTML emails.