SENDMESSAGE

Topic

New Reply

I am using the following code successfully behind a command button:

Private Sub cmdSend_Click()
On Error GoTo Err_cmdSend_Click

SendMessage (“FranklJ@sutterhealth.org”)

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click

End Sub

(See SENDMESSAGE function below)

I want to add a second recipient and I guess I am not using the ; in the right place??

SendMessage (“FranklJ@mycompany.org;LortonC@mycompany.org”)

I want both to appear on the “To” line.

What am I doing wrong?

Thank you as always !

Michael

***FUNCTION**
Private Sub SendMessage(Recip As String)

On Error GoTo Err_SendMessage_Click

Dim NameSpace As Object
Dim EmailSend As Object
Dim EmailApp As Object

Dim MYSTRING As String

Dim mySubject As String
Dim myBody As String
Dim mYRecipient As String

MYSTRING = “YOU MUST FILL IN ALL REQUIRED FIELDS. REQUESTER/SOURCE/HMO/IPA/GROUP# & PLAN”

If IsNull(cboRequester) Or IsNull(Combo779) Or IsNull([txtgroup#]) Or IsNull([cboSource]) Or IsNull(txtPlan) Then
MsgBox MYSTRING, vbExclamation, “DATA ENTRY ERROR”
Exit Sub
End If

mySubject = “ITCFM ” & “URGENT!!!!! ” & [HMO] & “/” & [IPA] & “/” & [Group Name] & “/” & [Group#]
myBody = “HMO: ” & [HMO] & Chr(10) & “IPA: ” & [IPA] & Chr(10) & “Group Name: ” & [Group Name] & Chr(10) & “Group#: ” & [Group#] & _
Chr(10) & “Plan Name: ” & [Plan] & Chr(10) & “Eff Date: ” & [EffDate] & Chr(10) & “IDX Plan#: ” & [IDX Plan#] & Chr(10) & “Contract Code/PPID: ” & _
[Contract Code (CC)] & Chr(10) & “Branch Code(Cigna):” & [txtBranchCode] & Chr(10) & “Benefit Option Code(Cigna):” & [txtBenefitOptionCode] & Chr(10) & _
“Unit #(Maxicare):” & [txtUnitNum] & Chr(10) & “Plan Description:” & [Plan Description] & Chr(10) & “OV CoPay: ” & [txtOVCoPay] & _
Chr(10) & Chr(10) & “COMMENTS:” & Chr(10) & _
[Notes_Comments] & Chr(10) & Chr(10) & Chr(10) & [Requester] ‘Add a comma and a zero here to send automatically
mYRecipient = Recip

Set EmailApp = CreateObject(“Outlook.Application”) ‘outlook object
Set NameSpace = EmailApp.GetNamespace(“MAPI”)
Set EmailSend = EmailApp.CreateItem(0) ‘CreateItem(0)Mail Item

EmailSend.Subject = mySubject ‘subject
EmailSend.Body = myBody ‘body
EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient
‘EmailSend.Importance = olImportanceHigh
EmailSend.Display ‘ Remove if you don’t want to view email before being sent.

[txtSentToIT] = Format(Date, “MM/DD/YYYY”)

Exit_SendMessage_Click:
Exit Sub

Err_SendMessage_Click:
MsgBox Err.Description
Resume Exit_SendMessage_Click

End Sub

Reply | Quote

Replies

The SendMail function in the attachment to [post=”728849″]Post 728849[/post] shows how to handle multiple recipients in a string, separated by semi-colons.

Reply | Quote

The SendMail function in the attachment to [post=”728849″]Post 728849[/post] shows how to handle multiple recipients in a string, separated by semi-colons.

Using the example:
SendMail “john@this.com;mary@that.org”

I have tried
SendMessage (“FranklJ@mycompany.org;LortonC@mycompany.org”)

and Outlook states “Microsoft Office Outlook does not recognize FranklJ@mycompany.org;LortonC@mycompany.org

Reply | Quote

You have to use the Split function to create an array of recipient names, and add them one by one. The code of the SendMail function shows how to do that:

Code:

Function SendMail( _
	Recipient As String, _
	...

  ...

  With objMI
	arr = Split(Recipient, ";")
	For Counter = 0 To UBound(arr)
	  Set Recip = objMI.Recipients.Add(arr(Counter))
	Next Counter
  ...

Here, objMI is the equivalent of EmailSend in your code.

Reply | Quote

You have to use the Split function to create an array of recipient names, and add them one by one. The code of the SendMail function shows how to do that:

Code:

Function SendMail( _
	Recipient As String, _
	...

  ...

  With objMI
	arr = Split(Recipient, ";")
	For Counter = 0 To UBound(arr)
	  Set Recip = objMI.Recipients.Add(arr(Counter))
	Next Counter
  ...

Here, objMI is the equivalent of EmailSend in your code.

I will add this to the SendMessage code and see what happens.

Thank you for your help Hans.

Michael

Reply | Quote

You’ll have to modify it to fit in with the your own code, of course.

Reply | Quote

You’ll have to modify it to fit in with the your own code, of course.

Of course

It is getting stuck asking for “Object required” on highlighted”

Private Sub SendMessage(Recip As String)

On Error GoTo Err_SendMessage_Click

Dim NameSpace As Object
Dim EmailSend As Object
Dim EmailApp As Object

Dim MYSTRING As String

Dim arr
Dim Counter As Integer

Dim mySubject As String
Dim myBody As String
Dim mYRecipient As String

MYSTRING = “YOU MUST FILL IN ALL REQUIRED FIELDS. REQUESTER/SOURCE/HMO/IPA/GROUP# & PLAN”

If IsNull(cboRequester) Or IsNull(Combo779) Or IsNull([txtgroup#]) Or IsNull([cboSource]) Or IsNull(txtPlan) Then
MsgBox MYSTRING, vbExclamation, “DATA ENTRY ERROR”
Exit Sub
End If

mySubject = “ITCFM ” & “URGENT!!!!! ” & [HMO] & “/” & [IPA] & “/” & [Group Name] & “/” & [Group#]
myBody = “HMO: ” & [HMO] & Chr(10) & “IPA: ” & [IPA] & Chr(10) & “Group Name: ” & [Group Name] & Chr(10) & “Group#: ” & [Group#] & _
Chr(10) & “Plan Name: ” & [Plan] & Chr(10) & “Eff Date: ” & [EffDate] & Chr(10) & “IDX Plan#: ” & [IDX Plan#] & Chr(10) & “Contract Code/PPID: ” & _
[Contract Code (CC)] & Chr(10) & “Branch Code(Cigna):” & [txtBranchCode] & Chr(10) & “Benefit Option Code(Cigna):” & [txtBenefitOptionCode] & Chr(10) & _
“Unit #(Maxicare):” & [txtUnitNum] & Chr(10) & “Plan Description:” & [Plan Description] & Chr(10) & “OV CoPay: ” & [txtOVCoPay] & _
Chr(10) & Chr(10) & “COMMENTS:” & Chr(10) & _
[Notes_Comments] & Chr(10) & Chr(10) & Chr(10) & [Requester] ‘Add a comma and a zero here to send automatically
mYRecipient = Recip

Set EmailApp = CreateObject(“Outlook.Application”) ‘outlook object
Set NameSpace = EmailApp.GetNamespace(“MAPI”)
Set EmailSend = EmailApp.CreateItem(0) ‘CreateItem(0)Mail Item

With EmailSend
arr = Split(Recip, “,”)
For Counter = 0 To UBound(arr)
Set Recip= EmailSend.mYRecipient.Add(arr(Counter))
Next Counter[/color]

EmailSend.Subject = mySubject ‘subject
EmailSend.Body = myBody ‘body
EmailSend.recipients.Add (mYRecipient) ‘first add the email or user as a recipient
‘EmailSend.Importance = olImportanceHigh
EmailSend.Display ‘ Remove if you don’t want to view email before being sent.

[txtSentToIT] = Format(Date, “MM/DD/YYYY”)

Exit_SendMessage_Click:
Exit Sub

Err_SendMessage_Click:
MsgBox Err.Description
Resume Exit_SendMessage_Click

End Sub

Reply | Quote

There’s some confusion here because you and I use the variable Recip for different purposes. Change the line

Set Recip= EmailSend.mYRecipient.Add(arr(Counter))

to

Set objRecip= EmailSend.Recipients.Add(arr(Counter))

You may want to declare objRecip near the beginning of SendMessage:

Dim objRecip As Object

Reply | Quote

There’s some confusion here because you and I use the variable Recip for different purposes. Change the line

Set Recip= EmailSend.mYRecipient.Add(arr(Counter))

to

Set objRecip= EmailSend.Recipients.Add(arr(Counter))

You may want to declare objRecip near the beginning of SendMessage:

Dim objRecip As Object

It compiled fine, but when I hit the command button I get the message:

Object variable or With block variable not set

Thanks for your patience
Michael

Private Sub cmdSend_Click()
On Error GoTo Err_cmdSend_Click

SendMessage (“FranklJ@sutterhealth.org;abramsm@sutterhealth.org”)

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click

End Sub
Private Sub SendMessage(Recip As String)

On Error GoTo Err_SendMessage_Click

Dim NameSpace As Object
Dim EmailSend As Object
Dim EmailApp As Object

Dim MYSTRING As String

Dim ARR
Dim Counter As Integer
Dim objRecip As Object

Dim mySubject As String
Dim myBody As String
Dim mYRecipient As String

MYSTRING = “YOU MUST FILL IN ALL REQUIRED FIELDS. REQUESTER/SOURCE/HMO/IPA/GROUP# & PLAN”

With EmailSend
ARR = Split(Recip, “,”)
For Counter = 0 To UBound(ARR)
Set objRecip = EmailSend.Recipients.Add(ARR(Counter))

Next Counter

End With

If IsNull(cboRequester) Or IsNull(Combo779) Or IsNull([txtgroup#]) Or IsNull([cboSource]) Or IsNull(txtPlan) Then
MsgBox MYSTRING, vbExclamation, “DATA ENTRY ERROR”
Exit Sub
End If

mySubject = “ITCFM ” & “URGENT!!!!! ” & [HMO] & “/” & [IPA] & “/” & [Group Name] & “/” & [Group#]
myBody = “HMO: ” & [HMO] & Chr(10) & “IPA: ” & [IPA] & Chr(10) & “Group Name: ” & [Group Name] & Chr(10) & “Group#: ” & [Group#] & _
Chr(10) & “Plan Name: ” & [Plan] & Chr(10) & “Eff Date: ” & [EffDate] & Chr(10) & “IDX Plan#: ” & [IDX Plan#] & Chr(10) & “Contract Code/PPID: ” & _
[Contract Code (CC)] & Chr(10) & “Branch Code(Cigna):” & [txtBranchCode] & Chr(10) & “Benefit Option Code(Cigna):” & [txtBenefitOptionCode] & Chr(10) & _
“Unit #(Maxicare):” & [txtUnitNum] & Chr(10) & “Plan Description:” & [Plan Description] & Chr(10) & “OV CoPay: ” & [txtOVCoPay] & _
Chr(10) & Chr(10) & “COMMENTS:” & Chr(10) & _
[Notes_Comments] & Chr(10) & Chr(10) & Chr(10) & [Requester] ‘Add a comma and a zero here to send automatically
mYRecipient = Recip

Set EmailApp = CreateObject(“Outlook.Application”) ‘outlook object
Set NameSpace = EmailApp.GetNamespace(“MAPI”)
Set EmailSend = EmailApp.CreateItem(0) ‘CreateItem(0)Mail Item

EmailSend.Subject = mySubject ‘subject
EmailSend.Body = myBody ‘body
EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient
‘EmailSend.Importance = olImportanceHigh
EmailSend.Display ‘ Remove if you don’t want to view email before being sent.

[txtSentToIT] = Format(Date, “MM/DD/YYYY”)

Exit_SendMessage_Click:
Exit Sub

Err_SendMessage_Click:
MsgBox Err.Description
Resume Exit_SendMessage_Click

End Sub

Private Sub Command4_Click()

Do While Not IsNull([Record#])

[TAT] = Work_Days(txtSentToIT, txtDateCompleted)
DoCmd.GoToRecord , , acNext

Loop

End Sub

Reply | Quote

OK I am getting closer.

I moved the code:

Now when the email pops up, it has both names TWICE.

Private Sub SendMessage(Recip As String)

On Error GoTo Err_SendMessage_Click

Dim NameSpace As Object
Dim EmailSend As Object
Dim EmailApp As Object

Dim MYSTRING As String

Dim ARR
Dim Counter As Integer
Dim objRecip As Object

Dim mySubject As String
Dim myBody As String
Dim mYRecipient As String

MYSTRING = “YOU MUST FILL IN ALL REQUIRED FIELDS. REQUESTER/SOURCE/HMO/IPA/GROUP# & PLAN”

If IsNull(cboRequester) Or IsNull(Combo779) Or IsNull([txtgroup#]) Or IsNull([cboSource]) Or IsNull(txtPlan) Then
MsgBox MYSTRING, vbExclamation, “DATA ENTRY ERROR”
Exit Sub
End If

mySubject = “ITCFM ” & “URGENT!!!!! ” & [HMO] & “/” & [IPA] & “/” & [Group Name] & “/” & [Group#]
myBody = “HMO: ” & [HMO] & Chr(10) & “IPA: ” & [IPA] & Chr(10) & “Group Name: ” & [Group Name] & Chr(10) & “Group#: ” & [Group#] & _
Chr(10) & “Plan Name: ” & [Plan] & Chr(10) & “Eff Date: ” & [EffDate] & Chr(10) & “IDX Plan#: ” & [IDX Plan#] & Chr(10) & “Contract Code/PPID: ” & _
[Contract Code (CC)] & Chr(10) & “Branch Code(Cigna):” & [txtBranchCode] & Chr(10) & “Benefit Option Code(Cigna):” & [txtBenefitOptionCode] & Chr(10) & _
“Unit #(Maxicare):” & [txtUnitNum] & Chr(10) & “Plan Description:” & [Plan Description] & Chr(10) & “OV CoPay: ” & [txtOVCoPay] & _
Chr(10) & Chr(10) & “COMMENTS:” & Chr(10) & _
[Notes_Comments] & Chr(10) & Chr(10) & Chr(10) & [Requester] ‘Add a comma and a zero here to send automatically
mYRecipient = Recip

Set EmailApp = CreateObject(“Outlook.Application”) ‘outlook object
Set NameSpace = EmailApp.GetNamespace(“MAPI”)
Set EmailSend = EmailApp.CreateItem(0) ‘CreateItem(0)Mail Item

EmailSend.Subject = mySubject ‘subject
EmailSend.Body = myBody ‘body
EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient
‘EmailSend.Importance = olImportanceHigh
EmailSend.Display ‘ Remove if you don’t want to view email before being sent.

With EmailSend
ARR = Split(Recip, “,”)
For Counter = 0 To UBound(ARR)
Set objRecip = EmailSend.Recipients.Add(ARR(Counter))

Next Counter
End With

[txtSentToIT] = Format(Date, “MM/DD/YYYY”)

Exit_SendMessage_Click:
Exit Sub

Err_SendMessage_Click:
MsgBox Err.Description
Resume Exit_SendMessage_Click

End Sub

Reply | Quote

You should obviously remove the line

EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient

Reply | Quote

You should obviously remove the line

EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient

Obviously !!

Hans – once again you pulled through for me – it works like a charm now.

Much, much appreciated !!

Have a nice week !

Michael Abrams

Reply | Quote

I am so sorry for being a PITA, but using the below code does put both names into the TO line of the email but when Outlook opens and I click send, the same message appears:
Microsoft Office Outlook does not recognize FranklJ@mycompany.org;LortonC@mycompany.org

This looks like it may be turning into an Outlook inquiry. Any clues to why Outlook isn’t recognizing the
two recipients as spelled out above?

Command button code:
Private Sub cmdSend_Click()
On Error GoTo Err_cmdSend_Click

SendMessage (“FranklJ@mycompany.org;LortonC@mycompany.org”)

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click

Function:
Private Sub SendMessage(Recip As String)

On Error GoTo Err_SendMessage_Click

Dim NameSpace As Object
Dim EmailSend As Object
Dim EmailApp As Object

Dim MYSTRING As String

Dim ARR
Dim Counter As Integer
Dim objRecip As Object

Dim mySubject As String
Dim myBody As String
Dim mYRecipient As String

MYSTRING = “YOU MUST FILL IN ALL REQUIRED FIELDS. REQUESTER/SOURCE/HMO/IPA/GROUP# & PLAN”

If IsNull(cboRequester) Or IsNull(Combo779) Or IsNull([txtgroup#]) Or IsNull([cboSource]) Or IsNull(txtPlan) Then
MsgBox MYSTRING, vbExclamation, “DATA ENTRY ERROR”
Exit Sub
End If

mySubject = “ITCFM ” & “URGENT!!!!! ” & [HMO] & “/” & [IPA] & “/” & [Group Name] & “/” & [Group#]
myBody = “HMO: ” & [HMO] & Chr(10) & “IPA: ” & [IPA] & Chr(10) & “Group Name: ” & [Group Name] & Chr(10) & “Group#: ” & [Group#] & _
Chr(10) & “Plan Name: ” & [Plan] & Chr(10) & “Eff Date: ” & [EffDate] & Chr(10) & “IDX Plan#: ” & [IDX Plan#] & Chr(10) & “Contract Code/PPID: ” & _
[Contract Code (CC)] & Chr(10) & “Branch Code(Cigna):” & [txtBranchCode] & Chr(10) & “Benefit Option Code(Cigna):” & [txtBenefitOptionCode] & Chr(10) & _
“Unit #(Maxicare):” & [txtUnitNum] & Chr(10) & “Plan Description:” & [Plan Description] & Chr(10) & “OV CoPay: ” & [txtOVCoPay] & _
Chr(10) & Chr(10) & “COMMENTS:” & Chr(10) & _
[Notes_Comments] & Chr(10) & Chr(10) & Chr(10) & [Requester] ‘Add a comma and a zero here to send automatically
mYRecipient = Recip

Set EmailApp = CreateObject(“Outlook.Application”) ‘outlook object
Set NameSpace = EmailApp.GetNamespace(“MAPI”)
Set EmailSend = EmailApp.CreateItem(0) ‘CreateItem(0)Mail Item

EmailSend.Subject = mySubject ‘subject
EmailSend.Body = myBody ‘body
‘EmailSend.Recipients.Add (mYRecipient) ‘first add the email or user as a recipient
‘EmailSend.Importance = olImportanceHigh
EmailSend.Display ‘ Remove if you don’t want to view email before being sent.

With EmailSend
ARR = Split(Recip, “,”)
For Counter = 0 To UBound(ARR)
Set objRecip = EmailSend.Recipients.Add(ARR(Counter))

Next Counter

End With

[txtSentToIT] = Format(Date, “MM/DD/YYYY”)

Exit_SendMessage_Click:
Exit Sub

Err_SendMessage_Click:
MsgBox Err.Description
Resume Exit_SendMessage_Click

End Sub

Reply | Quote

Oops, I’m sorry, I should have seen that. Since you separate the names with semi-colons, you should change the line

ARR = Split(Recip, “,”)

to

ARR = Split(Recip, “;”)

Reply | Quote

Oops, I’m sorry, I should have seen that. Since you separate the names with semi-colons, you should change the line

ARR = Split(Recip, “,”)

to

ARR = Split(Recip, “;”)

That did it !! Hans, thank you so much for sharing your knowledge and being so patient.

Very sincerely,
Michael Abrams

Reply | Quote

You should obviously place the code that adds the recipients to the e-mail message AFTER the code that creates the e-mail message, not before!

Reply | Quote