469,328 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

Access/VBA - Sending Email Problem

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

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim rcpts As String
  4. Dim rcptscount As Integer
  5.  
  6.  
  7. Set db = CurrentDb
  8. Set rst = db.OpenRecordset("EmailInfo")
  9. rcpts = ""
  10. rcptscount = "0"
  11.  
  12. With rst
  13.     .MoveFirst
  14.     Do Until .EOF
  15.         rcpts = rcpts & .Fields("EmailName") & ";"
  16.         rcptscount = rcptscount + 1
  17.         .MoveNext
  18.     Loop
  19. End With
  20.  
  21. Debug.Print rcpts
  22. Debug.Print rcptscount
  23.  
  24. DoCmd.SendObject , , , "email@server.com", , rcpts, "Subject Text Here", "Message Text Inserted Right Here - Or Leave Blank", True
  25.  
  26. rst.Close
Mar 31 '07 #1
7 9149
Denburt
1,356 Expert 1GB
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.
Mar 31 '07 #2
Killer42
8,435 Expert 8TB
...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.
Apr 1 '07 #3
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?
Apr 2 '07 #4
Killer42
8,435 Expert 8TB
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.
Apr 2 '07 #5
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2.  
  3. Dim mess_body As String
  4. Dim appOutLook As Outlook.Application
  5. Dim MailOutLook As Outlook.MailItem
  6. Set appOutLook = CreateObject("Outlook.Application")
  7. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  8.  
  9. Set appOutLook = CreateObject("Outlook.Application")
  10. Set MailOutLook = appOutLook.CreateItem(olMailItem)
  11.  
  12. With MailOutLook
  13. .BodyFormat = olFormatRichText
  14. .To = Me.Email_Address
  15.  
  16. .CC = Me.Cc_Address
  17. .BCC = Me.Bcc_Address
  18.  
  19. .Subject = Me.Mess_Subject
  20. .HTMLBody = Me.Mess_Text
  21.  
  22. If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
  23. .Attachments.Add (Me.Mail_Attachment_Path)
  24. End If
  25.  
  26. .Send
  27.  
  28. End With
  29.  
  30. Exit Sub
  31.  
  32. email_error:
  33. MsgBox "An error was encountered." & vbCrLf & "The error message is: " & 
  34.  
  35. Err.Description
  36. Resume Error_out
  37. Error_out:
  38.  
  39. End Sub
  40.  
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
Apr 2 '07 #6
NeoPa
32,181 Expert Mod 16PB
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.
Apr 2 '07 #7
Denburt
1,356 Expert 1GB
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.
Apr 2 '07 #8

Post your reply

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

Similar topics

13 posts views Thread by Guy Hocking | last post: by
2 posts views Thread by Tom Dauria | last post: by
1 post views Thread by gemma.gill | last post: by
16 posts views Thread by Ben Sehara | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.