Consulting

Results 1 to 7 of 7

Thread: Outlook - Automate Export to file (.PST)

  1. #1

    Outlook - Automate Export to file (.PST)

    Hi,
    At work we have an Exchange shared mailbox that 5 of us use on a daily basis.

    We are nearing the size limit of the mailbox so I've decided to take on the task of backing up all of the contents on a regular basis. (Backup is essential because we'll need to keep records of all emails for at least two years or so).

    I can do this task manually, not a problem.
    File / Open / Import / Export to a file / Outlook Data File (.pst) / Select the folder (I want to capture everything in the mailbox, so I choose the mailbox name) / Dont copy duplicates / Finish (Then it starts copying)

    So it's fairly simple to do... But I was hoping that I could just automate this process and perform the backup at midnight.
    Is this possible?

    I do not want the emails to be moved / or deleted in any way. I just want to 'copy' the emails. The above task does this.... But can this be done via VBA - so I can just forget about it ?

    Thanks

  2. #2
    The Import/Export dialog is apparently not accessible from VBA, so I thought it would be amusing to see how far it would be possible to emulate this in VBA, and I have come up with a method that works here with several POP accounts. To what extent it will work with Exchange accounts I have not been able to test.

    Basically it creates a new backup PST file (each day) and adds it to Outlook (if the named file exists it opens the named file). A userform then displays the available message stores and the selected Outlook Store inbox (and its sub folders) is then copied to the backup PST file and the backup PSTfile is closed. I have not considered the possibility of automatic running of the macro, or automatically selecting the file to be backed up. It shouldn't affect your default file, but do back it up first just in case of a problem.

    The macro uses a userform to pick the Outlook Store. You can download that form (attached) and import it into the Outlook VBA editor. The rest of the code follows:
    The backup file is stored in the folder "C:\Path\" which must pre-exist. Change to suit your requirements.


    Option Explicit
    
    Sub BackUpEmailInPST()
    Dim olNS As Outlook.NameSpace
    Dim olBackup As Outlook.Folder
    Dim bFound As Boolean
    Dim strPath As String
    Dim strDisplayName As String
        strDisplayName = "Backup " & Format(Date, "yyyymmdd")
        strPath = "C:\Path\" & strDisplayName & ".pst"
        Set olNS = GetNamespace("MAPI")
        olNS.AddStore strPath
        Set olBackup = olNS.folders.GetLast
        olBackup.Name = strDisplayName
        RunBackup olNS, olBackup
        olNS.RemoveStore olBackup
    lbl_Exit:
        Set olNS = Nothing
        Set olBackup = Nothing
        Exit Sub
    End Sub
    
    Sub RunBackup(olNS As Outlook.NameSpace, olBackup As Outlook.Folder)
    Dim oFrm As New frmSelectAccount
    Dim strAcc As String
    Dim olStore As Store
    Dim olFolder As Folder
    Dim i As Long
        With oFrm
            .BackColor = RGB(191, 219, 255)
            .Height = 190
            .Width = 240
            .Caption = "Backup E-Mail"
            With .CommandButton1
                .Caption = "Next"
                .Height = 24
                .Width = 72
                .Top = 126
                .Left = 132
            End With
            With .CommandButton2
                .Caption = "Quit"
                .Height = 24
                .Width = 72
                .Top = 126
                .Left = 24
            End With
    
            With .ListBox1
                .Height = 72
                .Width = 180
                .Left = 24
                .Top = 42
                For Each olStore In olNS.Stores
                    If Not olStore.DisplayName = olBackup Then
                        .AddItem olStore
                    End If
                Next olStore
            End With
            With .Label1
                .BackColor = RGB(191, 219, 255)
                .Height = 24
                .Left = 24
                .Width = 174
                .Top = 6
                .Font.Size = 10
                .Caption = "Select e-mail store to backup"
                .TextAlign = fmTextAlignCenter
            End With
            .Show
            If .Tag = 0 Then GoTo lbl_Exit
            With oFrm.ListBox1
                For i = 0 To .ListCount - 1
                    If .Selected(i) Then
                        strAcc = .List(i)
                        Exit For
                    End If
                Next i
            End With
            Set olFolder = olNS.Stores(strAcc).GetDefaultFolder(olFolderInbox)
            olFolder.CopyTo olBackup
            DoEvents
            Set olFolder = olNS.Stores(strAcc).GetDefaultFolder(olFolderSentMail)
            olFolder.CopyTo olBackup
        End With
    lbl_Exit:
        Unload oFrm
        Set olStore = Nothing
        Set olFolder = Nothing
        Exit Sub
    End Sub
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Wow thank you very much, you certainly went out of your way to achieve this.
    I modified it for my own purposes, and I've been using it for the last week or so, and it seems to be working so fingers crossed.
    Thanks.

  4. #4
    VBAX Newbie
    Joined
    Dec 2016
    Posts
    1
    Location

    Thanks

    I am looking for a way to backup certain folders in my inbox to pst files
    Office 365 recommends to keep all mail online in inbox folders. I would like to backup certain project folders each to its own pst file to keep a backup for each and every project.
    In case there is an issue with Office 365 I still have a backup of my emails.....
    The VBA above makes copies of complete PST files
    Last edited by Erik123; 12-21-2016 at 02:19 AM.

  5. #5
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    1
    Location

    Thank you

    Quote Originally Posted by gmayor View Post
    The Import/Export dialog is apparently not accessible from VBA, so I thought it would be amusing to see how far it would be possible to emulate this in VBA, and I have come up with a method that works here with several POP accounts. To what extent it will work with Exchange accounts I have not been able to test.

    Basically it creates a new backup PST file (each day) and adds it to Outlook (if the named file exists it opens the named file). A userform then displays the available message stores and the selected Outlook Store inbox (and its sub folders) is then copied to the backup PST file and the backup PSTfile is closed. I have not considered the possibility of automatic running of the macro, or automatically selecting the file to be backed up. It shouldn't affect your default file, but do back it up first just in case of a problem.

    The macro uses a userform to pick the Outlook Store. You can download that form (attached) and import it into the Outlook VBA editor. The rest of the code follows:
    The backup file is stored in the folder "C:\Path\" which must pre-exist. Change to suit your requirements.


    Option Explicit
    
    Sub BackUpEmailInPST()
    Dim olNS As Outlook.NameSpace
    Dim olBackup As Outlook.Folder
    Dim bFound As Boolean
    Dim strPath As String
    Dim strDisplayName As String
        strDisplayName = "Backup " & Format(Date, "yyyymmdd")
        strPath = "C:\Path\" & strDisplayName & ".pst"
        Set olNS = GetNamespace("MAPI")
        olNS.AddStore strPath
        Set olBackup = olNS.folders.GetLast
        olBackup.Name = strDisplayName
        RunBackup olNS, olBackup
        olNS.RemoveStore olBackup
    lbl_Exit:
        Set olNS = Nothing
        Set olBackup = Nothing
        Exit Sub
    End Sub
    
    Sub RunBackup(olNS As Outlook.NameSpace, olBackup As Outlook.Folder)
    Dim oFrm As New frmSelectAccount
    Dim strAcc As String
    Dim olStore As Store
    Dim olFolder As Folder
    Dim i As Long
        With oFrm
            .BackColor = RGB(191, 219, 255)
            .Height = 190
            .Width = 240
            .Caption = "Backup E-Mail"
            With .CommandButton1
                .Caption = "Next"
                .Height = 24
                .Width = 72
                .Top = 126
                .Left = 132
            End With
            With .CommandButton2
                .Caption = "Quit"
                .Height = 24
                .Width = 72
                .Top = 126
                .Left = 24
            End With
    
            With .ListBox1
                .Height = 72
                .Width = 180
                .Left = 24
                .Top = 42
                For Each olStore In olNS.Stores
                    If Not olStore.DisplayName = olBackup Then
                        .AddItem olStore
                    End If
                Next olStore
            End With
            With .Label1
                .BackColor = RGB(191, 219, 255)
                .Height = 24
                .Left = 24
                .Width = 174
                .Top = 6
                .Font.Size = 10
                .Caption = "Select e-mail store to backup"
                .TextAlign = fmTextAlignCenter
            End With
            .Show
            If .Tag = 0 Then GoTo lbl_Exit
            With oFrm.ListBox1
                For i = 0 To .ListCount - 1
                    If .Selected(i) Then
                        strAcc = .List(i)
                        Exit For
                    End If
                Next i
            End With
            Set olFolder = olNS.Stores(strAcc).GetDefaultFolder(olFolderInbox)
            olFolder.CopyTo olBackup
            DoEvents
            Set olFolder = olNS.Stores(strAcc).GetDefaultFolder(olFolderSentMail)
            olFolder.CopyTo olBackup
        End With
    lbl_Exit:
        Unload oFrm
        Set olStore = Nothing
        Set olFolder = Nothing
        Exit Sub
    End Sub

  6. #6
    I don't understand the code well. Which lines are used to create the file? I would like to do something different but I can't understand which lines are important here to create the PST file. Thank you.

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    This thread is now closed.
    Please start a new Thread, you can link to this thread: http://www.vbaexpress.com/forum/showthread.php?52361
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •