Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Send email to multiple names in a textbox 1

Status
Not open for further replies.
netrusher

netrusher

Technical User
Feb 13, 2005
952
US
I have a textbox on my form. This texbox is populated from a Listbox. What I am trying to accomplish is the
following:

I want to tie an event to a button and when the button is clicked I would like for it to pull all the names from
the TextBox [Engineering Distribution] and have them become To: on a email ToEmail1. I think I know how
to do this with one name in the Textbox but I would like for it to add every name in the Textbox to the email.
The names are located in a Table FormEngMETbl The Full Name is in the EngME Field. The login
Name is in the [LoginName] field of the Table. If Allen Jackson is the Name in the TextBox, his login Name
would be abajacks. Each Name in the TextBox would have a LoginName. I would have to add @nmhg.com
to each LoginName. Again, I can do this with one name but do not know how to accomplish this with mulitple
names in the textbox. Can anyone help me accomplish this or does anyone have abetter idea on how to
do this?

Code: 
       Dim O As Outlook.Application
       Dim m As Outlook.MailItem
       Dim toEmail1 As String
       Dim toEmail2 As String
       Dim ccEmail1 As String
       Dim ccEmail2 As String
       Dim ccEmail3 As String
       Dim bccEmail1 As String
       Dim SL As String, DL As String
       SL = vbNewLine
       DL = SL & SL

Set O = CreateObject("Outlook.Application")
Set m = Outlook.CreateItem(0)
toEmail1 = ""
ccEmail1 = "abkkulhy@nmhg.com"
ccEmail2 = "abkkulhy@nmhg.com"
ccEmail3 = ""
bccEmail1 = "abajacks@nmhg.com"

m.To = toEmail1
m.CC = ""
m.BCC = bccEmail1
m.Subject = "Form #" & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & "; This ECN is ready for SOURCING"
m.Body = "This ECN is Ready for SOURCING." & DL & _
       "Form # " & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & DL & _
       "Thank you for your help" & DL & _
       DLookup("ActualName", "ChangeFormUserNameTbl", "LoginName='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"

m.Display

On Error GoTo Err_CloseForm_Click


    DoCmd.Close

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
    
End Sub
 
Sort by date Sort by votes
HI

If you have multiple names, presumably they are seperated by some form of delimiter, a comma perhaps?

See SPLIT() function, this (as it name suggests) will allow you to seperate the names into a list in an array, you can then iterate thru the array with a Do or For loop, adding the "@nmhg.com" part of the EMail address and saving it in the appropriate property of the EMail object

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
 
Upvote 0 Downvote
I am looking that up. Do you have an examples with code
for me?
 
Upvote 0 Downvote
I know how to do this with one name in the Textbox
Please, show us how you do this and say us how the mist of names is built.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
The Listbox names come from a Table:
Allen Jackson;
Anthony Wasson;
Dwight Cross;
Chris Allen;

Code: 
DLookup("LoginName", "ChangeFormUserNameTbl", "ActualName='" & Forms!ECNBCNVIPfrm![ECN Analyst] & "'") & "@nmhg.com"

This is an example of code that is working on another field.
 
Upvote 0 Downvote
What is a typical content of [Engineering Distribution] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Allen Jackson;
Anthony Wasson;
Dwight Cross;
Chris Allen;
 
Upvote 0 Downvote
Why not using a MultiSelect ListBox instead ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
This is a multiSelect ListBox. The ListBox popsup and
I selecte the names that I want and it populates the
TextBox.
 
Upvote 0 Downvote
I click on the TextBox [Engineering Distribution] and
the code below runs. The ListBox pops-up and I select
the names I want to populate the TextBox.

Code: 
Private Sub Engineering_Distribution_Click()
On Error GoTo Err_Engineering_Distribution_Click

    Dim stDocName As String

    stDocName = "NamesMeFrm"
    DoCmd.OpenForm stDocName, acNormal
    
Exit_Engineering_Distribution_Click:
    Exit Sub

Err_Engineering_Distribution_Click:
    MsgBox Err.Description
    Resume Exit_Engineering_Distribution_Click
End Sub
 
Upvote 0 Downvote
Which code populates the TextBox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
The code that I just posted populates the TextBox.
 
Upvote 0 Downvote
How a single DoCmd.OpenForm may populate a TextBox ????
 
Upvote 0 Downvote
Sorry, I misunderstood you. The other code opens the
ListBox and the below code AfterUpdate Event populates
the Textbox.

Code: 
  Dim varItem As Variant
  Dim strEngineer As String
  For Each varItem In Me.MfgEngList.ItemsSelected
    strEngineer = strEngineer & Me.MfgEngList.ItemData(varItem) & vbCrLf
  Next varItem
    strEngineer = Trim(strEngineer)
  Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution] = strEngineer
 
Upvote 0 Downvote
OK, so the delimiter for the suggested Split function is vbCrLf:
Code: 
toEmail1 = ""
Dim x As String
For Each x In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution], vbCrLf)
  If Len(Trim(x)) > 0 Then
    toEmail1 = toEmail1 & DLookup("LoginName", "ChangeFormUserNameTbl", "ActualName='" & x & "'") & "@nmhg.com;"
  End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PHV,

Thanks for your help on this. I am getting the below error:
Compile error:

For Each control variable must be Variant or Object
Click to expand...

This is the code I am using.

Code: 
Private Sub CloseForm_Click()

       Dim O As Outlook.Application
       Dim m As Outlook.MailItem
       Dim toEmail1 As String
       Dim toEmail2 As String
       Dim ccEmail1 As String
       Dim ccEmail2 As String
       Dim ccEmail3 As String
       Dim bccEmail1 As String
       Dim SL As String, DL As String
       'Dim x As String
       SL = vbNewLine
       DL = SL & SL

Set O = CreateObject("Outlook.Application")
Set m = Outlook.CreateItem(0)
toEmail1 = toEmail1 = ""
                Dim x As String
                For Each x In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution], vbCrLf)
                If Len(Trim(x)) > 0 Then
                toEmail1 = toEmail1 & DLookup("LoginName", "FormEngMETbl", "EngME='" & x & "'") & "@nmhg.com;"
                End If
                Next
ccEmail1 = "abkkulhy@nmhg.com"
ccEmail2 = "abkkulhy@nmhg.com"
ccEmail3 = ""
bccEmail1 = "abajacks@nmhg.com"

m.To = toEmail1
m.CC = ""
m.BCC = bccEmail1
m.Subject = "Form #" & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & "; This ECN is ready for SOURCING"
m.Body = "This ECN is Ready for SOURCING." & DL & _
       "Form # " & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & DL & _
       "Thank you for your help" & DL & _
       DLookup("ActualName", "ChangeFormUserNameTbl", "LoginName='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"

m.Display

On Error GoTo Err_CloseForm_Click


    DoCmd.Close

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
    
End Sub
 
Upvote 0 Downvote
Please ignore my toEmail = that I have showed twice.
 
Upvote 0 Downvote
I am most sure that I have kept something in or left
something out. Sorry and again, thanks for the help.
 
Upvote 0 Downvote
Code: 
...
Set m = Outlook.CreateItem(0)
toEmail1 = ""
Dim x
For Each x In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution], vbCrLf)
  If Len(Trim(x) & "") > 0 Then
    toEmail1 = toEmail1 & DLookup("LoginName", "FormEngMETbl", "EngME='" & x & "'") & "@nmhg.com;"
  End If
Next
ccEmail1 = "abkkulhy@nmhg.com"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PHV,

Now I am getting the below error:
Run-time error '1002':

You canceled the previous operation.
Click to expand...

Code: 
Private Sub CloseForm_Click()

       Dim O As Outlook.Application
       Dim m As Outlook.MailItem
       Dim toEmail1 As String
       Dim toEmail2 As String
       Dim ccEmail1 As String
       Dim ccEmail2 As String
       Dim ccEmail3 As String
       Dim bccEmail1 As String
       Dim SL As String, DL As String
       SL = vbNewLine
       DL = SL & SL

Set O = CreateObject("Outlook.Application")
Set m = Outlook.CreateItem(0)
toEmail1 = ""
Dim x
For Each x In Split(Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution], vbCrLf)
  If Len(Trim(x) & "") > 0 Then
    toEmail1 = toEmail1 & DLookup("LoginName", "FormEngMETbl", "EngME='" & x & "'") & "@nmhg.com;"
  End If
Next
ccEmail1 = "abkkulhy@nmhg.com"
ccEmail2 = "abkkulhy@nmhg.com"
ccEmail3 = ""
bccEmail1 = "abajacks@nmhg.com"

m.To = toEmail1
m.CC = ""
m.BCC = bccEmail1
m.Subject = "Form #" & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & "; This ECN is ready for SOURCING"
m.Body = "This ECN is Ready for SOURCING." & DL & _
       "Form # " & Forms!ECNBCNVIPfrm![ECNBCNVIP ID].Value & DL & _
       "Thank you for your help" & DL & _
       DLookup("ActualName", "ChangeFormUserNameTbl", "LoginName='" & GetCurrentUserName() & "'") & SL & _
       "ECN Analyst"

m.Display

On Error GoTo Err_CloseForm_Click


    DoCmd.Close

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
    
End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Lamar Washington
  • Locked
  • Question
Programmatically send email using only OWA
Replies
2
Views
320
Andrzejek
Andrzejek
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
494
PrimoSteve
PrimoSteve
I
  • Locked
  • Question
How to get 2 codes to work together.
Replies
6
Views
455
hermanlaksko
hermanlaksko
Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
666
Kennelbloke
Kennelbloke
BJZeak
  • Locked
  • Question
Struggling with finding an answer to a simple question
Replies
5
Views
538
BJZeak
BJZeak

Part and Inventory Search

Sponsor

Back
Top