Solved Multiple Recipients Resolve trouble (1 Viewer)

[Kayleigh]

Hi,
I am trying to set up an email command which sends the message to multiple recipients. I've tried many approaches but always throws error 'can't recognise one or more names' after the send command. If I do 'display' it will send once it has resolved the email addresses manually.
Can anyone suggest how to go about this?
My code is:

Private Sub cmdSend(recipient As String)


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim txt As String
Dim varResponse As Variant
Dim varClient As String
Dim varAddress As String

On Error GoTo Err_cmdSend

txt = Nz(Forms!frmOrderMan!frmOrderLog.Form!fldJLNote, "")

DoCmd.Hourglass True

Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim varWhere As Variant
Dim Subjectline As String
Dim strDearName As Variant
Dim signature As String
Dim mail1 As Variant
Dim mail2 As Variant
Dim strBody As String
Dim strClient As String
Dim strTSEmail As Variant
Dim strCompany As Variant
Dim strCompPhone As String
Dim strCompEmail As String
Dim strCompPhone1 As String
Dim strCompEmail1 As String
Dim strCompPhone2 As String
Dim strCompEmail2 As String
Dim rsemail As DAO.Recordset
Dim Ns As Outlook.NameSpace
Dim Folder As Outlook.MAPIFolder
Dim mySQL As String
Dim strDescription As String
Dim varX As Variant


DoCmd.SetWarnings False
Set MyOutlook = New Outlook.Application
Set MyOutlook = CreateObject("Outlook.Application")
Set Ns = MyOutlook.GetNamespace("MAPI")
Set Folder = Ns.GetDefaultFolder(olFolderInbox)
MyOutlook.Explorers.Add Folder

strTSEmail = ""
mail1 = ""

strDearName = ""

varClient = Nz(DLookup("cfClient1", "lkpqryclient1", "[fldClientID] = " & Me.Parent.fldAClientID))
varAddress = Nz(DLookup("cfAddress", "lkpqryaddress", "[fldAddressID] =" & Me.Parent.fldOAddressID))


mail1 = recipient
'Debug.Print recipient

strClient = ""
Subjectline = ""
strBody = txt

Set MyMail = MyOutlook.CreateItem(olMailItem)

 
MyMail.Recipients.Add mail1


MyMail.Subject = Subjectline
MyMail.Body = strBody
If varX = 14 Or IsNothing(varX) Then
    MyMail.Display
Else
    MyMail.Send
End If
'SendKeys "^{END}", True 'moves curser to the end
'SendKeys "{End}", True
'SendKeys "%nas{enter}", True 'to add default signature

'If GetNumlock() Then
'  MsgBox "Num Lock is ON!"
'Else
 
'  SendKeys "{NUMLOCK}"
'End If

Set MyMail = Nothing
Set MyOutlook = Nothing
DoCmd.SetWarnings True
DoCmd.Hourglass False
Dim varZ As Variant
varZ = MsgBox("Message sent successfully", vbInformation + vbOKOnly, gtstrAppTitle)

Exit_cmdSend:
    Exit Sub

Err_cmdSend:
    MsgBox Err.Description, vbExclamation, "cmdSend Error " & Err.Number
    Resume Exit_cmdSend

End Sub

And I call the code with:

cmdSend ("admin@test.co.uk;office@test.co.uk;accounts@test.co.uk;manager@test.co.uk")

The only way it worked for me was to input the recipient addresses as an array and iterate through to send an email to each individually. But I think this is unnecessary and I'm sure there is a way to send to all in one email.
Would appreciate any suggestions please.

 

[cheekybuddha]

What is your IsNothing() function? Is it declared somewhere else?

>> The only way it worked for me was to input the recipient addresses as an array and iterate through to send an email to each individually. But I think this is unnecessary and I'm sure there is a way to send to all in one email. <<
Use an array, but just iterate over it calling MyMail.Recipients.Add for each one.

Something like:

Private Sub cmdSend(recipient As String)

' ...
Dim arrRecipients As Variant, i As Integer

' ...

Set MyMail = MyOutlook.CreateItem(olMailItem)

arrRecipients = Split(recipient, ";")
For i = 0 To Ubound(arrRecipients)
  MyMail.Recipients.Add Trim(arrRecipients(i))
Next i

' ...

I'd also suggest commenting out removing the SetWarning lines. They are probably hiding whatever the problem is.

 

[The_Doc_Man]

You are using Outlook. In the past, my method was to append names to the To: option one at a time, separating them with " ; " (and then allow Outlook to remove any spaces it wanted to remove.) The loop simply allowed Outlook the chance to "vet" the addresses one at a time in the To: line (which it does when in Display mode.)

@cheekybuddha - good point on the "SetWarning" issue. When debugging, NEVER disable warnings.

 

[Kayleigh]

Thank you. Worked perfectly like that!