Thank you for Visiting my Blog

Sunday, 19 March 2017

Move mail from one folder to another using macros or VBA

Hello Guys!!!


To move any mail item from one folder to another  using Macros or VBA you need to connect to your outlook mail .below I mentioned the step how I connected to mine  .........

Start sub function in your excel sheet inside Visual basics.

Sub Mail_Sheet_Outlook_Body()

' You need to use this module with the RangetoHTML subroutine.
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    ' You can also use a sheet name here.
    'Set rng = Sheets("YourSheet").UsedRange
    Set OutApp = CreateObject("Outlook.Application")

    On Error Resume Next
    Dim objSentFolder As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim sFilter As String
    Dim mySubject As String
    Dim myItem As Outlook.MailItem
    Dim myNewItem As Outlook.MailItem
    Dim Str As String
    Dim Mail_count As Integer
    Dim moveToFolder As Outlook.MAPIFolder
  
' set the input folder where actually mail exist  . in below example my mail exist in "temp" folder which is under inbox .

    Set objSentFolder = _
    OutApp.GetNamespace("MAPI").GetDefaultFolder(6).Folders("temp")
 
    Set myItems = objSentFolder.Items
   
    ' Setting the another folder where item should be moved. below example I am moving my mail from temp folder to Staging folder . both are under inbox.

    Set moveToFolder = OutApp.GetNamespace("MAPI").GetDefaultFolder(6).Folders("Staging")
     
'define the subject if you want any specific mail to move. below I am moving all the mail which is having subject learning
   
mySubject = "Learning"
   
   
    sFilter = "[Subject] = '" & mySubject & "'"
    myItem = myItems.Find(sFilter)
    Mail_count = myItems.Count
   
'below for loop will run to into temp folder till it find all the mail which is started from "Learning"

    For i = 1 To Mail_count
      If mySubject = myItems(i).mySubject Then
    
          
         myItems(i).Move moveToFolder

      End If
   
    Next i
   
  
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub



This is for today guys if you have any issue to understand this comment and let me know till than Happy learning...................
 

2 comments:

  1. Good one. It's very much informative in day to day life. Thanks for sharing this.

    ReplyDelete