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("tblEMAI
L_TMP")
Set appOutlook = New Outlook.Application
strEmail = Nz(rst![DC1EMAIL])
Set msg = appOutlook.CreateItem(olMa
ilItem)
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.Appl
ication")
Resume Next
Else
MsgBox "Error No: " & err.Number _
& " in EMailAllContacts procedure" _
& "; Description: " & err.Description
Resume ErrorHandlerExit
End If
Screen.PreviousControl.Set
Focus
DoCmd.RunCommand acCmdAutoDial
Exit_cmdEMAIL_NO_Click:
Exit Sub
Err_cmdEMAIL_NO_Click:
Resume Next
Resume Exit_cmdEMAIL_NO_Click
End Sub
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).