• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

simple code to launch outlook from excel

Hi all,

Could you let me know the code to launch outlook from a macro please?
0
knowvb
Asked:
knowvb
  • 4
  • 3
  • 2
  • +1
1 Solution
 
jeveristCommented:
Hi knowvb,

This site has most of what you would need to know about this:  http://www.rondebruin.nl/sendmail.htm

Here is some sample code:

Dim ws As Worksheet, sh As Worksheet, rng As Range, cel As Range, frg As Range, icol As Long
Dim oOutlookApp As Object, oOutlookMail As Object

Set oOutlookApp = CreateObject("Outlook.Application")

Application.ScreenUpdating = False

Set ws = ActiveSheet
Set sh = Worksheets("Sheet1")

Set cel = [G10]

Set frg = sh.Columns("B").Find(What:=cel, LookIn:=xlValues, LookAt:=xlWhole)
If Not frg Is Nothing Then
Set oOutlookMail = oOutlookApp.CreateItem(0)
With oOutlookMail
.To = frg.Offset(0, 1)
.CC = ""
.BCC = ""
.Subject = "Email from EXCEL"
.Body = "Hello this is a new email from EXCEL"
.Attachments.Add ("C:\EXCEL\TOSEND\myfile.xls")
.Send 'or use .Display
End With

Set oOutlookMail = Nothing
End If

Set oOutlookApp = Nothing

Application.ScreenUpdating = True


Jim
0
 
knowvbAuthor Commented:
Thanks for that jim,

I dont need it to send an email though. Iwould just like outlook to be opened on either the main screen or the calender screen.

Many thanks
k
0
 
jeveristCommented:
knowvb,

OK, try this:

Shell "OUTLOOK.EXE", vbNormalFocus

Jim
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
I was going to say

Sub ol_open()
Dim dummy As Double
    dummy = Shell("Outlook.exe", vbNormalFocus)
End Sub

but I was too slow ... just watch out as it will launch multiple instances.

Chris
0
 
Jeroen RosinkSoftware testing consultantCommented:
Perhaps others can help with this also http:/Q_22867496.html
Since the last comment deals about showing an appointment in the calendar of outlook. And there is also a snippet how to start the outlook from the Shell function.

Though it all works I asume that an easy answer is expected how to show outlook and/or the calendar without the side effects mentioned in that Q.

regards,
Jeroen
0
 
knowvbAuthor Commented:
Thanks for the replies everyone.

The two simple codes above do the trick but as chris pointed out multiple instances can be launched. Is it possible to add to the script to launch outlook if it is not already open and if it is already open then get the script to show the existng outlook instance.
I would also like to know if a simple line can be added to the script to make it open onthe calendar page and also what is the code for minimized open.

Many thanks all.
regards
k
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

Sub ol_open()
Dim dummy As Double
    If Find_App = 0 Then
        dummy = Shell("Outlook.exe", vbNormalFocus)
    Else
        dummy = SetForegroundWindow(Find_App)
    End If
End Sub

Function Find_App() As Long
    Find_App = FindWindow(vbNullString, "Microsoft Outlook")
End Function

Not sure about open minimised!

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Replace vbnormalfocus with your preference, (vbMinimizedFocus  or vbMinimizedNoFocus  I assume):

vbHide 0 Window is hidden and focus is passed to the hidden window.
vbNormalFocus 1 Window has focus and is restored to its original size and position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active window remains active.
0
 
knowvbAuthor Commented:
Thanks for that Chris. It was all very useful.
I have amended it a little as shown below. It seems to work as i need it now.
Just out of interest, could you explain what the declare line is doing please?
Many thanks again for your assistance here Chris.

Regards
k

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Sub ol_open()
Dim dummy As Double
    If Find_App = 0 Then
        dummy = Shell("Outlook.exe", vbMinimizedFocus)
    Else
       
    End If
   userform10.Show

End Sub

Function Find_App() As Long
    Find_App = FindWindow(vbNullString, "Microsoft Outlook")
End Function
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The declare makes a VB reference to a windows function defined in the user32 library.  It is needed because the outlook 2003 (2007 may be improved) application has a lesser interface and cannot switch to or identify a running application via VBA.

It is this same reason that requires the shell function rather than simply simply declaring an instantiation of outlook in the same way as you would for the other MS applications

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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