Creating a Queue in Excel

[ryanl1204]

I'm brand new to VBA and I don't really know what I'm doing yet. I searched google to find a way to create a queue system in excel.

Here is what I would like to do:

Pull emails from outlook into a spreadsheet and click an add button that would add the From and Subject of the email to the bottom of the list with a timestamp showing the time the add button was pressed for that email. I would like the list to have the oldest timestamps stay at the top of the list, if possible.

I would be more than happy to provide more detail if anyone is able to help.

Thanks.

 

[s.ridd]

Here's something rough which I have put together that you can adapt. You'll need to assign this procedure to a button. It assumes that you have your headers already in before you run for the first time.

It will pull in all emails received since the last time you pressed the button. It will place them in reverse order, but seeing as you aren't recording the received time, I assume that it doesn't matter. Lastly, you'll need to set a reference to the Outlook Object Library, to do this click on Tools>References and then choose Microsoft Outlook x.0 Object Library, where x represents the version number of Outlook.

Sub AddEmailDetails()

Dim outApp As Outlook.Application
Dim outItems As Outlook.Items
Dim i As Integer
Dim rng As Range
Dim dNow As Date


    dNow = Now
    Set outApp = Outlook.Application
    Set rng = Cells(Cells(1, 1).CurrentRegion.Rows.Count, 1)
    Set outItems = outApp.Session.GetDefaultFolder(olFolderInbox).Items.Restrict("[Received] > '" _
        & Format(rng.Offset(0, 2), "ddddd h:nn AMPM") & "'")


    For i = 1 To outItems.Count
        rng.Offset(i, 0) = outItems(1).Sender
        rng.Offset(i, 1) = outItems(1).Subject
        rng.Offset(i, 2) = dNow
    Next


Set outItems = Nothing
Set rng = Nothing
Set outApp = Nothing


End Sub

Chances are you'll probably get a warning from Outlook because you're accessing the Sender property. If this is the case and it isn't acceptable then ways of avoiding this include, but are not limited to:

• changing this code so it sits in Outlook
• downloading Redemption

Please amend this code to suit your needs. Hope this helps

Simon

 

[ryanl1204]

Thank you! I was able to get this to work.