Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1599
  • Last Modified:

Excel Vba - Outlook Mail Routine - Mail in Outbox and verify it is open

Hello Experts:

I have a routine in VB to email out but I have an issue with the email staying in the Outbox and not automatically being pushed.  It will eventually send if I wait long enough if Outlook stays open or when I re-initialize it. (Since it is set to close Outlook, if it was not Open)

Is there a way to get this routine to give it a little nudge or kick to push it? This will resolve my problem.  Also, I want to verify that the code is correct to check if there is an instance of Outlook open and if not - Open it, send mail and then close it.  (But to empty the Outbox first)

All my end-users are using Office 2010 (Outlook & Excel)

Thank You!
Michael

Code:

Sub CheckOutlookSend()

    On Error Resume Next
   
    Set oOutlook = GetObject(, "Outlook.Application")
         
    Dim OutApp As Object
    Dim OutMail As Object
         
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
           
    On Error GoTo 0
     
     With OutMail
     .display
     .To = "mknierim@classicburgers.com"
     .Subject = "MonthEnd Data"
     .Attachments.Add ActiveWorkbook.FullName
     .send
     End With
         
     Set oOutlook = Nothing
     Set OutMail = Nothing
     Set OutApp = Nothing
         
End Sub
0
mike637
Asked:
mike637
  • 3
  • 3
1 Solution
 
ScriptAddictCommented:
This is by design.  

It requires sendkeys to get around it.

Honestly the best way is to not use outlook, but to use SMTP.

Then as long as the computer it is on is allowed to relay and you have the correct connection string it works much smoother.  

If you're worried about getting a copy to your email box, just cc yourself on all the emails.

-SA
0
 
mike637Author Commented:
Experts,

How about this - can I modify the code to keep Outlook Open and not auto-close it immediately, similair to how it is if an instance of it is already running?

I then can add a Msg Box to say not to close Outlook.  Which leads me to ask - is there anyway to have a Msg Box appear - and stay on screen for 10 seconds and then close and exit the routine?

But on the Outlook - I am too ignorant to see where it closes it. So I need some guidance on what to rem out or change.  And then the issue with a time-delayed MsgBox.

Thanks,
Michael
0
 
ScriptAddictCommented:
Mike,  

Because of all the spam that goes out these days, Outlook has been designed to make automatic sending of emails basically impossible without human or sendkeys intervention.  

I highly recommend using SMTP to send your emails instead of Outlook.  There is no good reason to directly use Outlook.

here is a block of SMTP code for sending email.  The only thing you need is a user name and password for the SMTP mail server, and permission to be able to relay.

-SA

Function CDO_Mail_Small_Text(email As String, ttw As String)

On Error GoTo emailnotsent

    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Debug.Print email
    Debug.Print ttw
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "SMTP.email.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
            .Update
        End With
    
    'Prevent alerts from displaying to users
    Me.Application.DisplayAlerts = False
    'turns off screen updating for speed gains
    Me.Application.ScreenUpdating = False

    
    strbody = "this sends an HTML email  so <b> HTML codes</b> can be used."
              '"This is line 3" & vbNewLine & _
              '"This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = email
        .CC = ""
        .BCC = ""
        .From = """sender"" <sender@email.com>"
        .Subject = "How much better SMTP is then Outlook for sending emails automatically"
        .HTMLBody = strbody
        .Send
    End With
    
   
    'Activates alerts displaying to users
    Me.Application.DisplayAlerts = True
    'turns on screen updating for speed gains
    Me.Application.ScreenUpdating = True
    Exit Function
    
emailnotsent:
    'Activates alerts displaying to users
    Me.Application.DisplayAlerts = True
    'turns on screen updating for speed gains
    Me.Application.ScreenUpdating = True
    On Error GoTo 0
End Function

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
mike637Author Commented:
I will give this a try and adjust with pertinent username(s), password(s).

But where in the code does it state to include the attachment.  This was the whole basis of the routine to take the newly created workbook (after copying the sheet out of a workbook into a new workbook as a single sheet - then kill it at the end of the routine).  The attachment is the most important part.

I am confused on this attachment piece - please advise.

Thank you,
Michael
0
 
ScriptAddictCommented:
.AddAttachment "path\filename"

in the imsg part.
0
 
mike637Author Commented:
ScriptAddict:

Thank you for your help and education into this new area for me.

Michael
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now