email generation on record change

See if this gets you started

Private Sub Investigator_AfterUpdate()

On Error GoTo Err_Investigator_AfterUpdate

    Dim strMsg As String
       
        'only send a message if the value has changed
    If Me.Investigator <> Me.Investigator.OldValue Then
        strMsg = "You've been assigned a ticket"
        DoCmd.SendObject acSendNoObject, , , "omgang@ee.com", , , "Ticket Assignment", strMsg, False
    End If

Exit_Investigator_AfterUpdate:
    Exit Sub

Err_Investigator_AfterUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Investigator_AfterUpdate of VBA Document Form_frmCategory"
    Resume Exit_Investigator_AfterUpdate

End Sub

Things you'll need to do:

get email address of the investigator
Dim varAddy As Variant

varAddy = DLookup("emailfield", "tblInvestigators", "investigatorID = " & Chr(34) & Me.Investigator & Chr(34))

Add other details to your email message:
Dim strSubj As String, strMsg As String

strSubj = "You've been assigned to call # " & Me.CallIDField
strMsg = Me.CallIDField & " has been assigned to you.  Please take the following action....."

OM Gang

sorry.....

then you can
DoCmd.SendObject acSendNoObject, , , strAddy, , , strSubj, strMsg, False    '<---change last to True if you want the message to open in the email client before sending

OM Gang

Note that if you're using Outlook as your email client you'll receive an Outlook Security Warning message each time a message is sent.  The user must click Allow for the message to proceed.

OM Gang

So... Like this?

Private Sub Investigator_AfterUpdate()

On Error GoTo Err_Investigator_AfterUpdate

    Dim strMsg As String
        
'get email address of the investigator
Dim varAddy As Variant

varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "investigatorID = " & Chr(34) & Me.Investigator & Chr(34))
        
        'only send a message if the value has changed
    If Me.Investigator <> Me.Investigator.OldValue Then
        strMsg = "You've been assigned a ticket"
        DoCmd.SendObject acSendNoObject, , , varAddy, , , "Ticket Assignment", strMsg, True
    End If

Exit_Investigator_AfterUpdate:
    Exit Sub

Err_Investigator_AfterUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Investigator_AfterUpdate of VBA Document Form_frmMainActionLog"
    Resume Exit_Investigator_AfterUpdate
    
'Add other details to your email message:
Dim strSubj As String, strbodyMsg As String

strSubj = "You've been assigned to call # " & Me.Call
strbodyMsg = Me.Call & " has been assigned to you.  Please take the following action....."

End Sub

Select allOpen in new window

Few changes.  We don't want to lookup the email address unless we're sure we want to send the message.  Give this a shot and let me know if you have any questions.  Note that I added a piece for testing (see in the code) --- comment out the DLookup and uncomment the explicit assignment of your own email address.  Now you can test with all messages coming to you.
OM Gang



Private Sub Investigator_AfterUpdate()

On Error GoTo Err_Investigator_AfterUpdate

    Dim strbodyMsg As String, strSubj As String
    Dim varAddy As Variant
               
        'only send a message if the value has changed
    If Me.Investigator <> Me.Investigator.OldValue Then
            'get email address of the investigator
        varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "investigatorID = " & Chr(34) & Me.Investigator & Chr(34))
            'for testing comment the line above and uncomment the line below
        'varAddy = "rossdagley1@ee.com"    '<-------change this to your actual email address

            'Add other details to your email message:
        strSubj = "You've been assigned to call # " & Me.Call
        strbodyMsg = Me.Call & " has been assigned to you.  Please take the following action....."

        DoCmd.SendObject acSendNoObject, , , varAddy, , , strSubj, strMsg, True
    End If

Exit_Investigator_AfterUpdate:
    Exit Sub

Err_Investigator_AfterUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Investigator_AfterUpdate of VBA Document Form_frmMainActionLog"
    Resume Exit_Investigator_AfterUpdate
   
End Sub

Ross, I guess I should add that the procedure is based upon the following assumptions:
The investigator field is named Investigator
The call record ID field on the form is named Call

Also, you need to most likely revise the DLookup so that it is looking in the correct field in tblInvestigators.  If the field in the table is also named Investigator then
varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "Investigator = " & Chr(34) & Me.Investigator & Chr(34))

OM Gang

OM Gang

It's almost perfect, thank you so much. You're a real star.

When testing with my email address it works, when I comment it out and use the dlookup it just brings up the email with no email address in the "To" field. I suspect this is because of the dlookup failing? Perhaps just a field/table name issue?


  varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "InvestigatorName = " & Chr(34) & Me.Investigator & Chr(34))


The table is called "tblInvestigators". In that table, is "InvestigatorName" (the same as Investigator in the form), and "InvestigatorEmail" which holds the email address. I've tried editing it as above, but cant seem to get it right.

Also, is there ANY way to get this to work without the prompt / warning about something sending on my behalf with that delay etc? That'll be a royal pain. I have something that sends appointment requests in another database that doesn't prompt (even though it uses Outlook to email over the appointment request), so is it just because it's a plain old email thats the issue?

Thanks so much - you really are a life saver.

Ross

Debug.Print Me.Investigator  '<----- add this
varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "InvestigatorName = " & Chr(34) & Me.Investigator & Chr(34))

Is the field on your form named Investigator or InvestigatorName?



Re. the Outlook Security popup message.  Yes, there are ways around it....all much more complicated.  Softare programs like QuickBooks and others send messages via Outlook without generating the prompt so we know it's doable.
Options:
1) don't send via Outlook but send using CDO instead.  Drawbacks: no record of message in your Outlook Sent Items folder
2) automate Outlook and send the message using the Outlook Redemption library.  Drawbacks: Redemption library must be installed on each machine where your app will be used; much more coding.
3) use a third-party utility like Click-Yes or MAPILabs Outlook Security to eliminate the popup message.  Drawbacks: third-party software must be installed on each machine where your app will be used.

OM Gang

the debug window shows the record number of the investigator in the tblInvestigators table (the table has an autonumber field as the first field). I am "1" and when I change the investigator on my form to me, I get "1" in the debug window.

The field on the form is named Investigator. The field in the table it's looking up the email addresses in (tblInvestigators) is called InvestigatorName.

How do we go about using CDO instead? Do I need to then specify an SMTP server and that kind of thing? I don't know anything about it. I'm not interested in saving the message in Outlooks sent box.

So, we're looking up the email address based upon the Investigator ID value that is a number (not text).  We need to remove the Chr(34) from the DLookup because that was wrapping the Investigator value in quotes so it would be passed as a text value.  Try this instead.

Debug.Print Me.Investigator  '<----- add this
varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "Investigator = " & Me.Investigator)


Yes to needing to specify SMTP server address, etc.  I'll dig up some sample CDO mail code and post it for you.
OM Gang

Ross, did you notice that I also changed the fieldname in the criteria statement from InvestigatorName to Investigator?

Revised procedure that doesn't use Outlook.  Let me know if you have any questions.
OM Gang

Private Sub Investigator_AfterUpdate()

On Error GoTo Err_Investigator_AfterUpdate

    Dim strbodyMsg As String, strSubj As String, strFromAddy As String
    Dim varAddy As Variant
               
        'only send a message if the value has changed
    If Me.Investigator <> Me.Investigator.OldValue Then

            'this is the sender address we want to use on for the message
        strFromAddy = "rossdagley1@ee.com"

            'get email address of the investigator
        varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "Investigator = " & Me.Investigator)
            'for testing comment the line above and uncomment the line below
        'varAddy = "rossdagley1@ee.com"    '<-------change this to your actual email address

            'Add other details to your email message:
        strSubj = "You've been assigned to call # " & Me.Call
        strbodyMsg = Me.Call & " has been assigned to you.  Please take the following action....."

        'DoCmd.SendObject acSendNoObject, , , varAddy, , , strSubj, strMsg, True

            'call public function to send CDO email message
        Call SendCDOEmail(strFromAddy, varAddy, strSubj, strbodyMsg, False, False)    '<---- last two parameters are ImportanceHigh and PriorityHigh
    End If

Exit_Investigator_AfterUpdate:
    Exit Sub

Err_Investigator_AfterUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Investigator_AfterUpdate of VBA Document Form_frmMainActionLog"
    Resume Exit_Investigator_AfterUpdate
   
End Sub


Function SendCDOEmail(strFrom As String, strTo As String, strSubj As String, strMsg As String, _
        blImportanceHigh As Boolean, blPriorityHigh As Boolean)
'send e-mail message to specified address(es)
On Error GoTo Err_SendCDOEmail
   
    Dim objMessage As Object, objCon As Object
    Dim strSMTPGateway As String
   
    strSMTPGateway = "smtp.MyDomain.com"
   
    Set objMessage = CreateObject("CDO.Message")
    Set objCon = CreateObject("CDO.Configuration")

        objCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPGateway
        objCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        objCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        objCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        objCon.Fields.Update
   
    Set objMessage.Configuration = objCon
        objMessage.Subject = strSubj
        objMessage.From = strFrom
        objMessage.To = strTo
        objMessage.TextBody = strMsg
            'check boolean variable to see if we should set importance High for this message
        If blImportanceHigh = True Then
            objMessage.Fields.Item("urn:schemas:mailheader:importance").Value = "high"
        End If
            'check boolean variable to see if we should set priority 1 for this message
        If blPriorityHigh = True Then
            objMessage.Fields.Item("urn:schemas:mailheader:priority").Value = 1
        End If
   
        objMessage.Fields.Update
        objMessage.Send
       
Exit_SendCDOEmail:
        'destroy object variables
    Set objMessage = Nothing
    Set objCon = Nothing
    Exit Function
   
Err_SendCDOEmail:
    MsgBox Err.Number & ", " & Err.Description, , "Error in function SendCDOEmail"
    Resume Exit_SendCDOEmail

End Function

With this:

 'get email address of the investigator
        Debug.Print Me.Investigator
        varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "Investigator = " & Me.Investigator)
        Debug.Print varAddy

I get:

1
Null

in the Immediate window, and a failure to populate the To field.

You'll need to change the SMTP server address in the SendCDOEmail function.
OM Gang

Ross, in tblInvestigators, what are the field names and their types?
OM Gang

ID                                                  AutoNumber
InvestigatorName                         Text
InvestigatorInitials                         Text
InvestigatorNetworkLogin             Text
InvestigatorTeam                           Text
InvestigatorEmail                            Text

I'm guessing we still need to sort this dlookup issue before moving on to try and change to CDO.

For the time being, I can have the FALSE value set, so it requires me to click Send button, then the warning message isn't generated.

varAddy = DLookup("InvestigatorEmail", "tblInvestigators", "ID = " & Me.Investigator)

The DLookup function 'says' this

Lookup ( "the value in the field named InvestigatorEmail", "from the table named tblInvestigators", "where the field named ID = " the value from the Investigator field on my form)
OM Gang

Ross.

Is me.investigator a combo box, like in your earlier post?
The value of a combo box is it's bound column -- not what it displays!

Such things matter a great deal!

omgang - I now have it working perfectly with CDO and looking up the email address correctly, thank you very much :)

My final hurdle is that mostly the me.Investigator.OldValue is null - in this situation no email is generated. I would like the email to go, but with a different subject perhaps "You have a new assignment". Is this possible?

I want to email the new investigator on both a change, and a new assignment.

I think we're almost there!

OM Gang.

I understand perfectly now thank you very much for your hand holding and mentoring. Sincerely appreciated as I've learnt how to work with this, rather than just copy/paste.

Really - excellent job, and thank you so much.

excellent as always from the Expert OM Gang!