Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Office 2003
Excel Macro Security = Medium I have created a rule in Outlook to move all emails from a certain person and/or subject from the Inbox to a folder. This is a shared Mailbox, so many people may access to the mailbox, but the rule will apply for all. Then I thought I would €śprocess€ť the emails with some VBA. Essentially I want to see if the email contains certain text in the body, if the result is positive I will Parse the text, pull out the data I want, write it to a text file, mark the email read and move it to a €śprocessed€ť folder. If the test is negative I will simply delete the email. I am planning to manually run this code from Excel, but would this be better run from Outlook? I am getting Security Dialogs, how do I avoid this? The below started from some code on Dicks-Clicks, but has been filled out with research from both the Excel and Outlook Discussion Groups. Sub GetBodyFromInbox() ' http://www.dicks-clicks.com/excel/olRetrieving.htm Dim olApp As Outlook.Application Dim olNS As Namespace Dim InputFolder As Outlook.MAPIFolder Dim ProcessedFolder As Outlook.MAPIFolder Dim olMail As Outlook.MailItem Dim olMail2 As Outlook.MailItem Dim OutlookMailID As String Dim MessageText As String Set olApp = New Outlook.Application Set olNS = olApp.GetNamespace("MAPI") Set InputFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification") Set ProcessedFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification Processed") For Each olMail In InputFolder.Items OutlookMailID = olMail.EntryID Set olMail2 = olNS.GetItemFromID(OutlookMailID) MessageText = olMail2.Body If InStr(MessageText, "my text") 0 Then ' Parse this email. olMail2.UnRead = False olMail2.Move ProcessedFolder olMail2.UnRead = False ' Per suggestion from Ken Slovak re maybe having to set this twice. Else ' Mail is NOT Order Notification and should be deleted olMail2.Delete End If Next olMail Set InputFolder = Nothing Set ProcessedFolder = Nothing Set olNS = Nothing Set olApp = Nothing Set olMail2 = Nothing End Sub -- Trefor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Hi Trefor,
See Ron de Bruin at: How To Prevent displaying the dialog that enables you Index to send or not send the message http://www.rondebruin.nl/mail/prevent.htm --- Regards. Norman "Trefor" wrote in message ... Office 2003 Excel Macro Security = Medium I have created a rule in Outlook to move all emails from a certain person and/or subject from the Inbox to a folder. This is a shared Mailbox, so many people may access to the mailbox, but the rule will apply for all. Then I thought I would €śprocess€ť the emails with some VBA. Essentially I want to see if the email contains certain text in the body, if the result is positive I will Parse the text, pull out the data I want, write it to a text file, mark the email read and move it to a €śprocessed€ť folder. If the test is negative I will simply delete the email. I am planning to manually run this code from Excel, but would this be better run from Outlook? I am getting Security Dialogs, how do I avoid this? The below started from some code on Dicks-Clicks, but has been filled out with research from both the Excel and Outlook Discussion Groups. Sub GetBodyFromInbox() ' http://www.dicks-clicks.com/excel/olRetrieving.htm Dim olApp As Outlook.Application Dim olNS As Namespace Dim InputFolder As Outlook.MAPIFolder Dim ProcessedFolder As Outlook.MAPIFolder Dim olMail As Outlook.MailItem Dim olMail2 As Outlook.MailItem Dim OutlookMailID As String Dim MessageText As String Set olApp = New Outlook.Application Set olNS = olApp.GetNamespace("MAPI") Set InputFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification") Set ProcessedFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification Processed") For Each olMail In InputFolder.Items OutlookMailID = olMail.EntryID Set olMail2 = olNS.GetItemFromID(OutlookMailID) MessageText = olMail2.Body If InStr(MessageText, "my text") 0 Then ' Parse this email. olMail2.UnRead = False olMail2.Move ProcessedFolder olMail2.UnRead = False ' Per suggestion from Ken Slovak re maybe having to set this twice. Else ' Mail is NOT Order Notification and should be deleted olMail2.Delete End If Next olMail Set InputFolder = Nothing Set ProcessedFolder = Nothing Set olNS = Nothing Set olApp = Nothing Set olMail2 = Nothing End Sub -- Trefor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Norman,
Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor "Norman Jones" wrote: Hi Trefor, See Ron de Bruin at: How To Prevent displaying the dialog that enables you Index to send or not send the message http://www.rondebruin.nl/mail/prevent.htm --- Regards. Norman "Trefor" wrote in message ... Office 2003 Excel Macro Security = Medium I have created a rule in Outlook to move all emails from a certain person and/or subject from the Inbox to a folder. This is a shared Mailbox, so many people may access to the mailbox, but the rule will apply for all. Then I thought I would €śprocess€ť the emails with some VBA. Essentially I want to see if the email contains certain text in the body, if the result is positive I will Parse the text, pull out the data I want, write it to a text file, mark the email read and move it to a €śprocessed€ť folder. If the test is negative I will simply delete the email. I am planning to manually run this code from Excel, but would this be better run from Outlook? I am getting Security Dialogs, how do I avoid this? The below started from some code on Dicks-Clicks, but has been filled out with research from both the Excel and Outlook Discussion Groups. Sub GetBodyFromInbox() ' http://www.dicks-clicks.com/excel/olRetrieving.htm Dim olApp As Outlook.Application Dim olNS As Namespace Dim InputFolder As Outlook.MAPIFolder Dim ProcessedFolder As Outlook.MAPIFolder Dim olMail As Outlook.MailItem Dim olMail2 As Outlook.MailItem Dim OutlookMailID As String Dim MessageText As String Set olApp = New Outlook.Application Set olNS = olApp.GetNamespace("MAPI") Set InputFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification") Set ProcessedFolder = olNS.Folders("Mailbox - Test").Folders("Order Notification Processed") For Each olMail In InputFolder.Items OutlookMailID = olMail.EntryID Set olMail2 = olNS.GetItemFromID(OutlookMailID) MessageText = olMail2.Body If InStr(MessageText, "my text") 0 Then ' Parse this email. olMail2.UnRead = False olMail2.Move ProcessedFolder olMail2.UnRead = False ' Per suggestion from Ken Slovak re maybe having to set this twice. Else ' Mail is NOT Order Notification and should be deleted olMail2.Delete End If Next olMail Set InputFolder = Nothing Set ProcessedFolder = Nothing Set olNS = Nothing Set olApp = Nothing Set olMail2 = Nothing End Sub -- Trefor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Hi Trefor,
I fear that I misread your question. However, your similar post in the microsoft.public.outlook.program_vba NG, was anwered by the Outlook MVP Michael Bauer as follows: Problems with Processing Emails http://tinyurl.com/5rsr9o ============ If you run the code in Outlook 2003 or 2007 and don't create a new Application object but use the instrinsic one, you wouldn't get the security prompt. ============ Perhaps, therefore, you should proceed with your Outlook thread, especially as any relationship with Excel would appear to be tenuous. --- Regards. Norman "Trefor" wrote in message ... Norman, Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Norman,
The link between my problem here and Excel is: 1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in Outlook). 2. Once I have found the email, I will be writting the content to a text file that I will be reading back in to an Excel spreadsheet. So some of the code will be the same. If I move this to Outlook VBA I will also have to replicate a whole bunch of code. 3. I already have a system in place to distribute code updates for Excel, but not for Outlook. -- Trefor "Norman Jones" wrote: Hi Trefor, I fear that I misread your question. However, your similar post in the microsoft.public.outlook.program_vba NG, was anwered by the Outlook MVP Michael Bauer as follows: Problems with Processing Emails http://tinyurl.com/5rsr9o ============ If you run the code in Outlook 2003 or 2007 and don't create a new Application object but use the instrinsic one, you wouldn't get the security prompt. ============ Perhaps, therefore, you should proceed with your Outlook thread, especially as any relationship with Excel would appear to be tenuous. --- Regards. Norman "Trefor" wrote in message ... Norman, Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Hi Trefor,
In your Outlook thread, you replied to Michael: ============ Sorry but I am new to this, what does this "don't create a new Application object but use the instrinsic one" mean? In my example code, have I done something that breaks this rule? If so are you able to assist with what is wrong? ============ Michael was referring to the fact that your code creates a new version of Outlook with the following instruction: Set olApp = New Outlook.Application --- Regards. Norman "Trefor" wrote in message ... Norman, The link between my problem here and Excel is: 1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in Outlook). 2. Once I have found the email, I will be writting the content to a text file that I will be reading back in to an Excel spreadsheet. So some of the code will be the same. If I move this to Outlook VBA I will also have to replicate a whole bunch of code. 3. I already have a system in place to distribute code updates for Excel, but not for Outlook. -- Trefor "Norman Jones" wrote: Hi Trefor, I fear that I misread your question. However, your similar post in the microsoft.public.outlook.program_vba NG, was anwered by the Outlook MVP Michael Bauer as follows: Problems with Processing Emails http://tinyurl.com/5rsr9o ============ If you run the code in Outlook 2003 or 2007 and don't create a new Application object but use the instrinsic one, you wouldn't get the security prompt. ============ Perhaps, therefore, you should proceed with your Outlook thread, especially as any relationship with Excel would appear to be tenuous. --- Regards. Norman "Trefor" wrote in message ... Norman, Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Norman,
Thanks again, is this the only line that is wrong? What should I do instead? Does this apply to Excel VBA as well? I tried copying my entire code into Outlook and it look likes I mgiht have a few other problems as well, so my short term fix may well be running from Excel anyway. -- Trefor "Norman Jones" wrote: Hi Trefor, In your Outlook thread, you replied to Michael: ============ Sorry but I am new to this, what does this "don't create a new Application object but use the instrinsic one" mean? In my example code, have I done something that breaks this rule? If so are you able to assist with what is wrong? ============ Michael was referring to the fact that your code creates a new version of Outlook with the following instruction: Set olApp = New Outlook.Application --- Regards. Norman "Trefor" wrote in message ... Norman, The link between my problem here and Excel is: 1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in Outlook). 2. Once I have found the email, I will be writting the content to a text file that I will be reading back in to an Excel spreadsheet. So some of the code will be the same. If I move this to Outlook VBA I will also have to replicate a whole bunch of code. 3. I already have a system in place to distribute code updates for Excel, but not for Outlook. -- Trefor "Norman Jones" wrote: Hi Trefor, I fear that I misread your question. However, your similar post in the microsoft.public.outlook.program_vba NG, was anwered by the Outlook MVP Michael Bauer as follows: Problems with Processing Emails http://tinyurl.com/5rsr9o ============ If you run the code in Outlook 2003 or 2007 and don't create a new Application object but use the instrinsic one, you wouldn't get the security prompt. ============ Perhaps, therefore, you should proceed with your Outlook thread, especially as any relationship with Excel would appear to be tenuous. --- Regards. Norman "Trefor" wrote in message ... Norman, Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Processing Emails
Norman,
Thanks I have worked it out between here and the Outlook Discussion Group. -- Trefor "Norman Jones" wrote: Hi Trefor, In your Outlook thread, you replied to Michael: ============ Sorry but I am new to this, what does this "don't create a new Application object but use the instrinsic one" mean? In my example code, have I done something that breaks this rule? If so are you able to assist with what is wrong? ============ Michael was referring to the fact that your code creates a new version of Outlook with the following instruction: Set olApp = New Outlook.Application --- Regards. Norman "Trefor" wrote in message ... Norman, The link between my problem here and Excel is: 1. I am more familiar with using VBA in Excel than Outlook (i.e. ZERO in Outlook). 2. Once I have found the email, I will be writting the content to a text file that I will be reading back in to an Excel spreadsheet. So some of the code will be the same. If I move this to Outlook VBA I will also have to replicate a whole bunch of code. 3. I already have a system in place to distribute code updates for Excel, but not for Outlook. -- Trefor "Norman Jones" wrote: Hi Trefor, I fear that I misread your question. However, your similar post in the microsoft.public.outlook.program_vba NG, was anwered by the Outlook MVP Michael Bauer as follows: Problems with Processing Emails http://tinyurl.com/5rsr9o ============ If you run the code in Outlook 2003 or 2007 and don't create a new Application object but use the instrinsic one, you wouldn't get the security prompt. ============ Perhaps, therefore, you should proceed with your Outlook thread, especially as any relationship with Excel would appear to be tenuous. --- Regards. Norman "Trefor" wrote in message ... Norman, Thanks for your reply. Ok as I read it on Ron's site there is three choices. 1. Express ClickYes. Third Party, while free is not easy to distribute by the looks of it i.e. I can not embed in Excel. And appears to require the user to manually turn it off/on. 2. Outlook Redemption. Third Party and not Free for commercial use. 3. CDO, but this is not going to help me with Outlook emails by the looks of it. Are these my only choices (or have I misunderstood any of the choices)? -- Trefor |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to send mail from Outlook 2007 using Visual Basic 6.0 Code.
Hi Everyone,
In outlook 2007, the following code is not working properly. This is add-in project build using Visual basic 6.0. ---------------------------------------- Set oOutbox = m_oOutlook.Session.GetDefaultFolder(olFolderOutbox ) oMovedMail.Item = oCurMail.Move(oOutbox) oMovedMail.Send Variable usage FYI... Dim oCurMail as object Set oCurMail = CreateObject("Test.USafeMailItem") ' Parameter ByRef objMail As Outlook.MailItem Dim oMovedMail As Object Set oCurMail = CreateObject("Test.USafeMailItem") ' Get the current mail item. oCurMail.Item = objMail Dim oOutbox As Object ---------------------------------------- The followings are the observations; Case 1 : Some time, the mail is going out to the receiver, but it stayed at outbox itself. Case 2 : Some time, the mail is not all going out, it stayed at outbox itself. Could you please tell that what is the actual issues which cause the functionality? Do I need to change the coding flow for outlook 2007? Note : This is absolutely working fine in prior to 2007. I pasted only the important code fyi. I ensure that there is no runtime err or logical err. Your adivse would be appreciated on this. Thx in Advance, Ashok |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Processing Data | Excel Worksheet Functions | |||
Macro for Row Processing | Excel Programming | |||
speed of processing | Excel Programming | |||
Error processing | Excel Programming | |||
Some Help Processing a Workbook | Excel Programming |