VBA email string not getting to recipients

[Ksandra2901]

Hi All,

Long time lurker first time poster. Self taught and reliant on other clever people like you fabulous lot!

I'm just interested to see if there is a solution to a small problem I am having.

I have code that creates an email to send to multiple recipients, stringing the email addresses together from a list on a sheet. The code works fine and fills in the email addresses. The problem I am having is it asks for access to the address book (which is fine, security and all) but I am finding that some people are not getting the emails.

I think it is either users are not waiting long enough for the address book to resolve the addresses before sending (I have tried adding .resolve to the code but that hasn't helped) or I've missed some vital step to prevent it happening. Has anyone else encountered this? The recipient list is quite long, could that be a factor?

Here is the code:

On Error Resume Next
With OutMail
.To = Join(Application.Transpose(Worksheets("EmailList").Range("B2", Worksheets("EmailList").Range("B" & Worksheets("EmailList").Rows.Count).End(xlUp)).Value), ";")
.CC = Join(Application.Transpose(Worksheets("EmailList").Range("D2", Worksheets("EmailList").Range("D" & Worksheets("EmailList").Rows.Count).End(xlUp)).Value), ";")
.BCC = ""
.Subject = "Report Name - " & Format(Now, "DD/MM/YYYY")
.HTMLbody = Str1 & Str2 & RangetoHTML(Rng1) & Str3 & RangetoHTML(Rng2) & Str4 & RangetoHTML(Rng3) & signature
.display
End With
On Error GoTo 0

Interested to hear your thoughts?

Thanks

 

[Ksandra2901]

Hi All,

Long time lurker first time poster. Self taught and reliant on other clever people like you fabulous lot!

I'm just interested to see if there is a solution to a small problem I am having.

I have code that creates an email to send to multiple recipients, stringing the email addresses together from a list on a sheet. The code works fine and fills in the email addresses. The problem I am having is it asks for access to the address book (which is fine, security and all) but I am finding that some people are not getting the emails.

I think it is either users are not waiting long enough for the address book to resolve the addresses before sending (I have tried adding .resolve to the code but that hasn't helped) or I've missed some vital step to prevent it happening. Has anyone else encountered this? The recipient list is quite long, could that be a factor?

Here is the code:

On Error Resume Next
With OutMail
.To = Join(Application.Transpose(Worksheets("EmailList").Range("B2", Worksheets("EmailList").Range("B" & Worksheets("EmailList").Rows.Count).End(xlUp)).Value), ";")
.CC = Join(Application.Transpose(Worksheets("EmailList").Range("D2", Worksheets("EmailList").Range("D" & Worksheets("EmailList").Rows.Count).End(xlUp)).Value), ";")
.BCC = ""
.Subject = "Report Name - " & Format(Now, "DD/MM/YYYY")
.HTMLbody = Str1 & Str2 & RangetoHTML(Rng1) & Str3 & RangetoHTML(Rng2) & Str4 & RangetoHTML(Rng3) & signature
.display
End With
On Error GoTo 0

Interested to hear your thoughts?

Thanks

Whoops, forget to wrap the code. Newbie error!

 

[Alex Blakenburg]

I am not the best person to help on the code but its likely that the organisation has set a limit on the number of recipients that can be included in a single email. I believe this is the sum of the To, cc & Bcc list.
My expectation would be the the list truncates, so you may be able to check whether that is the case by looking at a users sent email.

 

[Ksandra2901]

I am not the best person to help on the code but its likely that the organisation has set a limit on the number of recipients that can be included in a single email. I believe this is the sum of the To, cc & Bcc list.
My expectation would be the the list truncates, so you may be able to check whether that is the case by looking at a users sent email.

Could be I guess, but prior to my clever bit of code we were sending to that many people anyway and it was going fine. So I don't think its a restriction by the organisation.