Thread: Opens Outlook from Excel using VBA and send emails

Anybody able to assist on this one?

Thanks

Any one able to assist?

The current code opens outlook and passes what's in the code to corresponding fields in Outlook.

However, what I would like it to work is:

1) Open outlook and fill the corresponding fields in Outlook
2) have user manually hit "Send" button in Outlook.
3) have the macro close that instance of Outlook.

Is this doable?

Private Sub EmailtoTeam_Click()
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strBody As String

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

    strBody = "Hi" & vbNewLine & vbNewLine & _
    "I have updated the tracking spreadsheet" & vbNewLine & vbNewLine & _
    "Please let me know if you have any questions." & vbNewLine & vbNewLine & _
    "Cheers!"

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = "test@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Tracking sheet updated"
        .Body = strBody
        
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    
End Sub

Thanks

Wow nice! That's an easy fix.

When it opens the Outlook, it actually opens the "Appointment" window?

Is there any way to have it open the "Message" window with email signatures and everything?

Thanks

Private Sub EmailtoTeam_Click()
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strBody As String

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

    strBody = "Hi" & vbNewLine & vbNewLine & _
    "I have updated the tracking spreadsheet" & vbNewLine & vbNewLine & _
    "Please let me know if you have any questions." & vbNewLine & vbNewLine & _
    "Cheers!"

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = "test"
        .CC = ""
        .BCC = ""
        .Subject = "Tracking sheet updated"
        .Body = strBody
        .display
        
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    
End Sub

wow nice, I wasn't aware of that.

so I guess

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

by default creates appointments?

Thanks

oh and.....I guess the code just creates a new instance of Outlook and from there displays the email window.

What if....the Outlook is already open? I don't want it to create a new instance of Outlook if the Outlook is already running. In this case, is it possible to have it display an new email window from existing Outlook application?

Originally Posted by OnErrorGoto0

It will use an open outlook instance.
The 0 means mail item, 1 means appointment.

ah good to know indeed.

thank you

Hi,

I am wondering if I can put a check in the code and have it first check to see if the Outlook is already open. If the Outlook is already running, then just open up a new email window from it and execute the rest of the code. If Outlook is not yet running, first start Outlook and from it open up an "Email" window and execute the rest of the code.

Is this possible?

THanks

Sorry should have been more clearer:

Currently, if the Outlook is already running, while the code does open the email window, it has to open a new instance of the Outlook along with it. I think this is because the code itself actually creates a new Outlook object and from it creates an appointment type of "0"?

So, I guess the more times users click on the button, the more instances of Outlook are created, and this might be a problem down the road now as I see it. (users probably just want to click on it once and if the Outlook is already running, open the appointment type of 0 instead of creating a new Outlook object). (Typically, users will have both Excel and Outlook running at the same time here...)

Do you have any suggestions on this?

Thanks

Nice!

Yeah, I believe I added that shell statement initially because I wasn't understanding how the code creates the Outlook as an object, but thank you and it's working like a charm.