+ Reply to Thread
Results 1 to 2 of 2

Outlook macro to filter mails

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    58

    Outlook macro to filter mails

    Hi Dear Experts, Hope all of doing safe and sound!

    well, I'm having a serious case that I'm getting lots of mails from various clients and I need to find a way to filter a a specific client mails and export them to excel report with defined criteria.

    so I will be pleased if someone can help me.


    I want to go through all mails at specific inbox, then target mails with sender (test@sample.com). then define the duration period for target mail (for ex. from 10 jan to 15 jan 2021)

    all mails subjects will be as following:

    New :: [#85236951] New Task Proposal COVID – EN-US :: WO42633 :: New Work Request :: Matrix Medical Network

    I need to export those subjects as exited on excel sheet attached.

    cross-Posting link:: http://www.vbaexpress.com/forum/show...o-excel-report
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Ethen5155; 01-19-2021 at 03:09 AM. Reason: cross posting link inserted

  2. #2
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    58

    Re: Outlook macro to filter mails

    Solved now by gmayor

    ption Explicit

    Sub subject2excel()
    'Graham Mayor - https://www.gmayor.com - Last updated - 19 Jan 2021
    Dim olFolder As Outlook.Folder
    Dim olItem As MailItem 'Object
    Dim i As Long, j As Long, k As Long
    Dim vSubject As Variant, vLanguage As Variant
    Dim sType As String
    Dim lNumber As Long
    Dim sSubject As String, sLanguage As String, sD1 As String, sD2 As String, sD3 As String
    Dim strValues As String
    Dim lFrom As Long, lTo As Long
    Dim lDate As Long


    Const strSender As String = "someone@somewhere.com" 'insert sender
    Const sWorkbook As String = "C:\Path\Report.xlsx" ' the location of the workbook

    'date range
    lFrom = 20210110: lTo = 20210115


    Set olFolder = Session.PickFolder
    For i = 1 To olFolder.items.Count
    Set olItem = olFolder.items(i)
    If olItem.SenderEmailAddress = strSender Then
    lDate = Val(Format(olItem.ReceivedTime, "yyyymmdd"))
    'Debug.Print lDate
    If lDate >= lFrom And lDate <= lTo Then
    vSubject = Split(olItem.Subject, "::")
    If UBound(vSubject) = 4 Then
    For j = 0 To UBound(vSubject)
    Select Case j
    Case 0: sType = vSubject(j)
    Case 1
    lNumber = Replace(Split(vSubject(j), "]")(0), "[#", "")
    sLanguage = Trim(Right(vSubject(j), 6))
    sSubject = Trim(Split(vSubject(j), "]")(1))
    sSubject = Left(sSubject, Len(sSubject) - 8)
    Case 2: sD1 = vSubject(j)
    Case 3: sD2 = vSubject(j)
    Case 4: sD3 = vSubject(j)
    End Select
    Next j
    strValues = sType & "', '" & _
    lNumber & "', '" & _
    sSubject & "', '" & _
    sLanguage & "', '" & _
    sD1 & "', '" & _
    sD2 & "', '" & _
    sD3
    WriteToWorksheet sWorkbook, "Sheet1", strValues
    DoEvents
    End If
    End If
    End If
    Next i
    lbl_Exit:
    Set olFolder = Nothing
    Set olItem = Nothing
    Exit Sub
    End Sub


    Private Function WriteToWorksheet(strWorkbook As String, _
    strRange As String, _
    strValues As String)
    Dim ConnectionString As String
    Dim strSQL As String
    Dim CN As Object

    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & strWorkbook & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
    strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
    Set CN = CreateObject("ADODB.Connection")
    Call CN.Open(ConnectionString)
    Call CN.Execute(strSQL, , 1 Or 128)
    CN.Close
    Set CN = Nothing
    lbl_Exit:
    Exit Function
    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy the mails from Outlook
    By krishnakuma6 in forum Excel General
    Replies: 3
    Last Post: 01-11-2016, 07:03 AM
  2. [SOLVED] Macro to send mails from outlook as per the email ids in excel
    By arun.sj in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-03-2015, 12:10 PM
  3. Outlook Autoforward mails to abc id if it has xyz in either to from or cc
    By sumitjhaxl in forum Outlook Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2015, 05:34 AM
  4. Excel containing macro to send bulk mails from outlook with multiple attachments
    By amandeep08 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 02:19 PM
  5. Get e-mails from outlook inbox
    By Peter1245 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 06-26-2013, 10:49 AM
  6. mails to outlook
    By vimalanathk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2013, 05:52 AM
  7. Conversion of macro code for sending mails from outlook to lotus notes
    By praveen2825 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 08:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1