Simple question about sending emails ...

[Herbert Becker]

Hello all!

This question might be too trivial, but yet I need to
answer it, so please be patient.
Wnat I need to do is programmatically create a message
item in VBA (Access 2002, Windows XP), pre-fill the
fields "subject", "body" and "to" and show the result to
the user. The user then can complete/change the text and
send the message afterwards.

So far so good. I found code which can accomplish that.
But what I also need to do is to make an entry in a
database whether the created message was sent or not! Now
the user can either send the message or discard it, and
even if he chooses to send it an error on sending might
occur.

My idea was to display the created message with
olItem.Display True
to halt execution of the VBA code till the user closes the
message window and to pick up some sort of a return code.

Is there any way to retrieve success or failure for
sending that message? Or do I really have to search
the "Sent Items" folder for the maybe newly created
message?

Any comments/hints/links/code snippets would be very much
appreciated!

Herbert Becker

 

[Jon Philip]

My answer may be too trivial, but I would simply create a table in the
database that logs when the message was sent. And attach an ID number
to each "transaction" when they send the email.

 

[Herbert Becker]

Hello Jon and thanks for the reply.

The process of sending the MailItem itself is my problem.
Once I displayed the MailItem, the user has control over
it. I cannot verify whether the user has sent the message
or has just closed the window discarding it.

 

[Dick Kusleika]

Herbert

Before you destroy your Outlook object variable, loop through the items in
the Outbox and SentItems folders. I think you need both folders because the
user can have Outlook to send immediately or not. This also presents a
problem if the user is set not to send immediately, then deletes the mail
from his Outbox before it gets sent.

What to look for, though? If the user can change the subject, to, and body
before sending, those are out. You could look at the sent time and it would
have to be pretty close to Now, but that's obviously not foolproof.

 

[Jon Philip]

Hello Jon and thanks for the reply.

The process of sending the MailItem itself is my problem.
Once I displayed the MailItem, the user has control over
it. I cannot verify whether the user has sent the message
or has just closed the window discarding it.

What I have done in a previous app is get all of the information I
need on a form and then send the email using VBA. I store an email
address in a table. Here is a sub from 1 of my databases. This runs
on Access97, but it should be similiar to AccessXP. Hopefully you can
use the concept of what I did and make it work for your app. - Jon

Private Sub cmdSendNow_Click()

On Error GoTo Err_Handler:

Dim dbs As Database
Dim rst As Recordset
Dim strEmailList As String
Dim strRequestID As String
Dim strOutPutFile As String
Dim strSubject As String
Dim strDate As String
Dim intRequestID As Integer

' Get Request ID
strRequestID = InputBox("Enter Request ID", "Request ID?")
If strRequestID = "" Then
MsgBox "Notification Cancelled", vbOKOnly, "Cancelled"
Exit Sub
Else
intRequestID = CInt(strRequestID)
End If

' Get info and email address based on Request ID
Call Create_Granted_Query(intRequestID)

' Get email address from query
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Grant_Access_Query")
If rst.EOF = True Then
MsgBox "No Records Found For " & strRequestID
Exit Sub
Else
strEmailList = rst.Fields(11)
End If

' Close recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing

strDate = Format(Now, "MMDDYYHHMMSS")
strOutPutFile = "\\admin\WILCommon\Computer_Access\Grants\" &
strRequestID & "_" & strDate & ".snp"
DoCmd.OutputTo acOutputReport, "Grant_Access_Report", "snapshot
format", strOutPutFile
DoEvents
strSubject = "Computer Access Enabled/Disabled:" & vbCrLf & "Note:
Click The Link, Print The Report, And Give Report To Employee" &
vbCrLf & _
"PLEASE READ SOP A-008(as revised)" & vbCrLf & vbCrLf
DoCmd.SendObject acSendNoObject, , , strEmailList, , , "Enable/Disable
Computer Access Report", strSubject & strOutPutFile, False
DoEvents

MsgBox "Report Sent!", vbOKOnly, "Sent!"

Exit Sub

' *******************************
Err_Handler:

Call Display_Err

End Sub

 

[Herbert Becker]

Hello Jon,

thanks a lot for pointing me into the right direction!
I tested DoCmd.SendObject a bit and what it does should be
sufficient for what I need!
I knew this function does exist but I didn't know that you
can send a common mail WITHOUT an Access object as
attachment. (I must have ignored the
constant "acSendNoObject"!)

Thanks again!

Herbert

What I have done in a previous app is get all of the information I
need on a form and then send the email using VBA. I store an email
address in a table. Here is a sub from 1 of my databases. This runs
on Access97, but it should be similiar to AccessXP. Hopefully you can
use the concept of what I did and make it work for your app. - Jon

Private Sub cmdSendNow_Click()

On Error GoTo Err_Handler:

Dim dbs As Database
Dim rst As Recordset
Dim strEmailList As String
Dim strRequestID As String
Dim strOutPutFile As String
Dim strSubject As String
Dim strDate As String
Dim intRequestID As Integer

' Get Request ID
strRequestID = InputBox("Enter Request ID", "Request ID?")
If strRequestID = "" Then
MsgBox "Notification Cancelled", vbOKOnly, "Cancelled"
Exit Sub
Else
intRequestID = CInt(strRequestID)
End If

' Get info and email address based on Request ID
Call Create_Granted_Query(intRequestID)

' Get email address from query
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Grant_Access_Query")
If rst.EOF = True Then
MsgBox "No Records Found For " & strRequestID
Exit Sub
Else
strEmailList = rst.Fields(11)
End If

' Close recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing

strDate = Format(Now, "MMDDYYHHMMSS")
strOutPutFile

= "\\admin\WILCommon\Computer_Access\Grants\" &

strRequestID & "_" & strDate & ".snp"
DoCmd.OutputTo

acOutputReport, "Grant_Access_Report", "snapshot

format", strOutPutFile
DoEvents
strSubject = "Computer Access Enabled/Disabled:" & vbCrLf & "Note:
Click The Link, Print The Report, And Give Report To Employee" &
vbCrLf & _
"PLEASE READ SOP A-008(as revised)" & vbCrLf & vbCrLf
DoCmd.SendObject acSendNoObject, , ,

strEmailList, , , "Enable/Disable