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.
Deleting selected emails using Access and Outlook automation
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Deleting selected emails using Access and Outlook automation
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
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?
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?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
The SenderEmailAddress property should do that.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
Thank you, that is just what i wanted
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
Another question, that code puts those messages in the Deleted Items folder, is there a way to permanently delete them?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
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:
and replace oMessage.Delete with:
At the end of the procedure, add
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
Code: Select all
Set oMail = oCDO.GetMessage(oMessage.EntryID, oMessage.Parent.StoreID)
oMail.Delete
Code: Select all
Set oCDO = Nothing
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
The Set oCDO command fails with ActiveX component can't create object???
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
I tried to install it but it says it needs Outlook 2007 to install, I have Outlook 2010 instead?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Deleting selected emails using Access and Outlook automa
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
I have Office 2003 (which fails to work), Office 2007 and Office 2010
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Deleting selected emails using Access and Outlook automa
I'd do without then, and leave the messages in the Deleted Items folder.
Best wishes,
Hans
Hans