Automated Outlook email in batches (1 Viewer)

[millwheal]

Hi,
I have a procedure to send the same email to a selection of people. The procedure works ... almost. Whilst testing it, using breakpoints, all was well, but when run 'live', only one email message is created. The addressees in it are successively overwritten, so I end up with just the last batch of people. The following is an abridged verison of the code:

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim EmailAdds As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

olMail.Subject = "Subject"
olMail.BodyFormat = olFormatHTML
' Email message text is in G_Email_Text
olMail.Body = G_Email_Text

' Loop to put first 50 email addresses in EmailAdds

olMail.To = "Undisclosed"
olMail.BCC = EmailAdds

olMail.Display

' Loop back for next batch

By way of explanation:
I don't want to try sending all the copies at once, as there are currently over 200 and this number will increase, hence the 'batch' approach.

I use "Display", rather than "Send" because a) I need to amend the account from which the email is sent [it's not the default account and amending the 'SendAddress' within Access isn't allowed] and b) it avoids the warning message you get with "Send".

I did consider using something like Send Personally, which would avoid this problem, but it would fill up my 'Sent Items' folder, and whilst I do want to retain a copy of the email and the addressees, I don't want 200 and more copies of each email.

I've tried 'DoEvents', which I've seen mentioned elsewhere on this forum, but to no avail.

I can get over the problem by putting a MsgBox immediately after the "olMail.Display", and that's what I will do if no-one can offer me a more elegant solution.

Thanks in anticipation!

 

[HiTechCoach]

Since you are displaying the message and not automatically sending it, you will have to make the code stop each time through the loop and wait until you have sent the message before creating the next one. I think this is what you have already discovered. Using a message box to pause the code is probably your best bet.

 

[rapsr59]

I'm not at all knowledgeable about programming email and all that.

In the past I have had problems accomplishing a task because speed would not allow a task to complete before the next iteration in a loop.

My solution was to place a "Timer Loop" inside the main loop and that changed the timing so the task could successfully complete it's job during each iteration.

I used:

[FONT=Times New Roman]While Whatever[/FONT]
[FONT=Times New Roman] DoTheTask[/FONT]
[FONT=Times New Roman] 'Give the task time to complete[/FONT]
[FONT=Times New Roman] For T = 1 to 1000000[/FONT]
[FONT=Times New Roman]     'Wait a while[/FONT]
[FONT=Times New Roman] Next T[/FONT]
 
[FONT=Times New Roman]Wend[/FONT]

Regards,


Richard

 

[millwheal]

Thanks HiTechCoach for the advice.
I'll live with the minor inconvenience of the 'message box' ... until my distribution list gets up towards it's final expected total of 2,000+, at which point I'll have to try increasing the batch size.

millwheal