Initial posting
This is not an answer. It is partly a request for clarification and partly a reference to an earlier answer which I believe will help you progress.
There is little difference between a VBA macro written for Outlook and one written for Excel. Is there a reason why you want to run an Excel macro from Outlook? It would be easier to include the macro without Outlook. For example something like this:
- New item event macro triggered by new email.
- Macro checks for subject.
- If subject is a keyword, open appropriate Excel workbook, store details of email, create reply from information in workbook and close workbook.
- Move processed emails to archive folder.
In response to earlier questions, I created a macro that demonstrates writing to Excel from Outlook. That macro does not match your requirement but demonstrates lots of techniques that will be relevant. Click https://stackoverflow.com/a/12146315/973283 to visit that answer.
I hope the above points are helpful. Do come back with clarifications or further questions as necessary.
Posting 2 after the requirement for clarified
This next part of the answer is later than I hoped. Partly because it has been a busy day and partly because I encountered a problem I had not expected.
From within Outlook select the Visual Basic Editor by:
- selecting Tools then Macro then Visual Basic Editor or
- clicking Alt+F11.
Down the left will be the Project Explorer which could be:
- Project1 (VbaProject.OTM)
+ Microsoft Outlook Objects
+ Forms
+ Modules
If you have no forms or modules, these entries will be missing. Any entries that are present may already be expanded. Expand Microsoft Outlook Objects, if not already expanded, by clicking the +. The display will change to:
- Project1 (VbaProject.OTM)
- Microsoft Outlook Objects
ThisOutlookSession
+ Forms
+ Modules
Click ThisOutlookSession. The top right area will become white (if it is not already white). This is a code area, like a module, but is for special code.
Paste the code below into the ThisOutlookSession code area.
This code contains two macros. The first macro, Application_Startup(), is automatically executed when Outlook is opened. It specifies that the arrival of a new item in the Inbox is to trigger a call of macro myNewItems_ItemAdd(). It also outputs "Welcome" to demonstrate that it has been called. The second macro, myNewItems_ItemAdd() identifies the type of the new item and outputs selected information to the Immediate Window.
These macros execute correctly but there is a problem I have not solved. Outlook correctly, in my view, is unhappy about macros and macros that access emails in particular. When you open Outlook it tells you there are macros (providing you have an adequate security level) and offers you the option of enabling or disabling those macros. If a macro attempts to access an email, Outlook warns you and offers the option of permitting access for up to 10 minutes.
I have self-certified these macros which tells Outlook I trust them. This suppresses the warning about macros being present but does not, as I expected, suppress the warning about a macro attempting to access emails. I will investigate further for my own interest but having to be ready to give permission every 10 minutes defeats the objective of you having a new item event.
I suggest you play with these macros anyway because knowing this functionality exists may be helpful later.
I can think of three situations:
- If you work for a large company with an IT department, you probably cannot self-certify anyway because you need administrative privileges to do so. You will need to seek the advice of the IT Department.
- If I cannot identify how to suppress the requirement to give permission every 10 minutes, perhaps someone else on Stack Overflow can.
- Once per hour, say, you could run a macro which would look for any request emails that had arrived since the last run. If the macro found any, you would give permission for it to process them. If this approach appeals, I definitely know how to implement such a macro.
.
Option Explicit
Public WithEvents MyNewItems As Outlook.Items
Private Sub Application_Startup()
' This event procedure is called when Outlook is started
Dim NS As NameSpace
Set NS = CreateObject("Outlook.Application").GetNamespace("MAPI")
With NS
Set MyNewItems = NS.GetDefaultFolder(olFolderInbox).Items
End With
MsgBox "Welcome"
End Sub
Private Sub myNewItems_ItemAdd(ByVal Item As Object)
' This event procedure is called whenever a new item is added to
' to the InBox.
Dim NewMailItem As MailItem
Debug.Print "------Item Received"
On Error Resume Next
' This will give an error and fail to set NewMailItem if
' Item is not a MailItem.
Set NewMailItem = Item
On Error GoTo 0
If Not NewMailItem Is Nothing Then
' This item is a mail item
With NewMailItem
Debug.Print "Subject " & .Subject
Debug.Print "Sender Email [" & .SenderEmailAddress & "]"
End With
Else
' Probably a meeting request.
Debug.Print "Not mail item " & Item.Subject
End If
End Sub
For my next posting, I will add an Outlook macro that opens a workbook and writes to it and reads from it.
In your question you say you want to send a range from the workbook to the author of the email. Do you know how to do this? If not what sort of range would you send? How would you want it to appear? Converting a small range to Html is not difficult if that would give you the appearance you seek.
Final posting
Sorry but I am giving up on this question. Nothing I have tried prevents Outlook from reporting that a macro is trying to send an email. Everything else about this question is straightforward but this issue is a killer.
