none
VBA to export/import Safe Sender List RRS feed

  • Question

  • I would like to code the exporting and importing of my safe sender list in Outlook 2007.  I want to read/write to a txt or csv file (excel would be fine as well).  It does not need to prompt the User for a path or file name.  Can someone please point me in the right direction?

    Thanks in advance,

    Jim


    Monday, March 5, 2012 1:33 PM

Answers

  • Dmitry,

    Here is what I have found, if I run the procedures at application startup the safe sender list displays the updated values.  So, it looks like I'm done.  Thank you very much!

    Here is the final results;

    At application quit I run ExportSafeSenderList, at application start I run ClearSafeSenderList and then ImportSafeSenderList.

    Once Redemption is downloaded, installed, and “Redemption Outlook and MAPI COM Library” is selected as a reference, the following code should work;

    Sub ExportSafeSenderList()  'Export the current profile's Safe Sender List
     
      Dim varOutputPath As String
      Dim varOutputFileName As String
     
      Dim rSession As Object
      Dim fs As Object
      Dim a As Variant
      Dim JunkOptions As Object
      Dim Address As Variant

      varOutputPath = "C:\Email\"
      varOutputFileName = "SafeSenderList.txt"

      Set fs = CreateObject("Scripting.FileSystemObject")
      Set a = fs.CreateTextFile(varOutputPath & varOutputFileName, True)

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      For Each Address In JunkOptions.TrustedSenders
        a.WriteLine (Address)
        'Debug.Print Address
      Next
     
      a.Close

    End Sub

    Sub ClearSafeSenderList() 'Clear all values from the current profile's safe sender list

      Dim rSession As Object
      Dim JunkOptions As Object

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      JunkOptions.TrustedSenders.Clear
      JunkOptions.Save

    End Sub

    Sub ImportSafeSenderList() 'Add the values from a text file into the current profile's safe sender list
     
      Dim varOutputPath As String
      Dim varOutputFileName As String
     
      Dim fs As Object
      Dim a As Variant
      Dim varReturnString As String
     
      Dim rSession As Object
      Dim JunkOptions As Object
      Dim Address As Variant
     
      varOutputPath = "C:\Email\"
      varOutputFileName = "SafeSenderList.txt"
     
      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
     
      Set fs = CreateObject("Scripting.FileSystemObject")
      Set a = fs.OpenTextFile(varOutputPath & varOutputFileName)    ', ForReading, False) <--remarked out because the "ForReading" clause throws and error
      Do While a.AtEndOfStream <> True

        varReturnString = a.ReadLine
        'Debug.Print varReturnString
        JunkOptions.TrustedSenders.Add (varReturnString)
       
      Loop
     
      JunkOptions.Save
       
      a.Close

    End Sub

    • Marked as answer by phonepagerfax Monday, March 5, 2012 9:50 PM
    Monday, March 5, 2012 9:50 PM

All replies

  • In case of Exchange, the list is stored as a part of the Junk mail rule (a hidden message in the Inbox folder), otherwise it is stored in the registry in the profile section.

    You can access that list using RDOSession.JunkMailOption.TrustedSenders or (if you want to access a delegate Exchange mailbox) RDOStore.JunkMailOption.TrustedSenders :

    http://www.dimastr.com/redemption/rdojunkemailoptions.htm


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 2:11 PM
  • Dimitry,

    Thank you for the help, I guess I need a little more handholding that I figured.  I’m getting “Method or data member not found” error on the .MAPIOBJECT of the Session.MAPIOBJECT in the line Session.MAPIOBJECT = Application.Session.MAPIOBJECT

    I have references set to Visual Basic for Applications, MS Outlook 12.0 Object Library, OLE Automation, and MS Office 12.0 Object Library…

    Thanks,

    Jim


    Monday, March 5, 2012 2:53 PM
  • How did you declare the Session variable?

    Where is your code running?

    Did you add a reference to Redemption?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 2:57 PM
  • Dmitry,

    At first I just copied the code from the link you provided and put it into a sub procedure.  I just downloaded the Redemption Developer Version and installed it.  I then selected “Redemption Outlook and MAPI COM Library” from my References list.  Restarted Outlook and get the same error.

    I’m trying to run the code as an Outlook Macro.

    Sub ExportSafeSenderList()

    set Session = CreateObject("Redemption.RDOSession")
    Session.MAPIOBJECT = Application.Session.MAPIOBJECT
    set JunkOptions = Session.JunkEmailOptions
    for each address in JunkOptions.BlockedSenders
      Debug.Print address
    next

    End Sub

    Monday, March 5, 2012 3:32 PM
  • Are you running this code from Access VBA? It has an intrinsic variable called "Session", so you will need to rename the avriabel in your script to, say, rSession:

    Sub ExportSafeSenderList()

    set rSession = CreateObject("Redemption.RDOSession")
    rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
    set JunkOptions = rSession.JunkEmailOptions
    for each address in JunkOptions.BlockedSenders
      Debug.Print address
    next

    End Sub


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 5:48 PM
  • Dmitry,

    The code below works!  Thanks!  Could you please help with writing the values to a file and reading them back in?

    Sub ExportSafeSenderList()
      Dim rSession As Object
      Dim JunkOptions As Object
      Dim Address As Variant

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      For Each Address In JunkOptions.TrustedSenders
        Debug.Print Address
      Next
    End Sub

    Monday, March 5, 2012 6:07 PM
  • You can use the FileSystemObject for that:

    http://msdn.microsoft.com/en-us/library/aa164509(v=office.10).aspx


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 6:21 PM
  • Dmitry,

    It's looking good.  I've read the text file back in.  The last two things I need to do are delete the values in the current Safe Sender List and insert the values I read in.

    To delete the values I can do them individually or the entire list at once, it does not matter to me.  I tried all kinds of variations on clearing the list, most of the time i received 'object does not support...'.  The Address = Null did not throw an error but it didn't clear the addresses either...

    For Each Address In JunkOptions.TrustedSenders
        Address = Null
        'JunkOptions.TrustedSenders.Address = Null
      Next

    When I do read a value in from the text file how would i write it to the list?  Something like Address = varReturnString?

      Do While a.AtEndOfStream <> True
        varReturnString = a.ReadLine
        Debug.Print varReturnString
      Loop

    Thanks for all your help,

    Jim

    Monday, March 5, 2012 7:46 PM
  • TrustedSenders returns an instance of the IStrings object (http://www.dimastr.com/redemption/strings.htm), which supports Add/Delete/Clear/Insert  methods.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 8:09 PM
  • Dmitry,

    Ok, it looks like I cleared the list and added the a@b.com address to it but, when I look at it through Outlook junk mail, it is my same list, no changes were made.  Do I need to commit or write the changes somehow?

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      JunkOptions.TrustedSenders.Clear
      JunkOptions.TrustedSenders.Add ("a@b.com")
     
      For Each Address In JunkOptions.TrustedSenders
        'a.WriteLine (Address)
        Debug.Print Address
      Next

    Monday, March 5, 2012 8:26 PM
  • Yes, ypu need to call JunkOptions.Save

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 8:47 PM
  • Dmitry,

    Believe it or not, I actually figured that out.  However, does it take some time to update the safe sender list in the Outlook UI?  It does not seem to update the list unless I close Outlook and open it again.

    Thanks,

    Jim


    Monday, March 5, 2012 9:04 PM
  • Outlook is notorious for its caching behavior, so having to restart Outlook is to be expected unfortunately...


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.2 is now available!

    Monday, March 5, 2012 9:43 PM
  • Dmitry,

    Here is what I have found, if I run the procedures at application startup the safe sender list displays the updated values.  So, it looks like I'm done.  Thank you very much!

    Here is the final results;

    At application quit I run ExportSafeSenderList, at application start I run ClearSafeSenderList and then ImportSafeSenderList.

    Once Redemption is downloaded, installed, and “Redemption Outlook and MAPI COM Library” is selected as a reference, the following code should work;

    Sub ExportSafeSenderList()  'Export the current profile's Safe Sender List
     
      Dim varOutputPath As String
      Dim varOutputFileName As String
     
      Dim rSession As Object
      Dim fs As Object
      Dim a As Variant
      Dim JunkOptions As Object
      Dim Address As Variant

      varOutputPath = "C:\Email\"
      varOutputFileName = "SafeSenderList.txt"

      Set fs = CreateObject("Scripting.FileSystemObject")
      Set a = fs.CreateTextFile(varOutputPath & varOutputFileName, True)

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      For Each Address In JunkOptions.TrustedSenders
        a.WriteLine (Address)
        'Debug.Print Address
      Next
     
      a.Close

    End Sub

    Sub ClearSafeSenderList() 'Clear all values from the current profile's safe sender list

      Dim rSession As Object
      Dim JunkOptions As Object

      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
      JunkOptions.TrustedSenders.Clear
      JunkOptions.Save

    End Sub

    Sub ImportSafeSenderList() 'Add the values from a text file into the current profile's safe sender list
     
      Dim varOutputPath As String
      Dim varOutputFileName As String
     
      Dim fs As Object
      Dim a As Variant
      Dim varReturnString As String
     
      Dim rSession As Object
      Dim JunkOptions As Object
      Dim Address As Variant
     
      varOutputPath = "C:\Email\"
      varOutputFileName = "SafeSenderList.txt"
     
      Set rSession = CreateObject("Redemption.RDOSession")
      rSession.MAPIOBJECT = Application.Session.MAPIOBJECT
      Set JunkOptions = rSession.JunkEmailOptions
     
      Set fs = CreateObject("Scripting.FileSystemObject")
      Set a = fs.OpenTextFile(varOutputPath & varOutputFileName)    ', ForReading, False) <--remarked out because the "ForReading" clause throws and error
      Do While a.AtEndOfStream <> True

        varReturnString = a.ReadLine
        'Debug.Print varReturnString
        JunkOptions.TrustedSenders.Add (varReturnString)
       
      Loop
     
      JunkOptions.Save
       
      a.Close

    End Sub

    • Marked as answer by phonepagerfax Monday, March 5, 2012 9:50 PM
    Monday, March 5, 2012 9:50 PM