• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MS Outlook - check subject line by VBA (in excel)

ThrottleWorks

Excel Ninja
Hi,

I need help regarding Outlook VBA. Apologies if not supposed to ask MS Outlook VBA queries on the Forum.

I have subject lines mentioned in an excel file, for example Range A1.

I need to check in Outlook Inbox if mail with subject line mentioned in range
A1 is sent or not.

For example if, value in Range A1 is Sample E-Mail then I want to check if inbox contains mail with subject line as “Sample E-Mail”.

Can anyone please share the code if possible.
 
@ ThrottleWorks

In a manual approach !!

Select all email & press Ctrl+C > goto excel > Ctrl+V.
You will get data like as

From Subject Received Size Categories

Based on the layout at outlook

Now do the manual match!!
 
Hi Deepak,

Thanks a lot for the help. I was not aware that such function is even allowed !

If possible could you please tell how to do it with VBA in excel.
 
Hi Deepak,

Thanks a lot for the help. I was not aware that such function is even allowed !

If possible could you please tell how to do it with VBA in excel.


On your cross post i found that u have achieved the goal!!
Is still something remain.
 
Hi Deepak,

The code from Ozgrd will work with some editing. But main problem is "picker".

I want to import the data without manual intervention.

As per "S O" from Ozgrid, macro will need intervention.
That is why am looking for alternative.
 
Hi Jagdev,

Thanks a lot for the help. However I am getting no results with the macro.

Also, as mentioned earlier I can not have manual intervention.

I am looking something similar to what Deepak has said.

"Select all email & press Ctrl+C > goto excel > Ctrl+V.
You will get data like as"

I want to perform this action with VBA.

Because this is a task I need to achive within the macro itself.

Have a nice weekend. :)
 
Check this..

Just tested & found working!!


Code:
Option Explicit
'This Code is Downloaded from OfficeTricks.com
'Visit this site for more such Free Code
Sub Download_Outlook_Mail_To_Excel()
    'Add Tools->References->"Microsoft Outlook nn.n Object Library"
    'nn.n varies as per our Outlook Installation
    Dim Folder As Outlook.MAPIFolder
    Dim sFolders As Outlook.MAPIFolder
    Dim iRow As Integer, oRow As Integer
    Dim MailBoxName As String, Pst_Folder_Name  As String
   
    'Mailbox or PST Main Folder Name (As how it is displayed in your Outlook Session)
    MailBoxName = "Mailbox - Deepak Kumar"
    'Mailbox Folder or PST Folder Name (As how it is displayed in your Outlook Session)
    Pst_Folder_Name = "Inbox" 'Sample "Inbox" or "Sent Items"
    'To directly a Folder at a high level
    'Set Folder = Outlook.Session.Folders(MailBoxName).Folders(Pst_Folder_Name)
   
    'To access a main folder or a subfolder (level-1)
    For Each Folder In Outlook.Session.Folders(MailBoxName).Folders
        If VBA.UCase(Folder.Name) = VBA.UCase(Pst_Folder_Name) Then GoTo Label_Folder_Found
        For Each sFolders In Folder.Folders
            If VBA.UCase(sFolders.Name) = VBA.UCase(Pst_Folder_Name) Then
                Set Folder = sFolders
                GoTo Label_Folder_Found
            End If
        Next sFolders
    Next Folder
Label_Folder_Found:
    If Folder.Name = "" Then
        MsgBox "Invalid Data in Input"
        GoTo End_Lbl1:
    End If
    'Read Through each Mail and export the details to Excel for Email Archival
    ThisWorkbook.Sheets(1).Activate
'  Folder.Items.Sort "Received"
   
    'Insert Column Headers
    ThisWorkbook.Sheets(1).Cells(1, 1) = "Sender"
    ThisWorkbook.Sheets(1).Cells(1, 2) = "Subject"
    ThisWorkbook.Sheets(1).Cells(1, 3) = "Date"
    ThisWorkbook.Sheets(1).Cells(1, 4) = "Size"
    ThisWorkbook.Sheets(1).Cells(1, 5) = "EmailID"
    'ThisWorkbook.Sheets(1).Cells(1, 6) = "Body"
   
    'Export eMail Data from PST Folder
    oRow = 1
    For iRow = 1 To Folder.Items.Count
        'If condition to import mails received in last 60 days
        'To import all emails, comment or remove this IF condition
    '  If VBA.DateValue(VBA.Now) - VBA.DateValue(Folder.Items.Item(iRow).ReceivedTime) <= 60 Then
          oRow = oRow + 1
          ThisWorkbook.Sheets(1).Cells(oRow, 1).Select
          ThisWorkbook.Sheets(1).Cells(oRow, 1) = Folder.Items.Item(iRow).SenderName
          ThisWorkbook.Sheets(1).Cells(oRow, 2) = Folder.Items.Item(iRow).Subject
          ThisWorkbook.Sheets(1).Cells(oRow, 3) = Folder.Items.Item(iRow).ReceivedTime
          ThisWorkbook.Sheets(1).Cells(oRow, 4) = Folder.Items.Item(iRow).Size
          ThisWorkbook.Sheets(1).Cells(oRow, 5) = Folder.Items.Item(iRow).SenderEmailAddress
          'ThisWorkbook.Sheets(1).Cells(oRow, 6) = Folder.Items.Item(iRow).Body
    '    End If
    Next iRow
    MsgBox "Outlook Mails Extracted to Excel"
    Set Folder = Nothing
    Set sFolders = Nothing
   
End_Lbl1:
End Sub
ref from http://officetricks.com/outlook-email-download-to-excel/

Or

https://techniclee.wordpress.com/2011/10/29/exporting-outlook-messages-to-excel/

It's having lot's of variation...

upload_2015-6-26_18-37-27.png

Goto Appropriate

https://techniclee.wordpress.com/2011/10/29/exporting-outlook-messages-to-excel/
 
Hi Deepak,

Thanks a lot for your help and valuable time.

I am getting a bug while running the macro.
I have changed following line to

MailBoxName = "Inbox - name.surname@company.com"

If you could possibly look into this..

Even if you don't have time that's fine with me.

Thanks a lot once again. Good night.
 

Attachments

  • Error.png
    Error.png
    9.4 KB · Views: 4
Hi,

The macro is working perfect. Would like to share an observation of mine.

When I cpoied the above mentioned code in a new workbook.
Following line gave me a bug.

"Dim Folder as Outlook.MAPIFolder"

However, I overwrite this code in previous macro of mine and it's working.
Though I am not facing any problem now but it seems strange to me.

Also, when I run the maro, it ask for permission (need to click allow).

Is there any way where we can turn off this pop-up.
 

Attachments

  • Error A1.png
    Error A1.png
    27 KB · Views: 9
  • Error A2.png
    Error A2.png
    18 KB · Views: 8
  • Error B1.png
    Error B1.png
    27.3 KB · Views: 9
Back
Top