retrieving data from mails(outlook) using excel/vba

[Deepak]

Andrei_Costa , Aravindhan

Either start a new post or send a private message to @Sam Mathai Chacko

 

[ganeshmg]

Hi Sam, Have few queries in that else if using 2 constants
how to specify a specific mailbox so that it extract mails of that mailbox only.
please assist.

 

[Tharabai]

Hi,

is it possible to extract e-mails from only specific e-mail IDs (two e-mail ids) that too only the body of the e-mail in the table format and import to excel.

 

[Cheran]

Hi Sam,
Past two year i'm using the Application_NewMailEx code, which is working perfectly...
Now i want to modify the same to ... where the subject line has the particular word... i need to capture the same to excel....
Please help me...

 

[Monty]

Hello Sam Mathai Chacko

Can you help with the attached file which is on the same lines that is extracting outlook 2013 mails to excel 2013.

Below attached file works perfectly with home desktop as you know outlook is connected to SMTP/POP server...but the same code does not work in my office as outlook 2013 is connected exchange server..

Error when running the same code at office

1) Sender's name
2) Sender's Email

Am not really sure if that is problem or something else...please help.

Thanking you in advance!!!Cheers

 

[SrideviR]

Hi All,

Can you please let me know how to fetch email IDs for the IDs (say employee Id) in excel and update the EmailID in the adjacent columns

Regards
Sridevi

 

[debxxx1]

Here's one way to do it. You need to paste this in the OutlookSession code module

Const strFilePath As String = "C:\Users\Public\Documents\Excel\OutlookMailItemsDB.xlsx"
Const strSubjectLineStartWith As String = ""
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
  
    Dim varArray As Variant
    Dim strSub As String
    Dim strBody As String
    Dim strArray() As String
    Dim lngLoop As Long
    Dim objItem As Object
    Dim lngMailCounter As Long
    Dim objMItem As MailItem
    strArray = Split(EntryIDCollection, ",")
    For lngMailCounter = LBound(strArray) To UBound(strArray)
        Set objItem = Session.GetItemFromID(strArray(lngMailCounter))
        If TypeName(objItem) = "MailItem" And InStr(1, objItem.Subject, strSubjectLineStartWith) And InStr(1, objItem.Body, "") Then
            Set objMItem = objItem
            With CreateObject("Excel.Application").workbooks.Open(strFilePath)
                With .sheets(1)
                    With .cells(.rows.Count, 1).End(-4162)(2).resize(1, 7)
                        .Value = Array(objMItem.SenderEmailAddress, objMItem.To, objMItem.CC, objMItem.BCC, objMItem.Subject, objMItem.ReceivedTime, objMItem.Body)
                    End With
                End With
                .Close 1
            End With
            Set objItem = Nothing
        End If
    Next lngMailCounter
    If Not IsEmpty(strArray) Then
        Erase strArray
    End If
  
End Sub

In case you want to do this for only mails with certain subject, you can put that in the strSubjectLineStartWith variable. I've left this as "" as of now.

Thank You So Much