Pull email from specified folder instead of folderpicker

coryjacques

New Member
Joined
May 17, 2019
Messages
14
The below code allows me to select a folder from outlook and pull some information from it. I didn't create this code - someone way more intelligent did! What I'm having issues with is getting rid of the selector for folder; I want to specify a mail folder within the code that will never vary. I've tried a few things that don't seem to work... Any suggestions?

Code:
Sub GetMailInfo()
Dim results() As String
  ' get contacts
  results = ExportEmails(True)

  Range(Cells(1, 1), Cells(UBound(results), UBound(results, 2))).Value = results
    MsgBox "Completed"
End Sub
Function ExportEmails(Optional headerRow As Boolean = False) As String()
Dim objOutlook As Object ' Outlook.Application
Dim objNamespace As Object ' Outlook.Namespace
Dim strFolderName As Object
Dim objMailbox As Object
Dim objFolder As Object
Dim mailFolderItems As Object ' Outlook.items
Dim folderItem As Object
Dim msg As Object ' Outlook.MailItem
Dim tempString() As String
Dim i As Long
Dim numRows As Long
Dim startRow As Long
Dim jAttach As Long ' counter for attachments
Dim debugMsg As Integer
Sheets("OutlookResults").Select
Sheets("OutlookResults").Cells.ClearContents
Range("A1").Select
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set strFolderName = objNamespace.PickFolder
Set mailFolderItems = strFolderName.Items
  If headerRow Then
    startRow = 1
  Else
    startRow = 0
  End If
  numRows = mailFolderItems.Count
  ReDim tempString(1 To (numRows + startRow), 1 To 100)
  For i = 1 To numRows
    Set folderItem = mailFolderItems.Item(i)
    If IsMail(folderItem) Then
      Set msg = folderItem
    End If
    With msg
      tempString(i + startRow, 1) = .Subject
      tempString(i + startRow, 2) = .SenderName
      tempString(i + startRow, 3) = .SentOn
      tempString(i + startRow, 4) = .ReceivedTime
      tempString(i + startRow, 5) = .To
    End With
  Next i
  ' first row of array should be header values
  If headerRow Then
    tempString(1, 1) = "Subject"
    tempString(1, 2) = "SenderName"
    tempString(1, 3) = "SentOn"
    tempString(1, 4) = "Received Time"
    tempString(1, 5) = "To"
  End If
  ExportEmails = tempString

End Function
Function IsMail(itm As Object) As Boolean
  IsMail = (TypeName(itm) = "MailItem")
End Function
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,305
Add this after the PickFolder line in your original code:
Code:
MsgBox strFolderName.FolderPath
and tell me what is displayed in the message box when you run it.

For example, if "\\MyAccount\folder1\subfolder1" is displayed, replace the PickFolder line with:
Code:
    Set strFolderName = objNamespace.Folders("MyAccount").Folders("folder1").Folders("subfolder1")
 

Watch MrExcel Video

Forum statistics

Threads
1,101,820
Messages
5,483,095
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top