CODEWITHSUNDEEP
vbaexcelemailoutlook
rhaskett
rhaskett

Reputation: 1962

How to Send All Emails From Outlook Outbox in VBA

I'm using Excel VBA to stage emails in Outlook and it is working well.

Dim template As Outlook.MailItem, tomerge As Outlook.MailItem
' Create E-mail
tomerge.Close olSave

The e-mails can then be manually moved to the Drafts folder and sent using this Sub.

'Loop through items in Drafts folder
objDrafts.Item(i).Send

However, many users have a bunch of extra drafts in their Drafts folder that they don't want sent.

If I replace "olFolderDrafts" with "olFolderOutbox" and try to send from their Outbox. The first message sends and then I get a "Run-time error" "Outlook has already begun transmitting this message".

Is there some way to send all from the Outbox or even better is there someway to stage and send from a newly created folder?

Upvotes: 0

Views: 3170

Answers (2)

rhaskett
rhaskett

Reputation: 1962

This answer was inspired by Nagarajan's comment above, but there are quite a few changes necessary from the answer in Send/Receive in Outlook Via Code. The main problem is using olSave doesn't put the messages in a "ready to send" state in Outlook so starting a sync using syc.Start from the answer above does nothing.

Instead, we found the following process to be straight forward:

  1. Put Outlook in offline mode using "Send/Recieve" -> "Work Offline"
  2. Use Excel VBA to stage the e-mails and instead of saving just .Send each email. As Outlook is offline they will be staged and ready to be sent but not actually sent.
  3. The e-mails should now be staged in Outlook's Outbox folder and can be reviewed just be sure to press the "send" button after reviewing/editing messages otherwise the e-mail you modified will be removed from the queue.
  4. When the messages are ready to be sent put Outlook back in online mode and they will be sent automatically.

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

You need to create a folder for your unsent items and process them separately. As a rule the Outbox folder contains already submitted items. So, that's not a right place for your items.

The Outlook Object Model provides the Add function of the Folders class. You can get an instance of the Folders class using the Folders property of the Folder class in Outlook. You can read more about that in the How To: Create a new folder in Outlook article.

Upvotes: 0

Related Questions