Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Track when Outlook e-mail sent

Posted on 2014-04-29
3
Medium Priority
?
1,258 Views
Last Modified: 2014-05-01
I have an MS Access database that the user can create an Outlook message to be sent, but I also need to know if the user actually sends the e-mail. Can someone direct or assist me in how to record in an Access table the date the e-mail was sent?

Below is my code for creating the e-mail:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim appOutlook As Outlook.Application
Dim msg As Outlook.MailItem
Dim strEmail As String
Dim msgbody As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblEMAIL_TMP")
Set appOutlook = New Outlook.Application
        strEmail = Nz(rst![DC1EMAIL])
Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Survey "
         msg.BodyFormat = olFormatHTML
         msg.Display
         msgbody = "Dear" & " " & rst![DC1TL] & " " & [DC1LN] & "," & vbCrLf
         msgbody = msgbody & " " & vbCrLf
         msgbody = msgbody & "We would greatly appreciate you taking a few minutes to complete a short survey regarding the Training Visit that recently took place with your dealership. " & vbCrLf
         msgbody = msgbody & "Your candor and feedback will help us improve our processes." & vbCrLf
         msgbody = msgbody & " " & vbCrLf
         msgbody = msgbody & "Please note that your responses are anonymous, unless you choose otherwise.  Thank you in advance for your time. " & vbCrLf
         msgbody = msgbody & " " & vbCrLf
         msgbody = msgbody & "Please click here to begin the survey:  https://www.surveymonkey.com/s/TVFY14 "
         msg.Body = msgbody
         msg.Display
         'msg.Send  
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & err.Description
      Resume ErrorHandlerExit
   End If

    Screen.PreviousControl.SetFocus
    DoCmd.RunCommand acCmdAutoDial

Exit_cmdEMAIL_NO_Click:
    Exit Sub

Err_cmdEMAIL_NO_Click:
    Resume Next
    Resume Exit_cmdEMAIL_NO_Click
End Sub
0
Comment
Question by:castlerj
3 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 750 total points
ID: 40030754
...Can I presume that a delivery receipt is not what you are looking for here?
ex.: msg.OriginatorDeliveryReportRequested = True

You may need to clearly define "Send"
When the user clicks "Send"?
When the email is actually "sent" (some systems store the email in the outbox to be sent later)?
I don't know of on easy way to track when an email is actually sent.

To be sure, the info on all emails "sent" is kept in your "Sent Items" folder in outlook.
You can open this folder and sort by the date sent, to see the most recently sent email(s).

You can also use Access to link to your sent items folder, ...then create a query to look at today's sent emails:
(Something like this ugly, but working query.). ;-):
SELECT CDate(Format([sent],"mm/dd/yyyy")) AS JustDate, [Sent Items].Sent, [Sent Items].Contents, [Sent Items].Subject, [Sent Items].To, [Sent Items].[Sender Name]
FROM [Sent Items]
WHERE (((CDate(Format([sent],"mm/dd/yyyy")))=Date()));
...Or simply use this linked table directly...

But it is not a simple task to "trigger" anything to happen (add a record to a table in Access) if a record is added to the sent items folder.

Your code creates an email, ...but emails do not really have a Primary Key attached to them, so it is difficult to track a specific email, (create a table entry when they are "sent"), after they are created.

Hope this helps...

JeffCoachman
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 750 total points
ID: 40030916
You can use the MailItem.EntryID (a hexdigits string) of the mail as a PK. It is generated by Outlook to uniquely identify stored or sent messages (either is sufficient).
As then send process is asynchronous, even if you just want to know at which time Outlook queued the message to its outbox, you either need to poll the generated MailItem object for the MailItem.Send boolean, or implement an event trigger for Send (which might not be possible with dynamic code - didn't try).
0
 

Author Closing Comment

by:castlerj
ID: 40035674
Good advice on both; I tried the following and it seems to work:
Added - Dim rst1 As DAO.Recordset
Set rst1 = Me.RecordsetClone
    rst1.Edit
    rst1!DSSENT = Now
    rst1.Update

ErrorHandlerExit:
   rst.Close
   rst1.Close
   Set rst = Nothing
   Set rst1 = Nothing
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question