determine if email has been sent?

[dabruins]

I have an Access form in which, based upon some info provided by the user, I compose an email that contains selected attachments, displaying the email to the user as an outlook message. At this point the user may send the email or not. What I want to know is if they have (or have not) sent the email so that I can update some other records in the database. I'm having trouble figuring out a way for Outlook to communicate the status of the email message.

Any ideas on how this might be done. A different approach maybe? I'm not too familiar with the outlook object model.

 

[Remou]

This may help.

Sub TestSent()
Dim OLApplication As Outlook.Application
Dim olNamespace As NameSpace
Dim olAllItems As Items
Dim OlSentItems As Outlook.MAPIFolder
Dim olAllSentItems As Items
Dim olLastMail As MailItem
Dim Filter1 As String, filter2 As String

Set OLApplication = CreateObject("Outlook.Application")
Set olNamespace = OLApplication.GetNamespace("MAPI")

Set OlSentItems = olNamespace.GetDefaultFolder(olFolderSentMail)
Set olAllSentItems = OlSentItems.Items
olAllSentItems.Sort "SentOn", True
Set olLastMail = olAllSentItems.GetFirst

Debug.Print olLastMail.SentOn
Debug.Print olLastMail.Subject
Debug.Print olLastMail.Recipients(1)

End Sub

 

[dabruins]

Hi Remou.

Thanks for sharing this bit of code. I can now see how to get a handle on the sent items but how can I be sure that the first item in the list is the message that I composed and the user may or may not have sent? If they didn't send it the first item in the sent folder will not be the one i'm interested in. Is there an ID can check for?

Thanks.

 

[Remou]

What kind of ID did you have in mind?

 

[traingamer]

You can check the subject (which you should know, since you just sent it). Loop through the outbox

For i = 1 To OlSentItems.Items.Count
If (OlSentItems.Items(i).Class = olMail) And (OlSentItems.Items.Item(i) = "Your Title Here") Then 
...
end if

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Loop through the sent items folder is what I meant to say.

 

[dabruins]

The subject line could be changed by the user prior to sending it. I could "require" that they not do so but I would like to avoid it if possible. This is why I was hoping that outlook might assign an ID when a new message is created that I could check for in the sent items folder. Basically, anything that outlook automatically assigns that the user cannot manually change and I can later look for. For example the EntryID. Do either of you know when this is assigned on a message?

In the meantime I've designed the following:

'compose message in With...End With block
'and Display it to the user. Code seems to pause at Display.
'After execution continue check for .sent status.
'If it errors out (objMess does not exist) set sent status
'to false and move on.

Dim objOutlk As Object
Dim objMess As Object
Set objOutlk = CreateObject("Outlook.Application")
Set objMess = objOutlk.CreateItem(0) '0 = olMailItem

blnSent = True

With objMess
.BodyFormat = 2 'olFormatHTML
.CC = Me.sendto
.Subject = "Email Distribution for " & Format(Me.jobno, "00-0000") & Me.dept
.Body = Me.emailbody
For i = 0 To Me.lstAttachments.ListCount - 1
.attachments.Add Me.lstAttachments.Column(1, i)
Next i

.Display (True)

On Error GoTo EMAILSENT
blnSent = .sent

End With

EMAILSENT:
On Error GoTo Err_Handler
blnSent = False

If blnSent Then...

 

[Remou]

It is possible to add and retrieve user properties with 2003, I do not know about earlier versions.

When sending:
[tt]MyProp = objMess.UserProperties.Add("MyID", olText, True)
objMess.UserProperties("MyID") = "Text for ID"[/tt]

When checking sent items:
[tt]Debug.Print olLastMail.UserProperties("MyID")[/tt]

I do not think that Outlook assigns an ID at the time that the first part of your code is run, so I do not think that such an ID can help.

 

[dabruins]

Hi Remou.

Thanks for the reply. This should work if I handle the error that will occur when looking through the other emails that will not have this property set upon them.

Actually, though I am dealing with other problems related to this issue, I have gotten it to work ok using the method I described above. I'm now attempting to automatically save a copy of the message on a server once is has been sent. I was using the method you descibed earlier in looking in the sent items folder but I have come across instances in which the message is not immediately sent which could be related to a number of netowrk issues and hence it does not find a matching message in the folder to copy. I'm now rethinking how I go at this. Maybe I have to set the property in the message as you suggest and let the user send the email. Later at the users request I could run a routine to make copies of those messages in the sent folder containing the property to the server???

 

[danvlas]

Your code should work fine, with the following change:

'...
.Display (True)

On Error Resume Next
blnSent = .Sent
If Err.Number Then
'message has been sent, as it returns the error
'-1560018678, The item has been moved or deleted.
'It has been moved from Drafts to Sent.
'Do your stuff here
Else
'there is no error, but it's always False...
End If

In the original version, blnSent was always set to False before checking its value...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PHV]

Is there an ID can check for?
You may try objMess.EntryID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[danvlas]

Checking EntryID after sending a new message will return the same error as checking the .Sent property (item has been moved). Checking it before sending will reurn a null string. because the item has not been saved yet.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html