-
Run code after item sent
I've created a userform that enables me to file an item from the Sent Items folder. The calling and initialise code is as follows
[VBA]
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Shows
End Sub
Sub Shows()
ufSentItems.Show
End Sub
Private Sub UserForm_Initialize()
Dim myOlApp As Outlook.Application
Dim myNamespace As NameSpace
Dim olItem As MailItem
Dim myFolder As Outlook.MAPIFolder
Set myOlApp = CreateObject("Outlook.Application")
Set myNamespace = myOlApp.GetNamespace("MAPI")
'get mail item from Sent Items folder
Set myFolder = _
myNamespace.GetDefaultFolder(5) 'Sent items folder
Set olItem = myFolder.Items(1)
Label1.Caption = olItem
Set myOlApp = Nothing
Set myNamespace = Nothing
Set myFolder = Nothing
Set olItem = Nothing
End Sub
[/VBA]
The problem appears to be that the userform is opened before the mail has reached the Sent Items folder, giving me an error on this line.
[VBA]
Set olItem = myFolder.Items(1)
[/VBA]Is there a way to delay things until the item arrives, or a different event I can use?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Can you change this:
[vba]Set olItem = myFolder.Items(1)[/vba]
to this:
[vba]Set olItem = Item[/vba]
Since the item in question is passed to the event procedure? Of course you'll have to forward the "Item" object variable from the event procedure to the Shows() procedure to the form initialization procedure.
Edit: You will likely have to pass the variable ByRef. I'm not sure if you can overload an event in VBA like that, though.
Last edited by Mavyak; 08-05-2008 at 11:02 AM.
-
I've added mySentItem as a Public Variable. That allowed me to open the form and run the code. Unfortunately the ItemSend event is triggered before the item is sent, and the Send does not complete because the item has been moved.
[vba]
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Set mySentItem = Item
Shows
End Sub
[/vba]
I've exported the userform and code to the zip file
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Can you add something like:
[VBA]While Not myItemSent.Sent
DoEvents()
WEnd[/VBA]
I'm writing off the cuff. I'm not usre if the Sent property is boolean or not, but I suspect you get the idea.
-
The ItemSend code is probably the wrong approach. It is dealing with the Item before it is sent. I don't see an AfterSending event, so I guess what I need is a workaround. Is there an OnTime type function available in Outlook.
If all else fails, I can click a button, but that seems like admitting defeat!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
[vba]Dim myOlApp As New Outlook.Application
Public WithEvents myOlItems As Outlook.Items
Public Sub Initialize_handler()
Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail).Items
End Sub
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
MsgBox Item.subject
End Sub[/vba]
Put that code in your ThisOutlookSession class module. Send yourself a test email and the subject should pop up in a message box.
Enjoy!
Mav
Edit: You might want to change the passing style to ByRef. I'm not sure if the default style of ByVal will actually move the item or copy it.
-
Hi Mavyak,
I've not forgotten this, just can't get it to work yet. I've looked at the ItemAdd method so see where you are coming from, but the AddItem code isn't getting triggered. Does it work for you?
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Yeah, it sure does. I tested it on me before I posted it to you.
-
I had to manually run the "Initialize_handler()" sub but it was automatic there after.
-
Success at last!
I don't see what iI was doing wrong before, but my final code is as follows
[VBA]
Option Explicit
Dim myOlApp As New Outlook.Application
Public WithEvents myOlItems As Outlook.Items
Private Sub Application_Startup()
'Sent emails handler
Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderSentMail).Items
'Autoclose routine
If Time < TimeValue(AutoCloseTime) Then StartAutoCloseTimer
End Sub
Private Sub Application_Quit()
StopAutoCloseTimer
End Sub
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
'open the mail handler form
Call Shows
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules