Deleting selected emails using Access and Outlook automation

[Pat]

I have a database I am using to try to delete emails from selected folders, it doesn't work, I have a few commands with comments following showing '??????????????
The first problem is when outlook is not loaded it bombs on the following command:
Set oOutlook = GetObject(, "Outlook.Application") '????????????????why doesn't this work if outlook is not loaded

When i start Outlook so it doesn't execute the following:
vTo = oMessage.From '???????????????Object doesn't support this property or method

If it ends with an error the following command fails:
rsQ.Close '???????????????????how to test if rsQ has been used???

Can someone please provide answers as I am stymied.

[HansV]

1) GetObject obtains a reference to a running application. If the application isn't running, it fails. So you have to place a line

On Error Resume Next

above the line with GetObject. Then test whether the variable is Nothing, and turn on normal error handling afterwards (you're already doing both).

2) If you look up the MailItem object in the Outlook VBA help, you'll see that there is no From property. A MailItem object has a SenderName property. This is the Display Name of the sender, not necessarily the e-mail address of the sender. The SenderEmailAddress property contains the e-mail address of the sender.

3) The MailItem does indeed have a Delete property, so oMessage.Delete is correct.

4) I'd put a line

On Error Resume Next

immediately below ExitHandler. You won't have to worry whether objects are valid at that point.

[Pat]

Thank you for those comments, they all worked obviously.
I notice the Sendername returns either an email address or the sender name depending if there's a sender name or not.

Is there a way to get just the email address of who sent it?

[HansV]

The SenderEmailAddress property should do that.

[Pat]

Thank you, that is just what i wanted

[Pat]

Another question, that code puts those messages in the Deleted Items folder, is there a way to permanently delete them?

[HansV]

Outlook VBA apparently doesn't like to delete items permanently. As a workaround, you can use CDO.
Add the following code at the beginning of the procedure in which you want to delete the item:

Code: Select all

    Dim oCDO As Object
    Dim oMail As Object
    Set oCDO = CreateObject("MAPI.Session")
    oCDO.Logon "", "", False, False

and replace oMessage.Delete with:

Code: Select all

            Set oMail = oCDO.GetMessage(oMessage.EntryID, oMessage.Parent.StoreID)
            oMail.Delete

At the end of the procedure, add

Code: Select all

    Set oCDO = Nothing

[Pat]

The Set oCDO command fails with ActiveX component can't create object???

[HansV]

Oops, sorry. I forgot that CDO is no longer included with Outlook (it was in the past). You can download and then install it from Collaboration Data Objects, version 1.2.1.

[Pat]

I tried to install it but it says it needs Outlook 2007 to install, I have Outlook 2010 instead?

[HansV]

I have Office 2010 too, and I tested the code successfully. Does this work better? Microsoft Exchange Server MAPI Client and Collaboration Data Objects 1.2.1 May 2013 Update

[Pat]

That doesn't work either, it says I cannot install that with Outlook.
I have Office 2003 (which fails to work), Office 2007 and Office 2010

[HansV]

I'd do without then, and leave the messages in the Deleted Items folder.