How can I send an email to multiple email addresses through Outlook 2007

[farisallil]

Hi
I have a table called Info with a column called Email Address in it. I want to create a command button in a form called Customer ,which has the Info table as the Record Source, and I want this command to open up a new email window in Outlook 2007 and copy all the email addresses to the To field without forgetting adding a semicolon after each email

How can I do that? or what is the VB code for such button?
I'm not so good in Access but I have a little experience

Any help will be greatly appreciated

[ADezii]

@farisallil
First, make sure that you have a Reference set to the Microsoft Outlook XX.X Object Library. Assuming you have a Table named tblEMailAddress, and it contains a Field to hold the E-Mail Addresses named [EMail Address] :

Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailAddress", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![EMail Address] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Test E-Mail to Multiple Recipients"
  .Subject = "Yada, Yada, Yada"
    .Send
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing

[ADezii]

P.S. - To Open Outlook and NOT Send automatically, replace .Send with .Display.

[ChipR]

This code does not require a reference to Outlook, and I would suggest not using one if there is a possibility of your application being around a while.

[ADezii]

@ChipR
Sorry ChipR, but you lost me, what code are you referring to?

[ChipR]

Your code in post #2.

[ADezii]

@ChipR
Hello ChipR, the code in Post #2 is virtually useless without a Reference to the Outlook Type Library.

[ChipR]

Nope, it works perfectly fine without it. It uses late binding as the variables are declared as Object.

[ADezii]

@ChipR
Thanks ChipR, for this interesting insight.

[Mmmel]

Hi! I've "borrowed" Adezil's code (in the first response above), and it works GREAT! Thank you!

However, I want to do one thing a bit different. I want to place a field from a different table AS the body of the email. To be more specific, I need to send one of 7 different types of emails to students who have attendance problems; I have the body of each of the 7 email types in memo fields in a table called Letters. So, there's one called Letter 1, then Letter 2, and so on. Can I do this? I think I could do it by using a text file (I've seen some code on this from this site), but I want to keep everything within the database. Seems simple, but the code keeps throwing up no matter what syntax I use.

Any ideas? I would be very thankful.

Thanks in advance!

Melody

[ADezii]

You will need to be a little more specific as to what you are requesting, posting all relevant information such as: Table Names, Field Names, current code context, Relationships if any, etc.

[Mmmel]

Yes...information is good!

Here goes:

I'm sending an email from a query called qryAtRisk, that loops through a field called fldEmails to make the To: part and then fire up an email message. Got that, no problem.

Here's a snippet of your code:

With oMail
.To = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
.Body = "Test E-Mail to Multiple Recipients"
.Subject = "Yada, Yada, Yada"
.Display
End With

Set oMail = Nothing
Set oOutlook = Nothing

I want to replace the part of the code bolded, underlined, and italicized above with a reference to a memo field called fldLetter1 in a table called tblLetters, so is just dumps whatever text is in that field into the body of the email. That's it! (sounds so eeeeeezy!) : )

Perhaps this is not the way to do it? Any advice?

[ADezii]

Assuming there is only a Single Record in tblLetters, to retrieve the value in the [fldLetter1] Field and place it in the Body Segment:

With oMail
  .To = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
  .body = DLookup("[fldLetter1]", "tblLetters")
  .Subject = "Yada, Yada, Yada"
    .display
End With

[Mmmel]

Ahhh...the old DLookUp! Great idea!

There are actually three fields in tblLetters:
1. Letter_ID (Autonumber primary key)
2. Letter_type
3. fld_Letter1 (this contains the text)

I would then just pass the ID somehow?

Oh, SO CLOSE! Thanks! : )

[ADezii]

I would then just pass the ID somehow?

Lost me here, what exactly are you referring to?

[Mmmel]

Sorry - I'd like it if everyone lived in my brain! : )

My Letters table has three fields:
1. Letter_ID - this contains the unique ID that goes with each of the seven letters (they are simply numbered 1 to 7),
2. Letter_Name - this contains the name of the letter, like "Attendance Warning Letter" and
3. Letter_Text - this contains the actual text that I want to dump into the body of the email, Like "Your attendance of late has been substandard. You are in danger of failing the course if your attendance does not improve." and so on...

So, I want to loop through emails of students who I've checked off as having attendance problems (this is another thing I'll have to figure out how to do! I think using an IF statement somewhere). I put these emails in the To: field (so it would eventually read "[e-mail]; [e-mail];" and so on.) That part I (mostly) have. But, for those emails, I want to dump the text from a particular letter (one of the 7 I have in the Letters table); this text will correspond to whatever button I have pushed. That is, if I call the button "Sent Letter 1", the body of the email would contain the text from the Letter_Text field of the first letter, or the one with Letter_ID equal to 1. If I want to send the students a 7 day letter (kicking them out of the program), I would dump the Letter_Text from the seventh letter, or the letter with the Letter_ID equal to 7.

I hope that makes sense now! I'm guessing it's just a small change to the code you sent. Can you help? Thanks! : )

[ADezii]

1. Create a Public Sub-Routine named EMailLetter(), as in:
   

   Public Sub EmailLetter(intLetterID As Integer)
   'Partial Code included
   With oMail
     .To = Left$(strEMail, Len(strEMail) - 1)          'Remove Trailing ;
     .body = DLookup("[Letter_Text]", "tblLetters", "[Letter_ID] = " & intLetterID)
     .Subject = "Yada, Yada, Yada"
       .display
   End With
   End Sub

2. In the Click() Event of your Command Buttons, place similar code depending on the Button Number (Button 1 currently displayed):
   

   Private Sub Button1_Click()
     Call EmailLetter(1)
   End Sub

3. Repeat for the other Buttons, namely:
   

   Call EmailLetter(2), Call EmailLetter(3), ...Call EmailLetter(7)

[Mmmel]

Thanks again for your quick response!

This is much sleeker than I was planning to try!

I'll give this a go. Have a great night!

Melody

[Mmmel]

Hello again!

I haven't yet had time to implement the ideas above, because I'm totally and utterly stuck on simply getting an email to open! I'm using Adezii's code above, and it works perfectly in my choir database. However, in my student database, it crashes on the .Bcc line below. I get a "Run time Error '5', invalid procedure call or argument." This is better than what I used to get, which was nothing at all (as in, nothing happened when I clicked the button!).

I'm using the following code:

Private Sub Command65_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
 
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
'Retrieve all E-Mail Addressess in Email: Students Fall
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From [Email: Students Fall]", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![E-mail] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = "[e-mail]"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Body of message goes here."
  .Subject = "Subject line goes here."
    .Display
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing
 
End Sub

Anybody know why it gacks on that line? What am I missing? Thanks in advance!

[ADezii]

Could there possibly be NULL Value(s) in the [E-mail] Field?

[Mmmel]

Oooooh...I got so excited! But, alas, no. I use an "is not null" criteria for the [E-mail] field in the query.

Any other ideas?

[ADezii]

Modify the .Bcc = Line (Code Line #28), to send a Blind Carbon Copy to a Known and Valid E-Mail Address and see if it works.