none
How to copy email files from Outlook to C drive by VBA? RRS feed

  • Question

  • How to copy email files by VBA from

    Folder in Outlook; say "Incoming"

    to

    Folder in C drive; say "C:\In"

    Tuesday, November 14, 2017 6:42 AM

Answers

  • Hello,

    You can use the SaveAs method of the MailItem class which saves the Microsoft Outlook item to the specified path and in the format of the specified file type. If the file type is not specified, the MSG format (.msg) is used.

    Sub SaveAsTXT() 
     Dim myItem As Outlook.Inspector 
     Dim objItem As Object 
    
     Set myItem = Application.ActiveInspector 
     If Not TypeName(myItem) = "Nothing" Then 
     Set objItem = myItem.CurrentItem 
     strname = objItem.Subject 
     'Prompt the user for confirmation 
     Dim strPrompt As String 
     strPrompt = "Are you sure you want to save the item? " &; _ 
     "If a file with the same name already exists, " &; _ 
     "it will be overwritten with this copy of the file." 
     If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then 
     objItem.SaveAs "C:\In\" &; strname &; ".txt", olTXT 
     End If 
     Else 
     MsgBox "There is no current active inspector." 
     End If 
    End Sub

    Note, the system drive (C:) requires admin privileges.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    • Marked as answer by london7871 Wednesday, November 22, 2017 3:44 AM
    Tuesday, November 14, 2017 9:05 AM
  • Hi london7871,

    still looks like you are not using the code in correct way.

    you need to follow the folder hierarchy.

    I can see that you are not following it properly cause this error.

    see your code below.

    I can see that "Ivan emailabc-Sundries for others" is your account name. you not need to pass account name.

    just maintain the folder hierarchy.

    if we see the folder structure.

    Get Mail is inside the folder which name is written in Chinese/ Japanese language.

    I can see that you did not pass that folder name in your code.

    so it is not working.

    see my same working example again in video below will give you an idea.

    you can see that I have Inbox folder, in which I have demo folder, in which I have sub demo folder.

    everything is working correctly.

    if you are not sure that in which folder you are moving then try to print the name of the folder in immediate window will help you.

    modified example:

    Sub demo1()
    
        Dim objNS As Outlook.NameSpace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders.GetFirst ' folders of your current account
        Set objFolder = objFolder.Folders("Inbox")
        Debug.Print (objFolder.Name)
        Set objFolder = objFolder.Folders("demo")
        Debug.Print (objFolder.Name)
         Set objFolder = objFolder.Folders("sub demo")
         Debug.Print (objFolder.Name)
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
                ' ... do stuff here ...
                path = "C:\Users\v-padee\Desktop\save_mail\" & Item.Subject & ".msg"
                Item.SaveAs (path)
                'Debug.Print Item.ConversationTopic
            End If
        Next
    
    End Sub
    

    Folder names in immediate window.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 21, 2017 6:44 AM
    Moderator
  • Hi london,

    It seems you have got the expected folder, I would suggest you mark the helpful reply as answer.

    For this new error, I would suggest you post a new thread, and then we could focus on this specific issue in new thread.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by london7871 Wednesday, November 22, 2017 3:43 AM
    Tuesday, November 21, 2017 1:00 PM
  • Thanks Eugene Astafiev,Deepak,Tao Zhou,

    the VBA below can move files from Outlook to my folder, though the subject name cannot be copied.

    Sub demo()
    
        Dim objNS As Outlook.Namespace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders("Ivan emailabc-Sundries for others")
        Set objFolder = objFolder.Folders("Get Email")
        
        
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
              path = "C:\Users\DK-01\Desktop\Scan\" & Item.Subject & ".msg"
              ' path = "C:\Users\DK-01\Desktop\Scan\" & i & ".msg"
               Item.SaveAs (path)
              '  i = i + 1
            End If
        Next
    End Sub
    

    It can only copy filename of email e.g. 122, ABC, 11 abc, 11-abc,

    but not for 11:ABC, 11/ABC etc.

    I will treat this thread as solved and will post a thread to seek for solution for copying subject name.


    • Edited by london7871 Wednesday, November 22, 2017 2:14 AM
    • Marked as answer by london7871 Wednesday, November 22, 2017 2:14 AM
    Wednesday, November 22, 2017 2:11 AM

All replies

  • Hello,

    You can use the SaveAs method of the MailItem class which saves the Microsoft Outlook item to the specified path and in the format of the specified file type. If the file type is not specified, the MSG format (.msg) is used.

    Sub SaveAsTXT() 
     Dim myItem As Outlook.Inspector 
     Dim objItem As Object 
    
     Set myItem = Application.ActiveInspector 
     If Not TypeName(myItem) = "Nothing" Then 
     Set objItem = myItem.CurrentItem 
     strname = objItem.Subject 
     'Prompt the user for confirmation 
     Dim strPrompt As String 
     strPrompt = "Are you sure you want to save the item? " &; _ 
     "If a file with the same name already exists, " &; _ 
     "it will be overwritten with this copy of the file." 
     If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then 
     objItem.SaveAs "C:\In\" &; strname &; ".txt", olTXT 
     End If 
     Else 
     MsgBox "There is no current active inspector." 
     End If 
    End Sub

    Note, the system drive (C:) requires admin privileges.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    • Marked as answer by london7871 Wednesday, November 22, 2017 3:44 AM
    Tuesday, November 14, 2017 9:05 AM
  • How to revise VBA that:
    1. Folder of Outlook is "Incoming";
    2. No need for prompt; as existing file can be overwritten;
    3. File type should be msg.

    Besides, how to check whether drive (C:) already has admin privileges?
    Tuesday, November 14, 2017 10:18 AM
  • Hi,

    you need to modify the example given by the Eugene Astafiev as per your requirement.

    you need to  loop through all the mails in incoming folder and save it as an .msg file.

    below is an example to loop through mails.

    Sub demo()
    
        Dim objNS As Outlook.NameSpace
        Dim objFolder As Outlook.MAPIFolder
    
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders.GetFirst ' folders of your current account
        Set objFolder = objFolder.Folders("Inbox")
    
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
                ' ... do stuff here ...
                Debug.Print Item.ConversationTopic
            End If
        Next
    
    End Sub

    now you just need to add one line of code to save mail item as msg file using "mailItem.SaveAs"  where I use debug.print in code above.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 15, 2017 3:17 AM
    Moderator
  • Deepak,

    As I'm a beginner to VBA so I don't know how to modify the VBA to suit your suggestion.

    Wednesday, November 15, 2017 4:30 AM
  • Hi,

    below is complete example.

    Sub demo()
    
        Dim objNS As Outlook.NameSpace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders.GetFirst ' folders of your current account
        Set objFolder = objFolder.Folders("Inbox")
    
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
                ' ... do stuff here ...
                path = "C:\Users\v-padee\Desktop\save_mail\" & Item.Subject & ".msg"
                Item.SaveAs (path)
                Debug.Print Item.ConversationTopic
            End If
        Next
    
    End Sub

    you need to change the path as per your requirement.

    further I suggest you to study the Outlook Object Model will give you information about Events , properties and methods of Outlook Object Model.

    Object model (Outlook VBA reference)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 3:14 AM
    Moderator
  • How to revise the code:
     Set objFolder = objNS.Folders.GetFirst ' folders of your current account
    if the folders of current account is "Emailabc"
    Thursday, November 16, 2017 6:43 AM
  • Hi london7871,

    you had mentioned that ,"if the folders of current account is "Emailabc""

    do you mean you want to loop through all the mails inside "Emailabc" folder?

    if yes then you can directly pass the name of the folder like below.

    Set objFolder = objFolder.Folders("Emailabc")

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 8:46 AM
    Moderator
  • I want to loop through all the mails inside "Get Email" folder as attached.

    How to revise the code below:
    Set objFolder = objNS.Folders.GetFirst ' folders of your current account
        Set objFolder = objFolder.Folders("Inbox")

    Friday, November 17, 2017 1:55 AM
  • Hi london7871,

    you just need to change the Folder name.

    Set objFolder = objFolder.Folders("Get Email")

    I can see that you are using multiple accounts.

    when you run the code, you need to make sure that Account is active. otherwise it will not able to find the folder.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 17, 2017 8:17 AM
    Moderator
  • How to revise the code below:
    Set objFolder = objNS.Folders.GetFirst ' folders of your current account

    NB. "Folder "Get Mail" is under "Ivan emailabc-Sundries for others"

    • Edited by london7871 Saturday, November 18, 2017 1:49 AM
    Saturday, November 18, 2017 1:48 AM
  • Hi london7871,

    you need to refer the Object model. Object Model contains all the information about methods , properties and events.

    GetFirst method of used to locate the first folder.

    Folders.GetFirst Method (Outlook)

    if you want to access sub folder then you can try to use Folder.Folders Property.

    it Returns the  Folders collection that represents all the folders contained in the specified Folder. Read-only.

    Folder.Folders Property (Outlook)

    Folders.("Folder name").Folders.("Folder name")

    so I hope now you get the idea how to select sub folder.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 20, 2017 1:14 AM
    Moderator
  • I have tried below for all files in subfolder "Get Mail" under folder "Ivan emailabc-Sundries for others" but failed:

    Sub demo()    Dim objNS As Outlook.Namespace    Dim objFolder As Outlook.MAPIFolder    Dim path As String    Set objNS = GetNamespace("MAPI")    'Set objFolder = objNS.Folders.GetFirst ' folders of your current account    'Set objFolder = objFolder.Folders("Inbox")' The following are tried:    'Set objFolder =  Folders.("Ivan emailabc-Sundries for others").Folders.("Get Email")    'Set objFolder = objFolder.Folders("Get Email")    'Set objFolder = Folders("Ivan emailabc-Sundries for others").Folders("Get Email")    For Each Item In objFolder.Items        If TypeName(Item) = "MailItem" Then            ' ... do stuff here ...            path = "C:\Users\DK-01\Desktop\Scan\" & Item.Subject & ".msg"            Item.SaveAs (path)            Debug.Print Item.ConversationTopic        End If    NextEnd Sub

            
    Tuesday, November 21, 2017 5:52 AM
  • Hi london7871,

    still looks like you are not using the code in correct way.

    you need to follow the folder hierarchy.

    I can see that you are not following it properly cause this error.

    see your code below.

    I can see that "Ivan emailabc-Sundries for others" is your account name. you not need to pass account name.

    just maintain the folder hierarchy.

    if we see the folder structure.

    Get Mail is inside the folder which name is written in Chinese/ Japanese language.

    I can see that you did not pass that folder name in your code.

    so it is not working.

    see my same working example again in video below will give you an idea.

    you can see that I have Inbox folder, in which I have demo folder, in which I have sub demo folder.

    everything is working correctly.

    if you are not sure that in which folder you are moving then try to print the name of the folder in immediate window will help you.

    modified example:

    Sub demo1()
    
        Dim objNS As Outlook.NameSpace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders.GetFirst ' folders of your current account
        Set objFolder = objFolder.Folders("Inbox")
        Debug.Print (objFolder.Name)
        Set objFolder = objFolder.Folders("demo")
        Debug.Print (objFolder.Name)
         Set objFolder = objFolder.Folders("sub demo")
         Debug.Print (objFolder.Name)
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
                ' ... do stuff here ...
                path = "C:\Users\v-padee\Desktop\save_mail\" & Item.Subject & ".msg"
                Item.SaveAs (path)
                'Debug.Print Item.ConversationTopic
            End If
        Next
    
    End Sub
    

    Folder names in immediate window.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 21, 2017 6:44 AM
    Moderator
  • My folder structure is revised as attached jpeg.

    Please show how to incorporate the "Ivan emailabc-Sundries for others" and "Get Mail" in your VBA.


    Tuesday, November 21, 2017 8:45 AM
  • Hi london,

    For your full requirement, since you are not familiar with Outlook VBA Developing, I think we split your requirement into multiple parts.

    First, we could try to get the Folder in Outlook. For this requirement, I suggest you try below code:

    Sub FindFolder()
        Dim objNS As Outlook.NameSpace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders("Ivan emailabc-Sundries for others")
        Set objFolder = objFolder.Folders("Get Mail")
        MsgBox (objFolder.Name)
    End Sub

    Will it show the expected Folder Name?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 21, 2017 9:31 AM
  • OK show the "Get Email" upon the code is revised as:

    Set objFolder = objFolder.Folders("Get Email")



    • Edited by london7871 Tuesday, November 21, 2017 10:14 AM
    Tuesday, November 21, 2017 10:07 AM
  • If the VBA is revised as follows,

    Sub demo()
    
        Dim objNS As Outlook.Namespace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders("Ivan emailabc-Sundries for others")
        Set objFolder = objFolder.Folders("Get Email")
        
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
                ' ... do stuff here ...
                path = "C:\Users\DK-01\Desktop\Scan\" & Item.Subject & ".msg"
                Item.SaveAs (path)
                Debug.Print Item.ConversationTopic
            End If
        Next
    
    End Sub

    There is error at:

     Item.SaveAs (path)
    Tuesday, November 21, 2017 10:26 AM
  • Hi london,

    It seems you have got the expected folder, I would suggest you mark the helpful reply as answer.

    For this new error, I would suggest you post a new thread, and then we could focus on this specific issue in new thread.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by london7871 Wednesday, November 22, 2017 3:43 AM
    Tuesday, November 21, 2017 1:00 PM
  • Thanks Eugene Astafiev,Deepak,Tao Zhou,

    the VBA below can move files from Outlook to my folder, though the subject name cannot be copied.

    Sub demo()
    
        Dim objNS As Outlook.Namespace
        Dim objFolder As Outlook.MAPIFolder
        Dim path As String
        Set objNS = GetNamespace("MAPI")
        Set objFolder = objNS.Folders("Ivan emailabc-Sundries for others")
        Set objFolder = objFolder.Folders("Get Email")
        
        
        For Each Item In objFolder.Items
            If TypeName(Item) = "MailItem" Then
              path = "C:\Users\DK-01\Desktop\Scan\" & Item.Subject & ".msg"
              ' path = "C:\Users\DK-01\Desktop\Scan\" & i & ".msg"
               Item.SaveAs (path)
              '  i = i + 1
            End If
        Next
    End Sub
    

    It can only copy filename of email e.g. 122, ABC, 11 abc, 11-abc,

    but not for 11:ABC, 11/ABC etc.

    I will treat this thread as solved and will post a thread to seek for solution for copying subject name.


    • Edited by london7871 Wednesday, November 22, 2017 2:14 AM
    • Marked as answer by london7871 Wednesday, November 22, 2017 2:14 AM
    Wednesday, November 22, 2017 2:11 AM
  • Hi london,

    Would you mind marking the reply from Eugene Astafiev,Deepak,Tao Zhou as answer after all they provide the solid solutions for your issue?

    It is discouraged you mark your own reply which is the suggestion from them.

    Regards,

    Tony


    Help each other

    Wednesday, November 22, 2017 2:24 AM
  • Noted and have marked their reply as answer.
    Wednesday, November 22, 2017 3:58 AM