Access/VBA - Sending Email Problem

[captainmorgan]

Below is the code I'm trying to use to create a blank email with the bcc list populated from a query.

(1) I have tested the codes output of the "rcpts" field, and counted it as well - OK
(2) I've removed the "rcpts" field from the bcc option of the SendObject tag - OK
(3) I tried inserting the DoCmd.SendObject line into the loop and used the .Field("EmailName") as bcc - it worked OK

I need a single blank email, with all the rcpts addresses in the bcc field.
The code works great, until I insert the rcpts name in the bcc option. Am I using the wrong syntax to call the rcpts into the SendObject tag? Or is there a limit to the size of the bcc field for the SendObject tag? Please Help!

Thanks

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rcpts As String
Dim rcptscount As Integer
 
 
Set db = CurrentDb
Set rst = db.OpenRecordset("EmailInfo")
rcpts = ""
rcptscount = "0"
 
With rst
    .MoveFirst
    Do Until .EOF
        rcpts = rcpts & .Fields("EmailName") & ";"
        rcptscount = rcptscount + 1
        .MoveNext
    Loop
End With
 
Debug.Print rcpts
Debug.Print rcptscount
 
DoCmd.SendObject , , , "[e-mail]", , rcpts, "Subject Text Here", "Message Text Inserted Right Here - Or Leave Blank", True
 
rst.Close

[Denburt]

Look in the immediate window and check this debug statement something must be fishy with you email addresses or something. Maybe you can post it with dummy addresses so we can see what is going on.

[Killer42]

...I need a single blank email, with all the rcpts addresses in the bcc field. The code works great, until I insert the rcpts name in the bcc option. Am I using the wrong syntax to call the rcpts into the SendObject tag? Or is there a limit to the size of the bcc field for the SendObject tag?

I'm not aware of any limit, and in fact haven't used the SendObject command. However, while Access does apply string-length limits in macros (I've run into that one) the suggested solution is to use VBA where the limits don't apply.

Could you give us a little more detail on the way in which it isn't working? Are you getting an error? Does it appear to work, but the mail never arrives? Etc.

But as far as the circumstances whichi stop it working - it's simply the use of multiple addresses, is that correct? Have you tried isolating the SendObject without the rest of your code, and just testing different values in Bcc?

Have you tried using your list delimiter (probably a comma) rather than semicolon, as mentioned in the online help for SendObject? Shouldn't be necessary, but may be worth a try.

By the way, I'm going to move this to the Access forum, as we should benefit from the weight of Access experience over there.

[captainmorgan]

I've been playing with the code, and I have it working as far as sending the first 255 characters of the BCC field. Now, I created a form with a MEMO field called BCC, which I have successfully transfered all the email addresses to, except again, they do not transfer to the Outlook Email.

Aaaahhhhh! I'm losing my mind over this. Is there better code than the SendObject, which will avoid the 255 character limitation?

[Killer42]

I've been playing with the code, and I have it working as far as sending the first 255 characters of the BCC field. Now, I created a form with a MEMO field called BCC, which I have successfully transfered all the email addresses to, except again, they do not transfer to the Outlook Email.

Aaaahhhhh! I'm losing my mind over this. Is there better code than the SendObject, which will avoid the 255 character limitation?

I don't know, but hopefully someone else here might.

In the meantime, could you work around the problem in some way? For example, could you create a distribution list in Outlook and use that in the Bcc field instead of the individual addresses?

Or send the e-mail as many times as necessary to use up all of the addresses, in chunks of up to 255 characters? This would probably at least be an improvement over one e-mail per address.

[ttamilvanan81]

Look in the immediate window and check this debug statement something must be fishy with you email addresses or something. Maybe you can post it with dummy addresses so we can see what is going on.

Hai. I need to Send and Receive Email using Ms-Access and OutLook.
Now am using the following code for Send Email.

Private Sub Command20_Click()
 
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.Email_Address
 
.CC = Me.Cc_Address
.BCC = Me.Bcc_Address
 
.Subject = Me.Mess_Subject
.HTMLBody = Me.Mess_Text
 
If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
 
.Send
 
End With
 
Exit Sub
 
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & 
 
Err.Description
Resume Error_out
Error_out:
 
End Sub
 

This code, the attachment was working as, we type the location of the path manually. I need the Browse button to browse and select the file, the selected file path was stored in a text box.

And Also i need to Receive Email using Ms-Access and OutLook.

Can Anybody know this, please let me know.

Thanks
Tamilvanan

[NeoPa]

You didn't post the results of your Debug.Print rcpts. I suspect it was very long, so no problem.
I would guess that it had a trailing ';' character though, which may (or may not) cause a problem. It wouldn't in the Outlook client, but who knows what it may fall over within VBA code.
Otherwise, I'd be very surprised if the length limit to a BCC parameter were as low as 255 chars.

[Denburt]

I need a single blank email, with all the rcpts addresses in the bcc field.
The code works great, until I insert the rcpts name in the bcc option. Am I using the wrong syntax to call the rcpts into the SendObject tag? Or is there a limit to the size of the bcc field for the SendObject tag? Please Help!

I am curious about why you want a blank email but that is a topic for later discussion. Are you getting any kind of error, if so what does it say? It would be nice to know the error number and description if you are getting one.

I am thinking you are possibly having an issue with one of the addresses. I know that I had a way of verifying email addresses but it is something I can't locate at this time. I just checked the help for sendobject and I noticed the following:

If the mail application can't identify the recipients' names, the message isn't sent and an error occurs.

I am wondering if you are on an exchange server or are you sending them through your local outlook client.

If you are on an exchange server take a look at this:
Exchange Emails

Or this:

Exchange

I did some research and someone else was complainging about a possible 255 character limit. I find this hard to believe but hey, could be. I like Killer's idea of "create a distribution list in Outlook" That sounds like a sound idea. I didn't see any hard documentation on a character limit but it doesn't surprise me. However if you are looking to possibly try another method then there are several at the following link. I personally like CDO and there are many examples of that out on the web.
Here are a couple of Microsoft Access Examples from Microsoft. I would stay away from the Access Basic (used in MS Access 2.0).

Email Examples

Hope some of this helps.