Launch Outlook, send email, close Outlook - from Excel

[JimJohnson]

The first two parts of this macro work just fine, it's the closing part of it which doesn't work. The strange thing is, if I isolate the close part of the macro and run it as it's own macro, it will work. However, it will not work as part of a larger macro, or even if called from a higher macro which contained the open portion. Here is my code:

Sub launch_send_close_outlook()

'launch Outlook
Dim Outlook As Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = Outlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
Outlook.Explorers.Add Folder
    
    'send email
    Set myOlApp = CreateObject("Outlook.Application")
    Set mailItem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = mailItem.Recipients.Add("name@domain.com")
    mailItem.body = "testing"
    mailItem.Subject = "test email"
    mailItem.Send

'close Outlook
Set Outlook = CreateObject("Outlook.Application")
Outlook.Quit
Set Outlook = Nothing

End Sub

If anyone can explain what I'm doing wrong with the last part, I would appreciate it. Also, if any other part of my code could use some cleaning up, feel free to let me know.

Thanks!

 

[JimJohnson]

This is getting pretty buried.

No ideas?

 

[Ruddles]

I suspect it's where you do the second Set Outlook = CreateObject("Outlook.Application"). You're destroying the value of the Outlook object which opened the application.

Remove that line and try again.

 

[JimJohnson]

Good call.

Also, I think that something is funky with the way that I was opening Outlook. I don't exactly understand what I was telling it to do, I just spliced that code in there and it did what I wanted, so I used it.

I think I need to do some work on that part of the macro.

 

[Ruddles]

I just spotted - you're opening it three times! This is a bit tidier:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Sub launch_send_close_outlook()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]'launch Outlook
Dim myOlApp As Outlook.Application
Set myOlApp = CreateObject("Outlook.Application")[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Dim ns As Outlook.Namespace
Set ns = myOlApp.GetNamespace("MAPI")[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Dim Folder As Outlook.MAPIFolder
Set Folder = ns.GetDefaultFolder(olFolderInbox)[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Dim MailItem As Outlook.MailItem
Dim myRecipient As Outlook.Recipient
    
    'send email
    Set MailItem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = MailItem.Recipients.Add("[EMAIL="name@domain.com"]name@domain.com[/EMAIL]")
    MailItem.body = "testing"
    MailItem.Subject = "test email"
    MailItem.Send[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]'close Outlook
myOlApp.Quit
Set myOlApp = Nothing[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]End Sub[/FONT]