email a bunch of people at once via a list (fields in list). 2

[verizonrep]

Okay folks, here's one I have not been able to find.

I have a table that has people in it. The list has their name and email address in it (as well as what criteria in which they are to be emailed). Overall, I was wondering if there is a way to merge all of the email listings into one field so that it all can be brought into the TO: field. Currently, I'm running a EOF VB and they are being sent one at a time. This can take a while if I am sending to 30 or so people. Just so I'm clear, here is an example of what I would love to do. This is my "members" table:

Name Address
Bob Smith Bob@yahoo.com
Sue Johns Sue@hotmail.com
John Doe John@earthlink.com

I want to merge them with a semicoso it reads in one field on a form (named "Addresses"):

Addresses
Bob@yahoo.com; sue@hotmail.com; John@Earthlink.com



In this case, this way, I send only one email, rather than three.

Also, (I don't think that it matters, but what the heck). We are using Lotus Notes.

Any ideas?


Thanks.

 

[lespaul]

use your EOF VB to build a TO string:

While not eof do
strRecipients := strRecipients + ';' + queryFieldValue

however, I know that in the LotusNotes scripts I have written, you have to use an array:

[b]Dim recipients( 1 To 2 ) As String
recipients( 1 ) = "bob@someaddress.com"
recipients( 2 ) = "sue@someotheraddress.com"[/b]
		
		If source.document.IsNewNote Then
			language = source.FieldGetText("OtherLanguage")
			docket = source.FieldGetText("DocketNo")
			fname = source.FieldGetText("FirstName")
			lname = source.FieldGetText("LastName")
			intdate = source.FieldGetText("InterviewDate")
			interviewer = source.FieldGetText("Interviewer")
			
			maildoc.Form = "Memo"
			maildoc.Subject = "Translator Needed"
			
			maildoc.Body = "Case Number: " + docket + ".      " + fname + " " + lname  + " , interviewed on " + intdate + " by " + interviewer + ", requires a " + language + " translator.  Thank you."
			
			[b]Call maildoc.Send( False, recipients )[/b]
		Else
			Exit Sub
		End If

what is the code you are currently using to send through Lotus?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[verizonrep]

Right now, I'm using a form to display names and email addresses. I am then using another form to create custom messages and subject lines. Here is what I'm running:


Private Sub Process_Click()
On Error GoTo Process_Click_Err

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.MoveFirst

Do Until rst.EOF

Me.Bookmark = rst.Bookmark
DoCmd.SendObject acReport, "CMS Sales Open Incentive Compensation Issues", "RichTextFormat(*.rtf)", Forms!Members_Form!Address, "", "", Forms!SubjectMessage_Frm!Subject, Forms!SubjectMessage_Frm!Message, False, ""
SendKeys "+{ESC}", False
rst.MoveNext
Loop

Process_Click_Exit:
Exit Sub

Process_Click_Err:
MsgBox Error$
Resume Process_Click_Exit

End Sub


This emails the report one at a time. It doesn't take too long, but it does take about 3 minutes to do about 30 people.

 

[Bstrick75]

This is what I did in a similar situation. I needed to create a database that would automatically queries all records that were added within a certain time period and find out what wholesalers those records belonged to. Then it would send an e-mail notification out to each of those wholesalers including a weekly report of the data that has been inputted.

I have a table that has the wholesalers and thier e-mail addresses. I setup a query that tells me what what wholesalers belong to what records. I put these results in a form and wrapped it into a listbox.

I used the windows scheduler like you did. I then setup a form as the form that pops up when the application opens up. I set a timeer on the form along with a "cancel" button. The timer run for 30 seconds so that if no-one presses the "cancel" button within 30 seconds then my "frmWholesalerAutoReport" opens.

I setup frmWholesalerAutoReport's load even so that it automatically goes through each record in the listbox (the listbox again is a query against the actaul data in the application and the wholesaler report that has the e-mail addresses in it) If there was data entered for that week and if there is a matching wholesaler then a record will appear in the listbox.

It loops through the listbox, finds wholesaler's name, sends out a e-mail and attaches the wholesaler report, using the Wholesaler name as a parameter within that report. Keep in mind that this e-mails out to 10 different people.

Here is some sample code:

Private Sub Form_Open(Cancel As Integer)
RunReport
DoCmd.Close
End Sub

Private Sub RunReport()

Dim Line As Variant
Dim row As Variant
On Error GoTo Err_cmdReport_Click
Dim intcount As Integer
Dim CCemail As String

For row = 1 To lstWholesaler.ListCount - 1
lstWholesaler.Selected(row) = True
lblWholesalerNum.Caption = lstWholesaler.ItemData(row)
lblContact.Caption = lstWholesaler.Column(2, row)
lblEmail.Caption = lstWholesaler.Column(3, row)
lbl2ndEmail.Caption = lstWholesaler.Column(4, row)
lblWholesalerName.Caption = lstWholesaler.Column(1, row)


lbl2ndEmail.Caption = ""
If Len(lstWholesaler.Column(4, row)) > 2 Then
lbl2ndEmail.Caption = lstWholesaler.Column(4, row)
End If
If Len(lstWholesaler.Column(5, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(5, row)
End If
If Len(lstWholesaler.Column(6, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(6, row)
End If
If Len(lstWholesaler.Column(7, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(7, row)
End If
If Len(lstWholesaler.Column(8, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(8, row)
End If
If Len(lstWholesaler.Column(9, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(9, row)
End If
If Len(lstWholesaler.Column(10, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(10, row)
End If
If Len(lstWholesaler.Column(11, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(11, row)
End If
If Len(lstWholesaler.Column(12, row)) > 2 Then
lbl2ndEmail.Caption = lbl2ndEmail.Caption & "; " & lstWholesaler.Column(12, row)
End If

lstCount.Requery
intcount = lstCount.ListCount
If intcount > 0 Then
If Len(lblEmail.Caption) < 1 Then
Else
Dim stDocName As String

DoCmd.SendObject acSendReport, "rptWholesalerReport_Auto", "Rich Text Format", lblEmail.Caption, lbl2ndEmail.Caption, , "Company Name", lblWholesalerName.Caption & " Account Management Team," & vbNewLine & vbNewLine & vbTab & "Please find attacted the listing for the upcoming Account Information for your accounts. If you have questions regarding this report or the detail of the account activity, please contact your Key Account Manager" & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Thank You!" & vbNewLine & vbNewLine & "National Accounts", False
Else
End If
Next

DoCmd.Close

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub


Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[lespaul]

Here's a link to a thread that shows the VBA code for creating a Notes memo. You will want to modify it a bit to create an array of recipients like I did above and then you will get a single email with multiple recipients.

HTH

leslie

Thread705-394014

 

[verizonrep]

Thanks to both of you.

Bstrick75 - I'm trying to understand the code. It looks like it still sends out the emails one at a time. Am I missing something?

lespaul - I haven't tried the code yet, but from what I'm reading so far, it looks like it might just work.

Thanks again,


Will

 

[Bstrick75]

It does send off an email one at a time. Each wholesaler get a uniquely different report that contains they're account info. An attachment is made of this report and sent off to all the email accounts for that wholesaler.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[verizonrep]

That's what I currently have now. My goal is to convert it so that I can send one report and email it out to many people at one time.

I have a series of reports that go out to a group of people. To do that, the only thing I can think of is to group all fot he emails that are in a field and group it into one field, so that the "to" line can be populated with all the addresses.

Any ideas?

Thanks,

Will

 

[Bstrick75]

The code that I sent you does that as well. It sends the report out to ten people at one time. If you notice, I use the If/Then code to determine if there is an email address. If there is then I concatenate it to the CC section of the email. All you have to do is scale it out.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[verizonrep]

Basically, I would need to continue with the if statements for quite a while, and there would be a limit to how many people could be done at once (limited by how many If statements I had), correct?

 

[Bstrick75]

Yes, how many people are you going to be emailing?

For me, I have a table with rows that don't always contain e-mail addresses. If you know that there will always be an e-mail address then you don't need the If/then statements. You can just loop through and grab all the email addresses. Or you can take a more object oriented approach and create a function that you continuously call that does the work for you.

You build it once and call it within you loop to check if an e-mail address exist. Maybe include a "Email exists" boolean variable. If it does have a valid email address it returns the e-mail address and a true value. If not it returns a false value. The loop then evaluates that value to see if its true. If it is then it grabs and concatenates the e-mail address. If its false then it skips it and moves to the next one.

This way there is only one if then statement and one function.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[verizonrep]

Basically, I just do a query that only lists those emails that are not null. That is my distribution list. This way, there are no blanks.

Will

 

[Bstrick75]

Yep,

Respond back once its done and let me know how things went.

Bobby Strickland
Solutions Engineer
Strictly Consulting, Inc
http:

http://www.StrictlyConsulting.net

'Pleasure in the job puts perfection in the work' -- Aristotle

 

[verizonrep]

Will do. It might take a couple of days. I'll probably get a chance to work with it on Thursday.

Thanks again.