I am trying to call a procedure stored in Outlooks' 'ThisOutlookSession', from an Excel workbook. Unfortunately the Newer Outlook 2010 app does not have compatibility with the application.run *SubName* between MS office products.
It is not an option to complete an Excel script that sends the email on Outlooks behalf due to security messages on '.send' which requires a manned station. (& unable to change security settings from company policy)
Current workflow...
-User sends me an e-mail with 'command' in subject & attachments
-Event listener finds and successfully runs an Excel routine on attachments with the below headers for listening in Outlook
Private WithEvents Items As Outlook.Items
&
Private Sub Items_ItemAdd(ByVal Item As Object)
-Once processed in Excel, I am trying to get this data automatically returned to sender. (This is where the problem is)
I am using late binding in Excel to create and ready the return e-mail. It is one step before '.send'. I would ideally like to avoid a SendKeys statements because it is not fully reliable if working on other workbooks at the same time.
In Excel...
Sub test()
Dim aOutlook As Object
Dim aEmail As Object
Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
aEmail.To = "My email address"
aEmail.Subject = "Testing"
aEmail.Body = "Testing"
aEmail.display
End Sub
I have been endlessly trying to get Outlook to recognise Excel creating this new email with events listed in the MSDN pages. I think something like the below code is what is needed to identify a new mailitem, but no success using most of the preset declarations under the Outlook.mailItem.
Private WithEvents NewItem As Outlook.mailItem
Please let me know if you have solution or an alternative idea for me to pursue
Solved, if anyone needs this in the future. It bypasses security warnings and does not rely on send-keys.
Excel prepares an email and displays it - when prepared, the 'On event' from Outlook recognises the 'to' address when the mailitem is loaded, and can then take over from there: finishing with a .send statement.
Note, more parameters will need to be added such as a specific code in the item.subject to ensure that its the correct e-mail that's being sent.
In Excel:
Sub test()
Dim aOutlook As Object
Dim aEmail As Object
Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
aEmail.To = "abs@123.com"
aEmail.Subject = "Testing"
aEmail.Body = "testing"
aEmail.Display
End Sub
In Outlook:
Public WithEvents myItem As Outlook.mailItem
Private Sub Application_ItemLoad(ByVal Item As Object)
If (TypeOf Item Is mailItem) Then
Set myItem = Item
End If
End Sub
Private Sub myItem_Open(Cancel As Boolean)
If myItem.To = "abs@123.com" Then
MsgBox "Detected"
myItem.Send
End If
End Sub