Replies

Just my 2 cents:

Every once in awhile, this happens to me. No rhyme, no reason.

When it happens, I go to:
Tools/Options/Calculation tab.

Somehow, the “Manual” radio button is checked.

All I do is change it to Automatic, and life is back to normal.

Don’t know why it happens, but that’s how I fix it. Stuff like that drives me crazy !!

Michael

Thank you so much Deborah – it works perfectly now !! Much appreciated.

Michael

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

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

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

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

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

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

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

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

First of all, I would like to thank Hans for your patience and pieterse for the tutorial.

You guys are awesome and I really appreciate the time you take to share your knowledge (and patience)

Very Sincerely,
Michael Abrams

If a toolbar is docked at the top of the Excel window, like the Standard and Formatting toolbars are by default, its title (caption) isn’t displayed, only the buttons. When you undock it (use the “handle” on the left hand side to drag it away from the top), the title bar will appear again. This is “normal” behavior for toolbars.

Wow !

On my previous computer it sat right on the top menu bar and said Reports with a dropdown of the macros.

It works fine now that I know this.

Thank you Hans !

Michael

Make sure that you drop the macro squarely on the toolbar and not beside it (the latter is easy to do if you want to add the button at the end, it has happened to me several times)

This is so frustrating.

When the toolbar is created, it appears ‘on’ the spreadsheet. I placed the button squarely on the toolbar.
The title Reports still apears, and the button is ‘in’ the toolbar.
When I drag the new toolbar up to the Standard toolbar, the button replaces the word Reports.

I will keep trying though !!

Thank you Hans.

I created the custom toolbar (Reports)

When I drag a custom button from Commands/Macros to the new toolbar, the title Reports goes away, and the macro IS the toolbar. There is no ‘dropdown’. I hope I am describing this clearly).

I cannot imagine what I am doing wrong. Can you assist?

You could try opening the .xlb file and see if that works. If not, you’ll probably have to reassign the macros to the toolbar buttons one by one.

Looks like it’s going to be 1 by 1. So sad……….

Thanks Hans !

Michael