By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,216 Members | 1,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,216 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Jul 25 '09 #1
Share this Question
Share on Google+
21 Replies


ADezii
Expert 5K+
P: 8,669
@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] :
Expand|Select|Wrap|Line Numbers
  1. Dim strEMail As String
  2. Dim oOutlook As Object
  3. Dim oMail As Object
  4. Dim strAddr As String
  5. Dim MyDB As DAO.Database
  6. Dim rstEMail As DAO.Recordset
  7.  
  8. Set oOutlook = CreateObject("Outlook.Application")
  9. Set oMail = oOutlook.CreateItem(0)
  10.  
  11. 'Retrieve all E-Mail Addressess in tblEMailAddress
  12. Set MyDB = CurrentDb
  13. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailAddress", dbOpenSnapshot, dbOpenForwardOnly)
  14.  
  15. With rstEMail
  16.   Do While Not .EOF
  17.     'Build the Recipients String
  18.     strEMail = strEMail & ![EMail Address] & ";"
  19.       .MoveNext
  20.   Loop
  21. End With
  22. '--------------------------------------------------
  23.  
  24. With oMail
  25.   .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  26.   .Body = "Test E-Mail to Multiple Recipients"
  27.   .Subject = "Yada, Yada, Yada"
  28.     .Send
  29. End With
  30.  
  31. Set oMail = Nothing
  32. Set oOutlook = Nothing
  33.  
  34. rstEMail.Close
  35. Set rstEMail = Nothing
Jul 25 '09 #2

ADezii
Expert 5K+
P: 8,669
P.S. - To Open Outlook and NOT Send automatically, replace .Send with .Display.
Jul 25 '09 #3

Expert 100+
P: 1,287
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.
Jul 27 '09 #4

ADezii
Expert 5K+
P: 8,669
@ChipR
Sorry ChipR, but you lost me, what code are you referring to?
Jul 27 '09 #5

Expert 100+
P: 1,287
Your code in post #2.
Jul 28 '09 #6

ADezii
Expert 5K+
P: 8,669
@ChipR
Hello ChipR, the code in Post #2 is virtually useless without a Reference to the Outlook Type Library.
Jul 28 '09 #7

Expert 100+
P: 1,287
Nope, it works perfectly fine without it. It uses late binding as the variables are declared as Object.
Jul 29 '09 #8

ADezii
Expert 5K+
P: 8,669
@ChipR
Thanks ChipR, for this interesting insight.
Jul 29 '09 #9

P: 52
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
Feb 15 '10 #10

ADezii
Expert 5K+
P: 8,669
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.
Feb 15 '10 #11

P: 52
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?
Feb 15 '10 #12

ADezii
Expert 5K+
P: 8,669
Assuming there is only a Single Record in tblLetters, to retrieve the value in the [fldLetter1] Field and place it in the Body Segment:
Expand|Select|Wrap|Line Numbers
  1. With oMail
  2.   .To = Left$(strEMail, Len(strEMail) - 1) 'Remove Trailing ;
  3.   .body = DLookup("[fldLetter1]", "tblLetters")
  4.   .Subject = "Yada, Yada, Yada"
  5.     .display
  6. End With
Feb 15 '10 #13

P: 52
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! : )
Feb 15 '10 #14

ADezii
Expert 5K+
P: 8,669
I would then just pass the ID somehow?
Lost me here, what exactly are you referring to?
Feb 15 '10 #15

P: 52
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 "johnny@domain.com; mary@ether.com;" 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! : )
Feb 16 '10 #16

ADezii
Expert 5K+
P: 8,669
  1. Create a Public Sub-Routine named EMailLetter(), as in:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub EmailLetter(intLetterID As Integer)
    2. 'Partial Code included
    3. With oMail
    4.   .To = Left$(strEMail, Len(strEMail) - 1)          'Remove Trailing ;
    5.   .body = DLookup("[Letter_Text]", "tblLetters", "[Letter_ID] = " & intLetterID)
    6.   .Subject = "Yada, Yada, Yada"
    7.     .display
    8. End With
    9. End Sub
  2. In the Click() Event of your Command Buttons, place similar code depending on the Button Number (Button 1 currently displayed):
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Button1_Click()
    2.   Call EmailLetter(1)
    3. End Sub
  3. Repeat for the other Buttons, namely:
    Expand|Select|Wrap|Line Numbers
    1. Call EmailLetter(2), Call EmailLetter(3), ...Call EmailLetter(7)
Feb 16 '10 #17

P: 52
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
Feb 16 '10 #18

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command65_Click()
  2. Dim strEMail As String
  3. Dim oOutlook As Object
  4. Dim oMail As Object
  5. Dim strAddr As String
  6. Dim MyDB As DAO.Database
  7. Dim rstEMail As DAO.Recordset
  8.  
  9.  
  10. Set oOutlook = CreateObject("Outlook.Application")
  11. Set oMail = oOutlook.CreateItem(0)
  12.  
  13. 'Retrieve all E-Mail Addressess in Email: Students Fall
  14. Set MyDB = CurrentDb
  15. Set rstEMail = MyDB.OpenRecordset("Select * From [Email: Students Fall]", dbOpenSnapshot, dbOpenForwardOnly)
  16.  
  17. With rstEMail
  18.   Do While Not .EOF
  19.     'Build the Recipients String
  20.     strEMail = strEMail & ![E-mail] & ";"
  21.       .MoveNext
  22.   Loop
  23. End With
  24. '--------------------------------------------------
  25.  
  26. With oMail
  27.   .To = "someone@domain.ca"
  28.   .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  29.   .Body = "Body of message goes here."
  30.   .Subject = "Subject line goes here."
  31.     .Display
  32. End With
  33.  
  34. Set oMail = Nothing
  35. Set oOutlook = Nothing
  36.  
  37. rstEMail.Close
  38. Set rstEMail = Nothing
  39.  
  40. End Sub
Anybody know why it gacks on that line? What am I missing? Thanks in advance!
Apr 6 '10 #19

ADezii
Expert 5K+
P: 8,669
Could there possibly be NULL Value(s) in the [E-mail] Field?
Apr 7 '10 #20

P: 52
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?
Apr 7 '10 #21

ADezii
Expert 5K+
P: 8,669
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.
Apr 7 '10 #22

Post your reply

Sign in to post your reply or Sign up for a free account.