|
Opening MS Applications
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 |
Opening MS Applications
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 |
Opening MS Applications
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 |
Opening MS Applications
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 |
Opening MS Applications
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 |
Opening MS Applications
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 :eek: 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 |
Opening MS Applications
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 |
Opening MS Applications
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 :eek: 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 |
Opening MS Applications
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 :eek: 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 |
Opening MS Applications
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 |
Opening MS Applications
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 |
Opening MS Applications
Which is exactly the crux of the thread I posted Chip.
Bob "Chip Pearson" wrote in message ... 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 |
Opening MS Applications
Taa for your reply bob, the function now works by setting olFolders.Display!! -- 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 |
Opening MS Applications
And more...
http://blogs.msdn.com/ericlippert/ar...28/122259.aspx -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Bob Phillips" wrote in message ... Which is exactly the crux of the thread I posted Chip. Bob "Chip Pearson" wrote in message ... 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 |
Opening MS Applications
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" wrote in message ... Which is exactly the crux of the thread I posted Chip. Bob "Chip Pearson" wrote in message ... 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 |
Opening MS Applications
See what you have started Ardus <vbg
"Jim Cone" wrote in message ... And more... http://blogs.msdn.com/ericlippert/ar...28/122259.aspx -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Bob Phillips" wrote in message ... Which is exactly the crux of the thread I posted Chip. Bob "Chip Pearson" wrote in message ... 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 |
| All times are GMT +1. The time now is 02:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com