VBA code to Open Outlook

[sdohertyccb]

I have code that sends an email through VBA code in excel. I want to test to see if Outlook is open (not everone will be using this on their primary email box) and if not, to open then send the attachments.
I have simpliefied the code below, would appreciate it if someone can give me the additional code to open this application.
When I run the following code on a box that does not have Oulook open, I get into some kind of a loop with a message that pops up that says "Excel is waiting for another OLE application to finish", which it never does...
Thoughts?
Thanks so much, in advance.

Sub send_test()
Dim Outlook_App As Object
Dim Outlook_Mail As Object

Set Outlook_App = CreateObject("Outlook.Application")
Set Outlook_Mail = Outlook_App.CreateItem(0)
On Error Resume Next
With Outlook_Mail
    .To = "me@xxxx.com"
    .Subject = "Test Email"
    .Body = "Help me!"
    .Send
End With
On Error GoTo 0

Set Outlook_Mail = Nothing
Set Outlook_App = Nothing
    
End Sub

 

[jdsouza]

Good to know that it works for you.

Not sure. Perhaps, you may have to use SendKeys

For ALT FS

When you know, post the right answer here.

 

[pman]

How about:

Shell "taskkill /F /IM outlook.exe", vbHide

 

[sdohertyccb]

Thanks so much pman...
That did the trick.
One other quick question, I want outlook to call another script to send the email, which works great, do I need to use Application.wait for it to send this so it has time to clear the outbox? Is there another command to force it to send this immediately?
I appreciate all the help both jd and pman have given me. This has been a thorn in my side for weeks.

 

[jdsouza]

Does not
.Send

work?

It works for OK me. The mail gets sent in the background.

 

[sdohertyccb]

That works if outlook is left open, but I am trying to open, send and close outlook. When I run the script to close, it remains in the outbox until I re-open and wait for the send/receive sequence to run. Can I force the send/receive so I can shut it down immediately? The reason I want to do this, is I don't want more than one copy of outlook running (one on the primary box, and one on the box running this program). So I am trying to open, send and close outlook to minimize the conflict between the two versions. Sorry for dragging this out, but I am very close to solving this.
I sincerely appreciate all the help.

 

[jdsouza]

This is the code I use (below the dashed line). If Outlook is not open, I can see the outlook icon in the task bar for a few seconds and icon then disappears. The mail is sent in the background. I have Windows 7 and Office 2010.

If Outlook is already open, the e-mail also gets sent and Outlook remains open without any issues.

--------------------------------------------
On Error Resume Next
Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "whatever@whatever.com"
.cc = ""
.BCC = ""
.Subject = "Vetting Report - " & TBFileName.Text
.Body = "For Your Information .."
.Attachments.Add BFld1 & TBFileName.Text
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\more attachments.txt")


.send ' to send in background
' .Display ' to open a mail window with a normal 'SEND' icon available
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing

'' Shell "taskkill /F /IM outlook.exe", vbHide ' to shut down outlook
aa:

 

[pman]

Thanks so much pman...
That did the trick.
One other quick question, I want outlook to call another script to send the email, which works great, do I need to use Application.wait for it to send this so it has time to clear the outbox? Is there another command to force it to send this immediately?
I appreciate all the help both jd and pman have given me. This has been a thorn in my side for weeks.

It should not take long to send. If you are calling from excel then you can use application.wait. otherwise you will need to use other methods. You could try waiting for 2 seconds to see if that helps. Let me know.

Edit:

this will work in excel or outlook. So to wait to seconds, use Wait(2)

Function Wait(Target As Long)
    Dim TimeWait As Date
    TimeWait = DateAdd("s", Target, Time)
    Do Until Time >= TimeWait
    DoEvents
    Loop
End Function

 

[pman]

I have not tested this, but it should work:

Sub sdfffsd()
    On Error Resume Next
    Dim OutApp As Object
    Dim OutMail As Object
    Dim objNsp As Object
    Dim colSyc As Object
    Dim objSyc As Object
    Dim i As Integer

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set objNsp = appOL.Application.GetNamespace("MAPI")
    Set colSyc = objNsp.SyncObjects

    On Error Resume Next
    With OutMail
        .To = "whatever@whatever.com"
        .Subject = "Vetting Report - " & TBFileName.Text
        .Body = "For Your Information .."
        .Attachments.Add BFld1 & TBFileName.Text
        .Send ' to send in background
        ' .Display ' to open a mail window with a normal 'SEND' icon available
    End With

    For i = 1 To colSyc.Count
        Set objSyc = colSyc.Item(i)
        objSyc.Start
    Next

    On Error GoTo 0

    OutApp.Quit

    Set OutMail = Nothing
    Set objNsp = Nothing
    Set colSyc = Nothing
    Set objSyc = Nothing
    Set OutApp = Nothing

aa:
End Sub

 

[sdohertyccb]

Thanks everyone for your help on this. I have been buried in another project that pooped up, I am sure the latest from pman will be great, will try to test it this weekend.
Again, I appreciate your efforts.

 

[sdohertyccb]

That should have said "popped" up. Although pooped up may be appropriate as well.