Access 2003 SendObject Macro 255 Character Limit

[LEhrler]

Hi
I have just completed the devlopment of a helpful communications set-up
using Access, but the final key step is being able to place long email
address strings into the Bcc box in MS Outlook using the Access SendObject
Macro.

The Bcc field in SendObject (as are all its fields) is limited to 255
characters which results in my string of email addresses being truncated,
making this great facility of little practical value. While table fields can
be enlarged by switching their formats from Text to Memo, this option does
not appear to be available for SendObject.

I am surprised Microsoft has not yet addressed this major limitation to what
could be a powerful communications tool in these times where large groups of
email addresses are common. Fixing this would remove the need to duplicate
email addresses in Outlook Contact Lists - a more cumbersome process when
updating email addresses in Access.

How do I solve this problem? The solution would be of much benefit to the
various community organisations I am involved with outside of my work. I
would be delighted to have this key problem solved for me.

 

[Linq Adams via AccessMonster.com]

I am surprised Microsoft has not yet addressed this major limitation >to what could be a powerful communications tool...

That's the point, really! MS Access is ***not*** a communications tool, it's
an RDBM tool! Expecting it to be a full communications tool is no different
than expecting to be able to manage a database using MS Paint! Using a hammer
and screwdriver to cut lumber is always going to be a disappointing
experience!

 

[Damon Heron]

Hmmmm.... I have Access 2007, and have no problem with strings in the BCC
field with sendobject. Are you using a variable to store the email
addresses, with a ";" between each, and using the variable in the command?

Damon

 

[Albert D. Kallal]

How do I solve this problem? The solution would be of much benefit to the
various community organisations I am involved with outside of my work. I
would be delighted to have this key problem solved for me.
--

It is a trival and easy problem to solve.

Just simply use object automtaotn, and not use send object.

Furhtmore, you can also do things like generate *several* reports, and have
MULTPLE attachments. In other words, don't use sendobject, but use object
automaton. You will thus remove most, if not all the limtations of
sendobject.

Furhtmore, I ALWAYS now send my reprots as pdf files as that perseres
formtting (again, another thing you you should consider -- those rtf reports
dont look that great).

So, here what the automation code looks like:

Public Sub MySendObject(strSubject As String, _
strMsgText As String, _
strEmailTo As String, _
strDocName As String)


' send to user via email
Dim ol As Object ' Late binding 10/03/2001 -
Ak
Dim ns As Object ' Late bind
Dim newmessage As Object ' Late bind
Dim mymessage As String

Set ol = GetObject(, "Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
ns.Logon
Set newmessage = ol.CreateItem(0) ' 0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
If strDocName <> "" Then
.Attachments.Add (strDocName)
End If
.Display
'.Send
End With
End Sub


You could take the above code, and expand it with stephans pdf creater. You
get:

Public Sub EmailReport(strReportName As String, _
strSubject As String, _
strMsgText As String, _
strDocName As String, _
strEmailTo As String)


' sends the active report out....
' send to user via email

Dim MyReport As Report
Dim ol As Object ' Late binding 10/03/2001 -
Ak
Dim ns As Object ' Late bind
Dim newmessage As Object ' Late bind
Dim mymessage As String

'DoCmd.OutputTo acReport, strReportName, acFormatRTF, strDocName, False
Call ConvertReportToPDF(strReportName, , strDocName, False, False)

DoCmd.Close acReport, strReportName

On Error GoTo CreateOutLookApp
Set ol = GetObject(, "Outlook.Application")
On Error Resume Next
Set ns = ol.GetNamespace("MAPI")
ns.Logon

Set newmessage = ol.CreateItem(0) ' 0 = olMainItem
With newmessage
.Recipients.Add strEmailTo
.Subject = strSubject
.Body = strMsgText
.Attachments.Add (strDocName)
.Display
' .Send
End With

Exit Sub

CreateOutLookApp:

Set ol = CreateObject("Outlook.application")
Resume Next

End Sub

You can find sthpans pdf creater here:

http://www.lebans.com/reporttopdf.htm

So, here what the above approch solves:

solves you text msg limits
adds pdf ability to your software
solves the problem of emailing reports with formatting by using pdfs
allows you to have 1, 2 or even more attachments such as pictures etc in
one email, and you can have MORE then one attachment.

I could likely type on more, but I VERY long time ago stopped using
sendobject, and if you hit the limits of sendobject, don't wine about it,
simply use object automaton..

 

[Albert D. Kallal]

The Bcc field in SendObject (as are all its fields) is limited to 255

characters which results in my string of email addresses being truncated,
making this great facility of little practical value. While table fields
can
be enlarged by switching their formats from Text to Memo, this option does
not appear to be available for SendObject.

Actually, strings that are far longer then 255 chars do fit just fine into
the bcc field.
the
I just tested the folliwng code:

Dim strBB As String
Dim i As Long

For i = 1 To 255
If strBB <> "" Then strBB = strBB & ";"
strBB = strBB & "kallal" & i & "@msn.com"
Next i

MsgBox Len(strBB)

DoCmd.SendObject acSendReport, "r1", acFormatRTF, , , strBB, "test
subject", "msg text", True

The above string produced for the email is over 4000 characters long, and it
inserted into the bcc field no problem....

At this point I actually have no idea as to what the limit or number of
characters that can be inserted in the bcc, but I can certainly tell you
right now that 255 is NOT the limit.

The limitation you are experiencing is a result of your coding practices, or
some other issue.

I am surprised Microsoft has not yet addressed this major limitation to
what
could be a powerful communications tool in these times where large groups
of
email addresses are common. Fixing this would remove the need to
duplicate
email addresses in Outlook Contact Lists - a more cumbersome process when
updating email addresses in Access.

The limitation you speak of actually does not exist, and unfortunately you
sent me on a wild goose chase. (not a big deal, I just be less trusting of
people's information next time that's all).

Regardless, we now see that you can use object automation as I suggested,
and there still a considerable number of benefits to using object
automation. However if you wish to use sendobject, it seems very well that
you can also use strings longer then the 255 character limit you speak of.

Do note that any contorl that has formatting on it, generally does get
limited to 255 characters, but I see no reason why you can't use a memo
field to store this resulting information. Furthermore as a general rule I
would the assume you have a table with a collum of e-mail addresses.

The following code snippet shows how to pull the names into one long string,
and then insert it using sendobject.

eg:


dim rstEmailNames as dao.RecordSet
dim strEmailNames as string
dim strSql as string

strSql = "select emailName from tblcustomers where emailname is not null"
set rstEmailName = currentdb.OpenrecordSet(strSql)

do while rstEmailNames.Eof = false
if strEmailnames <> "" then
strEmailNames = strEmailNames & ";"
end if
strEmailnames = strEmailNames & rstEmailNames!Emailname
rstEmailNames.MoveNext
loop
rstEmailNames.Close

DoCmd.SendObject acSendReport, "r1", acFormatRTF, , , strEmailNames,
"test subject", "msg text", True

Once again the above piece of code shows there is no 255 character limit.

You'll will have to be more forthcoming in your code, or how your insert the
names into the bcc field, but as it stands right now there's no such 255
char limit I can find here....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)




you should be able to use a memo field, and not have this limit

 

[LEhrler]

Hi Albert

Thank you for your full coverage of my options including some additional
helpful points. I am satisfied you have provided the solution to my genuine
enquiry. The reason I say it in this way is that I have so far been quite
successful in configuring various Microsoft applications as a front-end user
– utilising the existing options already available in each application –
although that does include having a general knowledge of how to use formulae
and strings. It is clear I now need to learn how to use Visual Basic and am
now embarking on that course. Then I shall know how to apply the helpful
information you have given. Your time spent in supplying this information
for me is appreciated. Regards from Lou

 

[LEhrler]

Hi Damon

Thanks for your valid comment Damon. I suspected such an option might exist
in Access 2007, but am also mindful of the users of my system having older
versions of MS Office. Also there is no general need for me to upgrade to
Office 2007 at this time.

I have been using the variable in my address string as mentioned, but this
seems to have had no effect on the SendObject field size limit. In view of
other information which has come to hand, it is clear I need to become more
familiar with Visual basic to resolve this matter. Regards from Lou.

 

[LEhrler]

Hi Ling
An unexpected, but no doubt valid perspective (you don’t work for Microsoft
do you?). My Access application, however, is already performing with ease as
a mass communication system and doesn’t seem to be aware it is in the
“screwdriver and lumber categoryâ€. In fact it is doing it so well that I
realised I could load it up with even more demands. The only sticking point
is the bottle-neck with the field size in the SendObject (which I am
satisfied has now been resolved).

I still consider my original position not unreasonable - after all, if
provision was made to expand other Access fields and if provision was made to
send emails, why suddenly stop at the limitation at the SendObject point? A
small thing with a big impact on performance.

I agree with your many ways of skinning a cat principle – my Access mass
communication system is currently functioning with my duplicating my Access
email Distribution lists in Outlook – resulting in Outlook recognising the
identical Distribution List name in the respective SendObject which in turn
triggers the Distribution List contained in Outlook. My aim is to operate
solely with everything in Access and I believe I now have the solution using
Visual Basic. Regards from Lou.