Adding a CC recipient (1 Viewer)

[jbkid]

I have been able to create code to send email for my ticket system but now have a need to add an additional recipient. I would prefer this recipient to be add in the CC field. This email address will come from its own table. I have tried to figure this out myself but I can't get anything to work for me :banghead:. I am fairly new to VBA and all of my experience is just through trial and error (mostly error). I have attached my original code below which is a patchwork of things I found on line. Please help if you can.


Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.cboAssignee
stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[strEMail]", "tblUsers", stWhere)


stSubject = "Health Alert " & strSubjectLine & " " & strSerial & Chr$(13)
stTicketID = Format(Me.txtTicketID, "00000")
RecDate = Me.txtDateReceived
'-- Helpdesk employee who assigns ticket
strHelpDesk = Me.cboReceivedBy.Column(1)

stText = "HOLT CAT Health Alert by e-Solutions EMS" & Chr$(13) & Chr$(13) & _
" " & strCustomer & Chr$(13) & _
" " & strRentalCustomer & Chr$(13) & Chr$(13) & _
"Serial Number: " & strSerial & Chr$(13) & _
"" & strAction & Chr$(13) & Chr$(13) & _
" " & strEventCode & " " & strSubjectLine & Chr$(13) & Chr$(13) & _
" " & strComments & Chr$(13) & Chr$(13) & _
" " & Chr$(13) & _
" " & strNotes & Chr$(13) & Chr$(13) & _
" " & strPossibleCauses & Chr$(13) & _
" " & Chr$(13) & _
" " & strSOSIndicators & Chr$(13) & _
" " & Chr$(13) & _
" " & strPMChecklists & Chr$(13) & _
" " & Chr$(13) & _
" " & strRecommendedActions & Chr$(13) & _
" " & Chr$(13) & _
"Description: " & Chr$(13) & _
"" & strVLAlerts & Chr$(13) & Chr$(13) & _
"Location: " & Chr$(13) & _
"" & strVLLocation & Chr$(13) & Chr$(13) & _
"Alert number: " & stTicketID & Chr$(13) & _
"This Alert has been sent to you by: " & strHelpDesk & Chr$(13) & _
"Received Date: " & RecDate & Chr$(13) & Chr$(13) & _
"This is an e-Solutions EMS e-mail."
'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
'Set the update statement to disable command button
'once e-mail is sent
strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
"Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"

On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0
'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Me.chkTicketAssigned.Requery
Me.chkTicketAssigned.SetFocus
Me.cmdMailTicket.Enabled = False
Exit Sub
Err_Execute:
' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next

Exit_cmdMailTicket_Click:
Exit Sub
Err_cmdMailTicket_Click:
MsgBox Err.Description
Resume Exit_cmdMailTicket_Click
End Sub

 

[pbaldy]

If you look in VBA help at SendObject, you'll see it has an argument for a CC address. If it's coming from a different source, you can use another DLookup() or a recordset to get it.