Do Outlook 2010 rules run on mail sent from Excel VBA?

5
votes

I have the following basic email code in an Excel VBA: 

Dim OutAppUser      As Object  
Dim OutMailUser     As Object  
Set OutAppUser = CreateObject("Outlook.Application")  
Set OutMailUser = OutAppUser.CreateItem(0)  
With OutMailUser  
            .To = Range("Ctl_Req_Email").Value  
            .CC = ""  
            .BCC = ""  
            .Subject = TempFNUser  
            .ReadReceiptRequested = False  
            .Body = userEmailText  
            .Attachments.Add FullTempNameUser  
            .Send    'Use .Send or .Display  
End With

The workbook with this macro is widely distributed but actual driving of the email macro is done by only a few and now the email address where this goes has changed. I have created an Outlook rule for "after I send the message" which add's a "CC" for the new email address as a means of redirecting the email externally. The rule works perfectly on manual test emails, but does not seem to act on the email sent from the Excel VBA code. Does something additional needs to be coded on the VBA code to send the email in order to allow rules to run?

2
excelrulesoutlook-2010vba
Do you have only one email account set up in Outlook? Could it be sending from the wrong one? Also, is your rule a client-side one or a server-side one? – Chris Rae
I find that hard coded emails can be problematic for individuals. when sent to a distibution list you have a better chance of success. Here is a link to some valuable training on the subject. <msdn.microsoft.com/en-us/library/ff458119(v=office.11).aspx> – Desert Spider
You may be able to use 'Run Rules Now' in Outlook on the message(s), that in turn you may be able to automate depending on what version you have - but it will be inelegant compared to just updating the source data to have the right address in the first place. – James Snell
I think Chris R's comment could well be important. Personally I've moved away from Outlook VBA within my Excel macros and now use our SQL Server to send all mails. Do you have access to the server? Do you have Outlook code in many workbooks? – whytheq

2 Answers

1
votes

http://www.slipstick.com/outlook/email/choosing-the-folder-to-save-a-sent-message-in/

"VBA does not work on messages created with File, Send commands in other applications or the Send to command in Windows Explorer. Send to commands use Simple MAPI, which bypasses Outlook functionality."

Does not actually say Rules but you have seen Rules bypassed.

If you really want to use Rules, have Outlook open up Excel.

0
votes

Why don't you create a new spreadsheet with a new macro:

Dim OutAppUser      As Object  
Dim OutMailUser     As Object  
Set OutAppUser = CreateObject("Outlook.Application")  
Set OutMailUser = OutAppUser.CreateItem(0)  
With OutMailUser  
            .To = Range("Ctl_Req_Email").Value  
            .CC = "New_email@address"  ' Only change to your code
            .BCC = ""  
            .Subject = TempFNUser  
            .ReadReceiptRequested = False  
            .Body = userEmailText  
            .Attachments.Add FullTempNameUser  
            .Send    'Use .Send or .Display  
End With

People would fill out their data using the old sheet, and the macro drivers would use this code to fire off the email.