LinkBack URL
About LinkBacks
Hi all,
Is it possible to launch MS Office Outlook from Excel via a Macro?
TIA
Opening MS Applications
Hi all,
Is it possible to launch MS Office Outlook from Excel via a Macro?
TIA
Sub test()
Dim oOutlookApp As Outlook.Application
Set oOutlookApp = New Outlook.Application
oOutlookApp.Quit
End Sub
HTH
--
AP
"gti_jobert" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> 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 Tools>References, 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 = _
oMailItem.Recipients.Add("[email protected]")
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:OZ%235lH%[email protected]...
> 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 = _
> oMailItem.Recipients.Add("[email protected]")
> 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" <[email protected]>
wrote
> in message news:[email protected]...
> >
> > 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
> >
>
>
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
Please Login or Register to view this content.
Why do you set to Nothing local variables that will be automatically
destroyed by the End Sub ?
Cheers,
--
AP
"Bob Phillips" <[email protected]> a écrit dans le message de news:
uyMZ3T%[email protected]...
> 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" <[email protected]> wrote in message
> news:OZ%235lH%[email protected]...
>> 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 = _
>> oMailItem.Recipients.Add("[email protected]")
>> 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" <[email protected]>
> wrote
>> in message
>> news:[email protected]...
>> >
>> > 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" <[email protected]> wrote in message
news:uh4HBY%[email protected]...
> Why do you set to Nothing local variables that will be automatically
> destroyed by the End Sub ?
>
> Cheers,
> --
> AP
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 = "[email protected]"
> '.CC = "[email protected]"
> .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
>
Re: 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
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 = "[email protected]"
> '.CC = "[email protected]"
> .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
>
>
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" <[email protected]> wrote in message
news:uhj9g3%[email protected]...
> 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" <[email protected]> wrote in message
> news:uh4HBY%[email protected]...
>> Why do you set to Nothing local variables that will be
>> automatically
>> destroyed by the End Sub ?
>>
>> Cheers,
>> --
>> AP
>
>
Which is exactly the crux of the thread I posted Chip.
Bob
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:uhj9g3%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:uh4HBY%[email protected]...
> >> Why do you set to Nothing local variables that will be
> >> automatically
> >> destroyed by the End Sub ?
> >>
> >> Cheers,
> >> --
> >> AP
> >
> >
>
>
Taa for your reply bob,
the function now works by setting olFolders.Display!!
And more...
http://blogs.msdn.com/ericlippert/ar...28/122259.aspx
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Bob Phillips" <[email protected]> wrote in message news:[email protected]...
Which is exactly the crux of the thread I posted Chip.
Bob
"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:uhj9g3%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:uh4HBY%[email protected]...
> >> Why do you set to Nothing local variables that will be
> >> automatically
> >> destroyed by the End Sub ?
> >>
> >> Cheers,
> >> --
> >> AP
> >
> >
>
>
I didn't bother to read the thread. I just posted in order to add
more credibility (Curland's, not mine) to the argument that you
don't need to Set = Nothing.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Which is exactly the crux of the thread I posted Chip.
>
> Bob
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:uhj9g3%[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:uh4HBY%[email protected]...
>> >> Why do you set to Nothing local variables that will be
>> >> automatically
>> >> destroyed by the End Sub ?
>> >>
>> >> Cheers,
>> >> --
>> >> AP
>> >
>> >
>>
>>
>
>
See what you have started Ardus <vbg>
"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> And more...
> http://blogs.msdn.com/ericlippert/ar...28/122259.aspx
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Which is exactly the crux of the thread I posted Chip.
> Bob
>
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:uhj9g3%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:uh4HBY%[email protected]...
> > >> Why do you set to Nothing local variables that will be
> > >> automatically
> > >> destroyed by the End Sub ?
> > >>
> > >> Cheers,
> > >> --
> > >> AP
> > >
> > >
> >
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks