Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SENDMESSAGE

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The SendMail function in the attachment to Post 728849 shows how to handle multiple recipients in a string, separated by semi-colons.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797581' date='12-Oct-2009 12:38']The SendMail function in the attachment to Post 728849 shows how to handle multiple recipients in a string, separated by semi-colons.[/quote]

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797594' date='12-Oct-2009 13:08']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.[/quote]

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

    Thank you for your help Hans.

    Michael

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll have to modify it to fit in with the your own code, of course.

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797597' date='12-Oct-2009 13:21']You'll have to modify it to fit in with the your own code, of course.[/quote]
    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



    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797600' date='12-Oct-2009 13:33']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[/quote]

    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.or g")


    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

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should obviously remove the line

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

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797612' date='12-Oct-2009 13:54']You should obviously remove the line

    EmailSend.Recipients.Add (mYRecipient) 'first add the email or user as a recipient[/quote]


    Obviously !!

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

    Much, much appreciated !!

    Have a nice week !

    Michael Abrams

  14. #14
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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, ";")

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •