Folder view settings by VBA macro

[Diane Poremsky]

I would have expected null too, but vba can be goofy - debug.print is my best friend.

 

[D.Moore]

Yeah, you right!

May I ask your help on an another question:

I am trying to check each email in a folder, and when it has a specific subject, I need to write the TO recipient to a txt file with append to the first line, and not whole file overwrite.

I was able to get to this point:

Sub StoreFailedLogs()

 Dim i As Long
 Dim ItemsCount As Integer

 Dim objVariant As Variant
 Dim objFolder As Outlook.MAPIFolder
 Dim objItem As Object
 
 Dim fso As FileSystemObject
 Dim ts As TextStream

' On Error Resume Next

    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile("D:\failed_logs_email.txt", ForAppending, True)

    Set objFolder = Session.GetDefaultFolder(olFolderInbox).Folders("Logs")

    ItemsCount = objFolder.Items.Count
        
    If ItemsCount Then
    
        For i = ItemsCount To 1 Step -1
        
            Set objItem = objFolder.Items.Item(i)
            
            Set objVariant = objItem
        
            If objVariant.MessageClass = "IPM.Note" Then
            
                If objVariant.Subject = "Undeliverable: Logs" Then
                
                    With ts
                        
                        .WriteLine (objVariant.To)
                        .Close
                
                    'objVariant.Delete
                    
                    End With
                    
                End If
    
            End If
            
        Next
    
    End If

 Set objFolder = Nothing
 Set objVariant = Nothing
 Set objItem = Nothing
 Set ts = Nothing
 Set fso = Nothing

End Sub

My problem is according to the debug: "User-defined type is not defined" indicated for FileSystemObject.

I was searching to find a better example, but those create object examples i found all failed, and seemed to be way more complicated than th actual task would require it.

Could you help me please, what would be the simplest way to make it work ?

Many many many thanks in advance!

 

[Diane Poremsky]

You need a reference to the Microsoft Scripting Runtime in Tools, references.

or use this format:
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")


FQIW, its generally faster to set the reference first, but on a small macro it probably wont make a difference.

 

[D.Moore]

THANK YOU! I was able to work it out based on your help as follows:

 Dim objFSO
Dim objTextFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
    Const ForAppending = 8
    Set objTextFile = objFSO.OpenTextFile("D:\emails.txt", ForAppending, True)

It is working, BUT I had to realize (and learn), that mailer-daemon failed delivery messages are not mail items, but report items.

I can read the subject as: objVariant.Subject

BUT,

my problem is, that I cannot access the original recipient, in spite, outlook shows it in the email item To column.

May I ask if there is any way to read out the original recipient to whom the email were trying to sent ?

BIG thank you for your time and kind help!

 

[Diane Poremsky]

is the address in the message body? this example uses the address in the NDR body: Categorize Contacts with bad addresses

I think you can get the address but I need to double check.

 

[D.Moore]

The email address is indeed in the message body (left bottom arrow), BUT the reason why I was looking for to catch the original sender TO address, as I was surprised to see, that the original address is available in outlook view pane TO column (top right arrow). So, if outlook has it, I am hoping we can have it to. What do you think/suggest ?

 

[Diane Poremsky]

I'll have to check on that - i hadn't noticed the address in the To field - I thought it had my address in the to field.

 

[D.Moore]

I'll have to check on that - i hadn't noticed the address in the To field - I thought it had my address in the to field.

I was surprised too, when I realized, that addition to the body, it also has it in outlook To column. I even tried a "dirty trick" to execute a reply (objVariant.Actions.Item(1).Execute) and trying pick the address up from the to Address field (.Recipients.Item(1).Address), but it only gave me the MAILER-DAEMON email address.

Maybe outlook is just intelligent and since the original recipient email address is in the body, why it is shown in the TO column of outlook reading pane ?

BTW: this is my outlook reading pane columns, it self setup:

So, in our case as you can see in my earlier message, FROM column is my email provider: MAILER-DAEMON , TO column is the original recipient email address, what I am trying to save.

 

[Diane Poremsky]

I used MFCMAPI to export an NDR message - it's in PR_ORIGINAL_DISPLAY_TO_W - this is not exposed in VBA but we can use a property accessor to get it.

 <property tag = "0x0074001F" type = "PT_UNICODE" >
  <Name>PR_ORIGINAL_DISPLAY_TO_W</Name>
  <OtherNames>PidTagOriginalDisplayTo, PR_ORIGINAL_DISPLAY_TO, PR_ORIGINAL_DISPLAY_TO_A, ptagOriginalDisplayTo</OtherNames>
 
 <property tag = "0x0E04001F" type = "PT_UNICODE" >
  <Name>PR_DISPLAY_TO_W</Name>
  <OtherNames>PidTagDisplayTo, PR_DISPLAY_TO, PR_DISPLAY_TO_A, ptagDisplayTo</OtherNames>

It's in some other tags, but one of those should do it.

If you use a text utility such as Notepad++ that displays XML in different colors, the body will be in a different color (orange here). We want the tags which as in blue and black (in Notepad ++ at least.)

Basics on using peorptyaccessors -
Read MAPI properties not exposed in Outlook's Object Model

 

[Diane Poremsky]

This is my code that works on the selected item -

Sub StoreFailedLogs()

 Dim objItem As Object
 Dim fso As FileSystemObject
 Dim ts As TextStream

 Dim propertyAccessor As Outlook.propertyAccessor

' On Error Resume Next

    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile("D:\failed_logs_email.txt", ForAppending, True)

Set objItem = Application.ActiveExplorer.Selection.Item(1)
    
Set propertyAccessor = objItem.propertyAccessor
    
Debug.Print propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0074001F")
    
 strTo = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0074001F")
 
    With ts
            
    .WriteLine (strTo)
    .Close
  End With
 
 Set objItem = Nothing
 Set ts = Nothing
 Set fso = Nothing

End Sub

 

[D.Moore]

Seems it was usefull for some

This is my code that works on the selected item -

Sub StoreFailedLogs()

Dim objItem As Object
Dim fso As FileSystemObject
Dim ts As TextStream

Dim propertyAccessor As Outlook.propertyAccessor

' On Error Resume Next

    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile("D:\failed_logs_email.txt", ForAppending, True)

Set objItem = Application.ActiveExplorer.Selection.Item(1)
   
Set propertyAccessor = objItem.propertyAccessor
   
Debug.Print propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0074001F")
   
strTo = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0074001F")

    With ts
           
    .WriteLine (strTo)
    .Close
  End With

Set objItem = Nothing
Set ts = Nothing
Set fso = Nothing

End Sub

Seems, it was useful for somebody else too. LOL!

BIG THANK YOU from me too!!

I was able to make it work both this solution and the other one you sent earlier. Hard to tell which one is more easier, but this one is definitively faster.

I also realized, that using these scripts as base of an Outlook Rule is not a stable solution as its not always got triggered. So, now I am working on to add it as itemAdd in ThisOutlookSession.

 

[D.Moore]

I ran into a very strange issue. When Outlook is running, and a MailItem or Report item arrives, _itemAdd event for the folder in ThisOutlookSession triggers properly. But when the message arrives e.g. over the night and I turn on outlook in the morning, rules are executed, BUT the _itemAdd event not trigger, until a new item is arrive to that folder.

May I ask why and whether possible to solve this ?

 

[D.Moore]

Dear Diane,

May I ask if there is any way to call/trigger an _itemadd event manually from Application Startup() ?

I know it is called when new item added to the given folder, but e.g. when outlook starts, it does not get called, so I would like to call it manually. Important to say, that the (ByVal Item As Object) is not exist in this case, and it isn't a problem, as I don't utilize it in the code, however, I still have to call/trigger the event somehow.

Could you help me please how an I am able to do that?

Many Thanks,

Moore

 

[Diane Poremsky]

itemadd is setup using auto startup - that sets the folder to watch. If you want to run the macro on everything in a folder, or on the first item in the folder, you can call it from the auto startup. if you want to run an item add manually, you can use a small stub macro to call it and pass the selected message to the macro.

 

[D.Moore]

itemadd is setup using auto startup - that sets the folder to watch. If you want to run the macro on everything in a folder, or on the first item in the folder, you can call it from the auto startup. if you want to run an item add manually, you can use a small stub macro to call it and pass the selected message to the macro.

My problem is, that I don't know how to do it concretely. In details:

My code is in: Private Sub objItemsUNDELIVERED_itemAdd(ByVal Item As Object)

I tried to add this line to the Application_Startup() :

objItemsUNDELIVERED_itemAdd

(null, because the Item is irrelevant for me in this case)

But it does not work, it seems missing the value of the Item, but when I tried to pass a mail.item, it said it is not the correct type.

So, could you help me please, how can I call a Private Sub objItemsUNDELIVERED_itemAdd manually if I dont have the Item to add ?

 

[Diane Poremsky]

do you want to run the itemadd routine on all messages in the folder or only on the selected message (or first message, first nn messages, new messages in last 24 hours, etc) ?

What I would probably do is create a stub macro and call it from the auto start.
put this at the end of auto start, after the folder id'd for objItemsUNDELIVERED is set.
RunItemAdd

the create a macro named RunItemAdd:

Depending on the conditions you are using, it would be something like this.
public sub RunItemAdd()
for each item in objItemsUNDELIVERED
objItemsUNDELIVERED_itemadd item
next
end sub

 

[D.Moore]

do you want to run the itemadd routine on all messages in the folder or only on the selected message (or first message, first nn messages, new messages in last 24 hours, etc) ?

What I would probably do is create a stub macro and call it from the auto start.
put this at the end of auto start, after the folder id'd for objItemsUNDELIVERED is set.
RunItemAdd

the create a macro named RunItemAdd:

Depending on the conditions you are using, it would be something like this.
public sub RunItemAdd()
for each item in objItemsUNDELIVERED
objItemsUNDELIVERED_itemadd item
next
end sub

Thank you!

The target is to run after Outlook started right away, addition to that when its run when a new item added.

Understood, so there is no proper way to call the already existing one, but better to create a separate one.

Al clear, BIG THANK YOU!

 

[Diane Poremsky]

The target is to run after Outlook started right away, addition to that when its run when a new item added.

When you start outlook, the itemadd should run on all new items put into the folder - the only exception could be if using Exchange online mode as mail is put into the folder on the server.

But, unless you move the items or mark them after processing and use an if statment, you'll reprocess everything in the folder.