?
Solved

simple code to launch outlook from excel

Posted on 2007-10-05
10
Medium Priority
?
1,019 Views
Last Modified: 2013-12-26
Hi all,

Could you let me know the code to launch outlook from a macro please?
0
Comment
Question by:knowvb
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 38

Expert Comment

by:jeverist
ID: 20025055
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
 

Author Comment

by:knowvb
ID: 20025093
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
 
LVL 38

Expert Comment

by:jeverist
ID: 20025191
knowvb,

OK, try this:

Shell "OUTLOOK.EXE", vbNormalFocus

Jim
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20025269
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
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20025733
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
 

Author Comment

by:knowvb
ID: 20027604
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 20027670
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20027678
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
 

Author Comment

by:knowvb
ID: 20028976
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20029539
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question