473,372 Members | 1,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 9309
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,556 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

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

Similar topics

13
by: Guy Hocking | last post by:
Hi there, i am creating a database driven login page. I am using code that has been successful in the past, however when i upload it to our web server it displys the following message in the...
10
by: MLH | last post by:
I print to a device that creates a PDF. Knowing the filename, how can I then embed the PDF into the body text of an OutLook Express outbound email & send to a specified address in a table? I want...
1
by: Atim | last post by:
I'm trying to create an email message in Novell Groupwise (from Access) which the user can edit before sending. Using the code below, I can create a message just fine, but the problem is that it...
2
by: Tom Dauria | last post by:
I have a Access database application that sends email through Outlook. A few years ago we started having a problem where it would ask for each and every email being sent whether you want to give...
3
by: Strasser | last post by:
In Access2000 mass emailing worked perfectly (very powerful tool!). Doesn't work when using XP version of both Access and Outlook, even though I checked the box to ensure that I was sending the...
1
by: gemma.gill | last post by:
Hi There, I have a button on a form within access that sends a verification e- mail. My problem is that these e-mails are sending from individual user accounts rather than a genieric mailbox. ...
14
by: ARC | last post by:
Hello All, I have a major application for quoting and invoicing that is written in Access 97 (both front and back ends). I have a user base of over 300 customers, and currently I don't require...
16
by: Ben Sehara | last post by:
Is there any way I can limit the access to my website? I have a site "A" and I want to allow access to it only from site "B" login user. If someone try to access site "A" directory, I want it...
9
by: JoeP | last post by:
Hi All, How can I find the reason for such an error: Failure sending mail. Some Code... oMailMessage.IsBodyHtml = False oMailMessage.Body = cEmailBody Dim oSMTP As New SmtpClient...
28
tdw
by: tdw | last post by:
Hi all, I am trying to add a feature to our database that automates sending emails. When entering a new order, I want the option to send an email to the company the order came from, attaching a...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.