Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

Sub test()
Dim oOutlookApp As Outlook.Application
Set oOutlookApp = New Outlook.Application
oOutlookApp.Quit
End Sub

HTH
--
AP

"gti_jobert" a
écrit dans le message de news:
...

Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

If you only need to launch the application, look into Shell.
If you need to interact with it afterwards, use Automation.

Set a reference to Outlook in ToolsReferences, then
Dim MyOutlook As Outlook.Application
Set MyOutlook=New Outlook.Application
With MyOutlook
.....Do something

Make sure you close/quit all objects and set all variable to nothing when
finished.

This will get you started: http://support.microsoft.com/?kbid=220595

NickHK

"gti_jobert" wrote
in message ...

Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

This is an example that sends an email

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
om")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With

Set oRecipient = False
Set oMailItem = False
Set oNameSpace = False
Set oOutlook = False

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"gti_jobert" wrote
in message ...

Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

All the Set object = False at the end should be Set object = Nothing

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Bob Phillips" wrote in message
...
This is an example that sends an email

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
om")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With

Set oRecipient = False
Set oMailItem = False
Set oNameSpace = False
Set oOutlook = False

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"gti_jobert"
wrote
in message ...

Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread:
http://www.excelforum.com/showthread...hreadid=545424

Why do you set to Nothing local variables that will be automatically
destroyed by the End Sub ?

Cheers,
--
AP

"Bob Phillips" a écrit dans le message de news:
...
All the Set object = False at the end should be Set object = Nothing

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"Bob Phillips" wrote in message
...
This is an example that sends an email

Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
om")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With

Set oRecipient = False
Set oMailItem = False
Set oNameSpace = False
Set oOutlook = False

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

"gti_jobert"
wrote
in message
...

Hi all,

Is it possible to launch MS Office Outlook from Excel via a Macro?

TIA


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread:
http://www.excelforum.com/showthread...hreadid=545424

Ardus,

This could go on for ever <g.

Personally, I don't see the point myself (see thread
http://tinyurl.com/okdup for a previous view from me). But I have been
pulled up so many times by others (see http://tinyurl.com/qae6u and
follow-up posts for a particularly asinine thread), that I just add it as a
de-facto now. It doesn't hurt, and doesn't take much time.

Regards

Bob

PS Where about in France are you loacted? Somewhere nice I hope.


"Ardus Petus" wrote in message
...
Why do you set to Nothing local variables that will be automatically
destroyed by the End Sub ?

Cheers,
--
AP

In his book "Advanced VB6", Matt Curland, one of the programmers
of VB (he wrote, among other things, the Intellisense popup
feature) writes that it is absolutely unnecessary to set a local
variable to Nothing. VB will automatically destroy and clean up
whatever is necessary.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob Phillips" wrote in message
...
Ardus,

This could go on for ever <g.

Personally, I don't see the point myself (see thread
http://tinyurl.com/okdup for a previous view from me). But I
have been
pulled up so many times by others (see http://tinyurl.com/qae6u
and
follow-up posts for a particularly asinine thread), that I just
add it as a
de-facto now. It doesn't hurt, and doesn't take much time.

Regards

Bob

PS Where about in France are you loacted? Somewhere nice I
hope.


"Ardus Petus" wrote in message
...
Why do you set to Nothing local variables that will be
automatically
destroyed by the End Sub ?

Cheers,
--
AP

Hi all, thanks for the advice!

I have the following code;

The probolem is that the do and for loops will NOT be executed as
Outlook doesn;t seem to be opened! If I open Outlook first then run
this Function then it works a treat! Any ideas bob....or anyone else?
TIA


Code:
--------------------

Sub SendAnEmailWithOutlook(CurrFile)

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailSent As Boolean, countMsg%
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim lngRow As Long
Dim intAtt As Integer
Dim wbkTemp As Workbook
Dim strTempFile As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.GetDefaultFolder(olFolderSentMail)

countMsg = olFolder.Items.count

With olMail
.To = "
'.CC = "
.Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
.Attachments.Add CurrFile & ".xls"
.Display
.OriginatorDeliveryReportRequested = True
.ReadReceiptRequested = True
End With

Do
Loop Until olFolder.Items.count = (countMsg + 1)

'go thru all mail in Sent Items
For Each olMail In olFolder.Items
' only check those with attachments
For intAtt = 1 To olMail.Attachments.count
' only those with xls files
If InStr(1, olMail.Attachments(intAtt).FileName, ".xls", vbTextCompare) 0 Then
' get folder and filename for xls file
strTempFile = ThisWorkbook.Path & Application.PathSeparator & olMail.Attachments(intAtt).FileName
' save it so we can open and read it
olMail.Attachments(intAtt).SaveAsFile strTempFile
Set wbkTemp = Workbooks.Open(strTempFile)
If Right(CurrFile, 13) & ".xls" = olMail.Attachments(intAtt).FileName Then
MsgBox "Mail Has Been Sent!!"
End If
' close and destroy temporary excel file
wbkTemp.Close False
Set wbkTemp = Nothing
Kill strTempFile
End If
Next
Next

Set olFolder = Nothing
Set olNamespace = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

You could try making Outlook visible


olFolder.Display

after setting that variable

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"gti_jobert" wrote
in message ...

Hi all, thanks for the advice!

I have the following code;

The probolem is that the do and for loops will NOT be executed as
Outlook doesn;t seem to be opened! If I open Outlook first then run
this Function then it works a treat! Any ideas bob....or anyone else?
TIA


Code:
--------------------

Sub SendAnEmailWithOutlook(CurrFile)

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailSent As Boolean, countMsg%
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim lngRow As Long
Dim intAtt As Integer
Dim wbkTemp As Workbook
Dim strTempFile As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.GetDefaultFolder(olFolderSentMail)

countMsg = olFolder.Items.count

With olMail
.To = "
'.CC = "
.Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
.Attachments.Add CurrFile & ".xls"
.Display
.OriginatorDeliveryReportRequested = True
.ReadReceiptRequested = True
End With

Do
Loop Until olFolder.Items.count = (countMsg + 1)

'go thru all mail in Sent Items
For Each olMail In olFolder.Items
' only check those with attachments
For intAtt = 1 To olMail.Attachments.count
' only those with xls files
If InStr(1, olMail.Attachments(intAtt).FileName, ".xls", vbTextCompare)
0 Then
' get folder and filename for xls file
strTempFile = ThisWorkbook.Path & Application.PathSeparator &
olMail.Attachments(intAtt).FileName
' save it so we can open and read it
olMail.Attachments(intAtt).SaveAsFile strTempFile
Set wbkTemp = Workbooks.Open(strTempFile)
If Right(CurrFile, 13) & ".xls" = olMail.Attachments(intAtt).FileName
Then
MsgBox "Mail Has Been Sent!!"
End If
' close and destroy temporary excel file
wbkTemp.Close False
Set wbkTemp = Nothing
Kill strTempFile
End If
Next
Next

Set olFolder = Nothing
Set olNamespace = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile:
http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424

Ardus' question "Why do you set to Nothing local variables that will
be automatically destroyed by the End Sub ?" - I've noticed object
models which I've used have some pretty serious memory leaks when I
don't explicitly destroy objects. As Bob says, it doesn't hurt, but
it's good practice and it can be positively beneficial.

My two cents worth...
Col :)


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=545424

Set olApp = CreateObject("Outlook.Application")
Should open Outlook.

--
Regards,
Tom Ogilvy


"gti_jobert" wrote:


Hi all, thanks for the advice!

I have the following code;

The probolem is that the do and for loops will NOT be executed as
Outlook doesn;t seem to be opened! If I open Outlook first then run
this Function then it works a treat! Any ideas bob....or anyone else?
TIA


Code:
--------------------

Sub SendAnEmailWithOutlook(CurrFile)

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailSent As Boolean, countMsg%
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim lngRow As Long
Dim intAtt As Integer
Dim wbkTemp As Workbook
Dim strTempFile As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.GetDefaultFolder(olFolderSentMail)

countMsg = olFolder.Items.count

With olMail
.To = "
'.CC = "
.Subject = "Textron Schedule Agreements: " & Right(CurrFile, 13)
.Attachments.Add CurrFile & ".xls"
.Display
.OriginatorDeliveryReportRequested = True
.ReadReceiptRequested = True
End With

Do
Loop Until olFolder.Items.count = (countMsg + 1)

'go thru all mail in Sent Items
For Each olMail In olFolder.Items
' only check those with attachments
For intAtt = 1 To olMail.Attachments.count
' only those with xls files
If InStr(1, olMail.Attachments(intAtt).FileName, ".xls", vbTextCompare) 0 Then
' get folder and filename for xls file
strTempFile = ThisWorkbook.Path & Application.PathSeparator & olMail.Attachments(intAtt).FileName
' save it so we can open and read it
olMail.Attachments(intAtt).SaveAsFile strTempFile
Set wbkTemp = Workbooks.Open(strTempFile)
If Right(CurrFile, 13) & ".xls" = olMail.Attachments(intAtt).FileName Then
MsgBox "Mail Has Been Sent!!"
End If
' close and destroy temporary excel file
wbkTemp.Close False
Set wbkTemp = Nothing
Kill strTempFile
End If
Next
Next

Set olFolder = Nothing
Set olNamespace = Nothing
Set olMail = Nothing
Set olApp = Nothing

End Sub

--------------------


--
gti_jobert
------------------------------------------------------------------------
gti_jobert's Profile: http://www.excelforum.com/member.php...o&userid=30634
View this thread: http://www.excelforum.com/showthread...hreadid=545424