Putting Email addresses into a BBC field

capemem

New member
Local time
Today, 21:56
Joined
Dec 18, 2008
Messages
7
Hi

I have hijacked a bit of code to facilitate grabbing a whole lot of email address from a table and put them into an email. I have modified the code so rather than create a seperate one for each email address they all go into one email. The problem that I have is that they all go into the To: field and I need them to be in the BCC: field in the email. I have looked on this site and many others and have not found a solution. If I post the code below, I would really appreciate the help to get this working.

Also, when then the email opens up in Outlook, for some reason it does not have my signature which usually comes up when I open a new email. Is there something I can do to make this happen?

I am using Office 2003.

Thanks for your assistance.

Callum


Public Function SendEmail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String



Set fso = New FileSystemObject
' First, we need to know the subject.
' We can’t very well be sending around blank messages...
'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
'"We Need A Subject Line!")
' If there’s no subject, call it a day.
'If Subjectline$ = "" Then
'MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "E-Mail Merger"
'Exit Function
'End If
' Now we need to put something in our letter...
'
'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
'"We Need A Body!")
' If there’s nothing to say, call it a day.
'If BodyFile$ = "" Then
'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ain’t Got No-Body!"
'Exit Function
'End If

' Check to make sure the file exists...
'If fso.FileExists(BodyFile$) = False Then
'MsgBox "The body file isn’t where you say it is. " & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ain’t Got No-Body!"
'Exit Function
'
'End If
' Since we got a file, we can open it up.
'Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
' and read it into a variable.
'MyBodyText = MyBody.ReadAll
' and close the file.
'MyBody.Close


' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections

Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")


' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' adding them to e-mails and sending them.
Do Until MailList.EOF

' This addresses it
MyMail.Recipients.Add MailList("email")














' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
MailList.MoveNext
Loop





'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
MyMail.Body = MyBodyText
'If you want to send an attachment
'uncomment the following line
'MyMail.Attachments.Add "c:\myfile.txt", olByValue, 1, "My Displayname"
' To briefly describe:
' "c:\myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don’t want the attachment’s icon string to be "c:\myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"
'This sends it!
'MyMail.Send
'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.
MyMail.Display
'And on to the next one...



'Cleanup after ourselves
Set MyMail = Nothing
'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.
'MyOutlook.Quit
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
 
When you add the recipients email address, you will also need to tell it the type:

Try :

Code:
' This addresses it
MyMail.Recipients.Add MailList("email")
' add the following line to change the type to BCC
MyMail.Recipients.Type = olBCC   ' olBCC = 3


Also See:
http://support.microsoft.com/kb/823917
 
Thanks for replying. I have tried your suggestion and also the code in the microsoft article and neither seem to work. Keeps coming back with a runtime error.

Any other ideas?

Thanks for your help.



When you add the recipients email address, you will also need to tell it the type:

Try :

Code:
' This addresses it
MyMail.Recipients.Add MailList("email")
' add the following line to change the type to BCC
MyMail.Recipients.Type = olBCC   ' olBCC = 3


Also See:
http://support.microsoft.com/kb/823917
 
What is the error?

What line of code generates the error?

If it was the line that I had you add, then try:

Code:
MyMail.Recipients.Type = 3   ' olBCC = 3
 
Hi

It was with that bit of code.

I have tried the new bit also and it still comes up with an error on that line.

The error is a Run-time error '438':
Object doesn't support this property or method.

Thanks again for your assistance. It has been driving me crazy for 3 days.






What is the error?

What line of code generates the error?

If it was the line that I had you add, then try:

Code:
MyMail.Recipients.Type = 3   ' olBCC = 3
 
Last edited:
I've never tried this, but I'm guessing you need to build a string of CCs or BCCs. The string, when built, would probably look like this:

Code:
MyMail.BCC = "user1@yahoo.com; [EMAIL="user2@yahoo.com"]user2@yahoo.com[/EMAIL]; [EMAIL="user3@yahoo.com"]user3@yahoo.com[/EMAIL]; "
or maybe this:


Code:
MyMail.BCC = "John Smith< [EMAIL="JS@yahoo.com"]JS@yahoo.com[/EMAIL]>; Mike Peters <[EMAIL="MP@yahoo.com"]MP[EMAIL="MP@yahoo.com"]@yahoo.com[/EMAIL][/EMAIL]>; Tom Candy <TC@yahoo.com>"
 
Hi

This does seem to enable putting addresses into the BCC field but only if I type them in as you have done. Is there a way that I can put in all the email addresses in a similar way that the previous line does:

MyMail.Recipients.Add MailList("email")

Then I could get rid of this line and just have the BCC line.

Thanks



I've never tried this, but I'm guessing you need to build a string of CCs or BCCs. The string, when built, would probably look like this:

Code:
MyMail.BCC = "user1@yahoo.com; [EMAIL="user2@yahoo.com"]user2@yahoo.com[/EMAIL]; [EMAIL="user3@yahoo.com"]user3@yahoo.com[/EMAIL]; "
or maybe this:


Code:
MyMail.BCC = "John Smith< [EMAIL="JS@yahoo.com"]JS@yahoo.com[/EMAIL]>; Mike Peters <[EMAIL="MP@yahoo.com"]MP[EMAIL="MP@yahoo.com"]@yahoo.com[/EMAIL][/EMAIL]>; Tom Candy <TC@yahoo.com>"
 
OK. Have managed to get the first email address in my email by using

MyMail.BCC = strMailList

after amending the code to look like this however, it only puts in the first emai address even though it appears to be looping through them all.

Any ideas?

Code:
' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections

Set db = CurrentDb()
Set MailLIst = db.OpenRecordset("MyEmailAddresses")
strMailList = strMailList & MailLIst.Fields("email") & ";"


' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' adding them to e-mails and sending them.
Do Until MailLIst.EOF


' This addresses it

MyMail.BCC = strMailList






' now, this is the meat and potatoes.
' this is where we loop through our list of addresses,
MailLIst.MoveNext
Loop




I've never tried this, but I'm guessing you need to build a string of CCs or BCCs. The string, when built, would probably look like this:

Code:
MyMail.BCC = "user1@yahoo.com; [EMAIL="user2@yahoo.com"]user2@yahoo.com[/EMAIL]; [EMAIL="user3@yahoo.com"]user3@yahoo.com[/EMAIL]; "
or maybe this:


Code:
MyMail.BCC = "John Smith< [EMAIL="JS@yahoo.com"]JS@yahoo.com[/EMAIL]>; Mike Peters <[EMAIL="MP@yahoo.com"]MP[EMAIL="MP@yahoo.com"]@yahoo.com[/EMAIL][/EMAIL]>; Tom Candy <TC@yahoo.com>"
 
If you put all the addresses in the BCC field, who is going to be in the TO field?

My advice:
(A) for those recipients in the TO field, use MyMail.Recipients.Add
(B) You might need to build your string INSIDE the loop. In other words move this line:


strMailList = strMailList & MailLIst.Fields("email") & ";"


to a position inside the loop.
 
Brilliant

That solved it straight away. The only issue I have now is that when the email opens in Outlook, it is blank and does not have my signature which is usually there when I open a new email.





If you put all the addresses in the BCC field, who is going to be in the TO field?

My advice:
(A) for those recipients in the TO field, use MyMail.Recipients.Add
(B) You might need to build your string INSIDE the loop. In other words move this line:


strMailList = strMailList & MailLIst.Fields("email") & ";"


to a position inside the loop.
 
Sorry, I'm not familiar with the Outlook object model. You're now asking about email signatures, which sounds far beyond my experience - the most I could manage is sending a simple email. Maybe you should start another thread for that one.
 
OK. Thanks for all your help. Very much appreciated.



Sorry, I'm not familiar with the Outlook object model. You're now asking about email signatures, which sounds far beyond my experience - the most I could manage is sending a simple email. Maybe you should start another thread for that one.
 
I generally use the following,
============================================
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecipTo = .Recipients.Add(whoever or no-one)
objOutlookRecipTo.Type = olTo
objOutlookRecipTo.Resolve
Set objOutlookRecipCC = .Recipients.Add(whoever or no-one)
objOutlookRecipCC.Type = olCC
objOutlookRecipCC.Type.Resolve
============================================
so it must be adaptable to BCC:

Set objOutlookRecipBCC = .Recipients.Add(whoever or no-one)
objOutlookRecipBCC.Type = olBCC
objOutlookRecipBCC.Resolve
 

Users who are viewing this thread

Back
Top Bottom