VBA email string not getting to recipients

Ksandra2901

New Member
Joined
Jan 3, 2012
Messages
6
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Ksandra2901

New Member
Joined
Jan 3, 2012
Messages
6
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

Well-known Member
Joined
Feb 23, 2021
Messages
630
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 3, 2012
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,961
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top