Specifying an email address and folder name(Not default) if i have more than 1 email address

[bnfkru4567]

I need all your Excel VBA expert to help.

The following code is working but I have a few following questions
1) If I have more than 1 email address in Outlook and would like to specific email address in Excel . Let say in tab, cell (A1 ) in Sheets("Main") is for me to type email address(group email address). How can I specific it in the following code

2) If I have to specific a folder (not default folder--inbox,sent,draft, delete) and would like to specific in folder name in Excel Let say in , cell (B1) in Sheets ("Main") is for me to type folder name. How can I specific it in the following code
=====================================================

Sub GetFromInbox()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim i, ij As Integer
Dim tt As Date

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1
ij = 0
x = Date

' Now. the following "For each next " code starts to look in the oldest email!



For Each olMail In Fldr.Items
ij = ij + 1
'If IsNumeric((Format(olMail.ReceivedTime, "dd/mm/yy"))) Then
Sheets("test").Range("a1").Select
Sheets("test").Range("I1").Clear
Sheets("test").Range("I2") = ij
Sheets("test").Range("I1").Value = (Format(olMail.ReceivedTime, "dd/mm/yy"))
Sheets("test").Range("I1").NumberFormat = "dd/mm/yy"
tt = Sheets("test").Range("I1")
' MsgBox ("Y-tt=" & tt & " receivedtime=" & olMail.ReceivedTime)
'Else
'tt = 0
'MsgBox ("N-tt=" & tt & " receivedtime=" & olMail.ReceivedTime)
'End If
' tt = CDate(Format(olMail.ReceivedTime, "dd/mm/yy"))
If tt >= Range("H1") Then
'If InStr(olMail.Subject, "others") > 0 And tt >= Range("h1") Then
If InStr(olMail.Subject, "others") > 0 Then
ActiveSheet.Range("h2") = "y"
ActiveSheet.Cells(i, 1).Value = olMail.Subject
ActiveSheet.Cells(i, 2).Value = olMail.ReceivedTime
ActiveSheet.Cells(i, 3).Value = olMail.SenderName
tt = CDate(Format(olMail.ReceivedTime, "dd/mm/yy"))
ActiveSheet.Cells(i, 4).Value = CDate(Format(olMail.ReceivedTime, "dd/mm/yy"))
' tt = ActiveSheet.Cells(i, 4).Value
ActiveSheet.Cells(i, 5).Value = (Format(olMail.ReceivedTime, "hh:mm"))
MsgBox ("tt=" & tt)
i = i + 1
End If
Else
Sheets("test").Range("h2") = "N"
End If
Next olMail

Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
'tt = ""

End Sub

 

[Dan_W]

I'm sorry - I've really tried, but I cannot for the life of me work out what it is that you're asking for or what you're trying to do with this code.

First, when you say "like to specific email address", do you mean "specify" an email address? If so, I'm not sure where it would go in the code because I don't know what the code is trying to do. I've read a couple of times, and whatever it is you want it to do, it's not going to work - you code rewrites over anything it's already written to the spreadsheet such that it becomes meaningless.

I don't have an understanding of the workbook structure either. Your question references Sheets("Main") but your code references Sheets("test') and Activesheet. Is the latter mean to be Sheets("Main")? If so, you're need to stop using Activesheet, because it is only ever going to refer to test given that you code keeps refocusing on test when you execute Sheets("test").Range("a1").Select at the outset of each loop.

 

[Dan_W]

Ok - scratch that - I think I worked out what you want.

Are you looking to import the details of those emails that meet certain date requirements from a designated folder of a specific email address? And what you're asking is (1) how do you specify the email address from which to import the emails; and (2) from which folder in that email account? I'm guessing that your outlook has multiple accounts in it, and judging from your code (and a lot of the code I found online when trying to work out how to answer your question) they all seem to point to the default folder in the default account.

If that's the case, try this - replace:

Set Fldr = olNs.GetDefaultFolder(olFolderInbox)

with:

Set Fldr = olNs.PickFolder

This should present you with a dialog box that shows all available email accounts, and all folders in each email account, from which the user can specify the preferred target folder.

Alternatively, if you need to do it programmatically (i.e., without a dialog box), then you could try this - replace the same line in your code with the following:

MailBoxName = Sheets("Main").Range("A1").Value
FolderName = Sheets("Main").Range("B1").Value
Set Fldr = olApp.Session.Folders(MailBoxName).Folders(FolderName)

The MailBoxName and FolderName need to be written in A1 and B1, however, exactly the same way as it is in Outlook. It must be the same spelling, capitalisation, etc.

Hopefully that's what you wanted. I still expect your code will throw up some errors, though, for the reasons outlined above.

 

[bnfkru4567]

Hi Dan

Thanks for your help

You are quite right--- I have a few email accounts in Outlook. The main one is treated as Default but I need to point to other folder in other email account.

After I tried your code, it is working.

Well Done