Limited number of characters per line of code problem

[aliendan]

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 & ! & ";"
.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

 

[strongm]

> 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?

 

[combo]

You might reached number of recipients in message, see

https://answers.microsoft.com/en-us...-and-bcc/cdc434b0-c101-4377-be1f-15a15079372e

or

https://www.msoutlook.info/question/198

combo

 

[aliendan]

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

 

[combo]

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

 

[aliendan]

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
[highlight #FCE94F] .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;”[/highlight]

.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 & ! & ";"
.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)[COLOR=#FCE94F][/color]
With mail
[highlight #FCE94F].To = strEmail[/highlight]

.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

 

[strongm]

This

[blue]On Error Resume Next
...
On Error GoTo Err_Finally
...
If Err.Number <> 0 Then[/blue]

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

 

[combo]

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

combo

 

[combo]

I had no problem with your code in:

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 [tt]Set out = CreateObject("Outlook.Application")[/tt] was reported (Activex component can't create object).

combo