Email VBA

[sshinshaw]

I have a database that is set up to send emails to certain people if such and such is meet. However I was wondering, is there anyway to know if they didn't send the email? If they closed out instead of hitting send.

 

[Cronk]

Can you determine whether the email was sent?

 

[sshinshaw]

Can you determine whether the email was sent?

That would be fine too. If I could determine if the email was sent, then I should be able to tell if it wasn't.

 

[MrHans]

You could add an action to the 'Send' button command. For example by adding a new record with date, time and username to another table, for example 'tblEmailSend'

 

[sshinshaw]

You could add an action to the 'Send' button command. For example by adding a new record with date, time and username to another table, for example 'tblEmailSend'

How do I add an action to the outlook send button in access? Can you give me an example in vba?

 

[MrHans]

Yes, you could add something like this to the Outlook button on click even:

            Const cDQ As String = """"
            Dim strSQL As String
            Dim strActionType As String

            strActionType = "E-mail button pressed"
            strSQL = "INSERT INTO " _
                & "tblEmailSend (Date, User, RecordID, SourceTable, ActionType) " _
                & "VALUES (Now()," _
                & cDQ & fUserName & cDQ & ", " _
                & cDQ & Me.fldRecordID.Value & cDQ & ", " _
                & cDQ & Me.RecordSource & cDQ & ", " _
                & cDQ & strChangeType & cDQ & ")"
        
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True

This should add a new record to the tblEmailSend, specifying the date, the username, the RecordID and the original table.
In my case I use another function FUserName to get the network username, but you can modify this to match your system.

 

[sshinshaw]

Ok I don't think I'm following. My code current looks like this

If Mid(myAction, Len(myAction) - 4, 1) = 1 Then
eTo = "email"
eCC = "email"
eSubject = "Counterfeit Item"
Ebody = "Please blah blah. " & _
"According to blah blah." & Chr(13) & Chr(13) & _
"Date: " & Date & Chr(13) & _
"Accounting Department" & Chr(13) & _
"Employee: " & DLookup("[Employee Name]", "tbl Employees", "[Employee ID] = '" & g_EID & "'") & Chr(13) & Chr(13) & _
"Account #: " & Me.lstChecks.Column(lAccount, 1) & Chr(13) & _
"Check Date: " & Me.lstChecks.Column(lDate, 1) & Chr(13) & _
"Check #: " & Me.lstChecks.Column(lCheck, 1) & Chr(13) & _
"Amount: " & Format(Me.lstChecks.Column(lAmount, 1), "$#,##0.00") & Chr(13) & Chr(13) & _
"Thank you!"
On Error Resume Next
DoCmd.SendObject acSendNoObject, , , eTo, eCC, , eSubject, Ebody
End If