INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Limited number of characters per line of code problem

Limited number of characters per line of code problem

(OP)
I have an Access 2013 database program that emails a report to 108 email addresses in the company via code in a module. I have been manually editing the .send = in the code to remove or add email addresses, but now I want to put the email addresses in a table and gather them for the .send = code line. I gather the email from a recordset using:

With rst1
.MoveFirst
Do Until .EOF
strEmail = strEmail & ![Email] & ";"
.MoveNext
Loop
End With

So now the .send = strEmail is too long since the number of characters in a line of code is limited in Access and the emails will not send due to the syntax problem of limited characters.

Does anyone know how I can programmatically parse the strEmail into, say, 10 emails per line and add “ & _ to the end of each line – except the last line?


Dan Rogotzke

RE: Limited number of characters per line of code problem

> the number of characters in a line of code

Whilst thiis is true, the length of a string held in a variable is much, much bigger, and does not contribute to line length (beyond the characters in the variable name).

So, given the code that you have shown here there should be no problem with line length.

What is the exact error message that you are getting?

RE: Limited number of characters per line of code problem

(OP)
stronngm, I don't get an error message. The emails just don't get to Outlook and get sent.

combo, before I went to the table with my email addresses I had them hard coded in the .To = line. I had them stated in 10 different lines so that the lines of code weren't too long to deal with and the emails went out as they should, so the number of addresses is not the problem. I had to use 10 lines for the email addresses because while typing them in I was only aloud 836 characters per line. Access would not allow me to type more than that per line.

I must apologize for in my previous post I said the .send = line. I meant to say the .To = line.

Dan Rogotzke

RE: Limited number of characters per line of code problem

Does it work with one recipient? You mix two variables: strEmail and line in your examples, what is the full code that creates and sends email?
"& _" is a way to break long line in vba code, has nothing to do with text processed by the code.

combo

RE: Limited number of characters per line of code problem

(OP)
combo, Here is the format of the code that works:
PLEASE REMEMBER, in the first example it works with the 108 emails I have coded in. In the second example it pulls the 108 emails from a table... it DOES NOT work.

DoCmd.OutputTo acOutputReport, "rptReport", acFormatPDF, "c:\data\rptReport.pdf"

FullPath = "c:\data\rptReport.pdf"

On Error Resume Next

'OlSecurityManager.DisableOOMWarnings = True
On Error GoTo Err_Finally

Set out = GetObject(, "Outlook.Application")

If Err.Number <> 0 Then
Dim val As Variant
val = Err.Number
Set out = CreateObject("Outlook.Application")
End If
Set mapi = out.GetNamespace("MAPI")
Set mail = out.CreateItem(0)
With mail
.To =” Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;” & _
Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;” & _
Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;” & _
Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;Name@email.com;”


.Subject = "Rig List"
.body = "Please see the attachment."
.Attachments.Add (FullPath)
' .Display
.Send
End With

Here is the format of the code that DOES NOT work:

Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.CursorType = adOpenStatic
rst1.LockType = adLockOptimistic
rst1.Open "SELECT tblEmailList.Email" & _
" FROM tblEmailList;"

With rst1
.MoveFirst
Do Until .EOF
strEmail = strEmail & ![Email] & ";"
.MoveNext
Loop
End With

DoCmd.OutputTo acOutputReport, "rptReport", acFormatPDF, "c:\data\rptReport.pdf"

FullPath = "c:\data\rptReport.pdf"

On Error Resume Next

'OlSecurityManager.DisableOOMWarnings = True
On Error GoTo Err_Finally

Set out = GetObject(, "Outlook.Application")

If Err.Number <> 0 Then
Dim val As Variant
val = Err.Number
Set out = CreateObject("Outlook.Application")
End If
Set mapi = out.GetNamespace("MAPI")
Set mail = out.CreateItem(0)
With mail
.To = strEmail

.Subject = "Rig List"
.body = "Please see the attachment."
.Attachments.Add (FullPath)
' .Display
.Send
End With
It looks like the indentations go away in the preview... hmmmmm.

Thanks,
Dan

Dan Rogotzke

RE: Limited number of characters per line of code problem



This


CODE

On Error Resume Next
...
On Error GoTo Err_Finally
...
If Err.Number <> 0 Then 

might explain both the fact that you don't get an error message AND why your code doesn't work ...

RE: Limited number of characters per line of code problem

How have you dimmed strEmail, what is its initial value, what is [Email] field type and what is strEmail value after recordset looping?

combo

RE: Limited number of characters per line of code problem

I had no problem with your code in:

CODE -->

Dim out
For i = 1 To 1000
    strEmail = strEmail & "xxx&yyyy.zzz;"
Next

Set out = GetObject(, "Outlook.Application")

If Err.Number <> 0 Then
Dim val As Variant
val = Err.Number
Set out = CreateObject("Outlook.Application")
End If
Set mapi = out.GetNamespace("MAPI")
Set mail = out.CreateItem(0)
With mail
    .To = strEmail
    
    .Subject = "Rig List"
    .body = "Please see the attachment."
    .Display
End With 
if Outlook was open. Otherwise error 439 in line Set out = CreateObject("Outlook.Application") was reported (Activex component can't create object).

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close