Creating one record "String" from email addresses in multiple records

[pam0566]

I have a database (Cnpr 2003.mdb) and inside a table "Table1" and a field in that table "Email_Addr". I have a list of names and email addresses. i am trying to create a field that merges all of the records "email addresses" into 1 string separated by a ";". (so i can email via outlook to a list of people from my database). I have played with the code, but have been unsucessful. it has been 4 years since i have done any of this and it has completely escaped me!!! Any help?

[pbaldy]

It would have been helpful to see/correct your code, but you can start here:

http://www.granite.ab.ca/access/email/recordsetloop.htm

Basically you want a loop but rather than send within the loop, you'd build your string.

[June7]

Do you want recipients to all be aware of each other? Instead of building the TO string can build BCC string and the TO could be yourself.

How many receipients? I don't know if Outlook has a limit on number of addresses or total length of addresses string.

Post your attempted code for analysis if you still have issues with the looping structure.

[pam0566]

no limit, or if its in a string on a form I can copy half, and then do other half. don't think limit will be an issue. No I would prefer BCC, thanks for bringing that up. I am having trouble figuring out the code have scratched everything and looking for a heads up to start all over before I totally screw up whats left of my data base ha ha.. I don't need it to actually email either, just to create the string in a form or something that I can copy would be fine.

[June7]

Here is example of code to send one email to one receipient http://forums.aspfree.com/microsoft-...ro-447084.html

pbaldy referenced an example with a loop to send individual email to many recipients. Whether or not you want to update a date field in table is optional.

Build string in loop with:

strBCC = strBCC & ";" & RS!cEmailAddress

Then need to chop the orphan ";" after the last address so outside the address build loop before sending email:

strBCC = Left(strBCC, Len(strBCC)-1)

Post your attempt to integrate these examples.

[pam0566]

i tried using this code (see below) but get errors. i am now so confused, this was on a module, as per a link from pbaldy's post.:

Option Compare Database
Option Explicit
Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
   ' Create the e-mail message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   TheAddress = MyRS![EmailAddress]
   With objOutlookMsg
      ' Add the To recipients to the e-mail message.
      Set objOutlookRecip = .Recipients.Add(TheAddress)
      objOutlookRecip.Type = olTo
      ' Add the Cc recipients to the e-mail message.
      If (IsNull(Forms!frmMail!CCAddress)) Then
      Else
         Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
         objOutlookRecip.Type = olCC
      End If

      ' Set the Subject, the Body, and the Importance of the e-mail message.
      .Subject = Forms!frmMail!Subject
      .Body = Forms!frmMail!MainText
      .Importance = olImportanceHigh 'High importance

      'Add the attachment to the e-mail message.
      If Not IsMissing(AttachmentPath) Then
         Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      End If
      ' Resolve the name of each Recipient.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
            objOutlookMsg.Display
         End If
      Next
      .Send
   End With
   MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

[June7]

What errors do you get - error message, wrong results, nothing happens?

Did you set VBA reference to Microsoft Outlook Object Library?

Do you want one email with a long BCC string for multiple addresses?

Or do you want individual multiple emails, each to a single TO address?

How are you executing this procedure? What is the code that calls it?

Refer to link at bottom of my post for guidelines on debugging techniques.

[pam0566]

I set the reference.. I do want one long bcc string. it tells me compile error: ambiguous name detected: sendmessages

[June7]

The procedure cannot have same name as a module.

Procedures in same module cannot have same name.

That code does not build a BCC string.