SendObject Message

[williammarino]

Hey I am trying to finish out my macro (SendObject function) but I guess I used up the max amount of characters per field so I thought finishing it out in VBA code would do the trick but it just keeps erroring when I edit it. Here is the macro formula I am using its just concatanation with some static text nothing fancy. Can someone tell me how to add: Thanks, [Opened By]



="Hello" & Chr(13) & Chr(10) & "I am requesting a QC that contains" & [File Count] & "," & [Record Count] & "records and needs to be completed by" & " " & [Due Date] & ". All Appropiate information be found" & [Checklist Location] & Chr(13) & Chr(10)

Below is the macro converted to VBA code I just want to add more text to the message. I have also attached a screen shot


'------------------------------------------------------------
' Issues_Form_Macros_cmdEmailAssignedTo___On_Click
'
'------------------------------------------------------------
Function Issues_Form_Macros_cmdEmailAssignedTo___On_Click()
On Error GoTo Issues_Form_Macros_cmdEmailAssignedTo___On_Click_E rr

With CodeContextObject
Docmd.SendObject , "", "", Nz ( DLookUp ( "[E-Mail Address]" , "Contacts" , "ID=" & Nz ( .[Assigned To]

, 0 ) ) ), Nz ( DLookUp ( "[E-Mail Address]" , "Contacts" , "ID=" & Nz ( .[Opened By]

, 0 ) ) ), "", "QC Clippership Request " & .ID & ": " & .Title, "Hello" & Chr ( 13 ) & Chr ( 10 ) & "I am requesting a QC that contains" & .[File Count]

& "," & .[Record Count]

& "records and needs to be completed by" & " " & .[Due Date]

& ". All Appropiate information be found" & .[Checklist Location]

& Chr ( 13 ) & Chr ( 10 ), True, ""
End With


Issues_Form_Macros_cmdEmailAssignedTo___On_Click_E xit:
Exit Function

Issues_Form_Macros_cmdEmailAssignedTo___On_Click_E rr:
MsgBox Error$
Resume Issues_Form_Macros_cmdEmailAssignedTo___On_Click_E xit

End Function

[dcrake]

For a start I would single out this action and place in a new macro. Then convert the macro to vb code.

In the converted macro module

If would validate both the sender and receiver email addresses first. before I attempt to use the send object.

 
Dim Sender as String
Dim Recepient As String
Dim UMsge As String
 
 
Sender =  Nz(DLookUp("[E-Mail Address]" , "Contacts" , "ID=" & [Assigned To] ),"0")
 
If Sender = "0" Then
  'No sender email address. No point in doing anything else so exit sub
   Exit Sub
Endif
 
Recepient = Nz(DLookUp("[E-Mail Address]" , "Contacts" , "ID=" & [Opened By]),"0")
 
If Receepient = "0" Then
   'No Receipent email address. No point in doing anything else so exit sub
   Exit Sub
Endif
 
UMsge = "QC Clippership Request " & .ID & ": " & .Title, "Hello" & VbCrLf  & "I am requesting a QC that contains" & .[File Count]
& "," & .[Record Count]
& "records and needs to be completed by" & " " & .[Due Date]
& ". All Appropiate information be found" & .[Checklist Location] & VbCrLf
 
'So now we know we have a sender and recipient and a message we can now use the send object
 
DoCmd.SendObject,"",Sender,Recipient,"",UMsge,True,""

Then use the RunCommand and call the function/Sub


The reason being that if either no sender or receiver is found then the macro will error

This is all aircode and untested

David

[williammarino]

Thanks David I will be testing this now and let you know the results.

[williammarino]

I keep getting a compile error. I have attached a screenshot.

I would attached the database but it is too big.

[dcrake]

If you are calling this sub from a form you need to pass the arguments to the subroutine.

First in your sub declare the arguments

Public Sub Bill(Sender As String,Recepient As String,Etc as Type)

Such as:

Call Bill(Me.Sender,Me.Recipient,Me.etc)

Then in you Dlookups, etc replace the actual field names previously used with the arguments passed to the sub from the form.

I don't know how you are calling the sub from the form but you can peform the validation on the sender and receiver email addresses prior to calling the sub that way if there is no sender or receiver you can warn the user via a MsgBox.

 
If IsNull(Me.Sender) Then
   MsgBox "Cannot perform email action as there is no sender email address present"
End If

David