Solved

Outlook vba to send draft emails

Posted on 2016-09-09
14
44 Views
Last Modified: 2016-10-29
Is there and way to use vba to send x amount (say 50) emails from the drafts folder, at an x time interval?

I found this here, but....

Sub sendDrafts()

Dim intmai As integer

    with Application.Session.GetDefaultFolder(olFolderDrafts)

        For intmai = .Items.count to 1 step -1

        .Items(intmai).Send

        Next

    end with

End Sub
0
Comment
Question by:Chet Powell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41792297
...but what?

What have you tried to implement the time interval requirement?

PS. You have not closed a previously asked (& abandoned) question:

[ https://www.experts-exchange.com/questions/28964895/Excel-for-Mac-VBA.html ]
0
 

Author Comment

by:Chet Powell
ID: 41792300
I really don't know where to start to try. I asked because I don't know if it can even be done.

Chet
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41792302
How or when does the process of sending start?

Is it a manual request actioned by, say, clicking a button, or does it occur automatically from a fixed time on a particular day/date (or even occur every day, or every second Tuesday in a month)?

Does the sending begin when the Drafts folder has reached a total of fifty items?

Where is the interval between sends set, or dictated?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Chet Powell
ID: 41792303
And.. I think the x amount of emaiotd lies in the items.count line, but not sure what to change it to.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41792304
And.. I think the x amount of emaiotd lies in the items.count line, but not sure what to change it to.

Sorry, what is "emaiotd"?
0
 

Author Comment

by:Chet Powell
ID: 41792310
Starting isn't major issue. Clickin a button would b fine. If i could get it to say, at a particular time.. if it's the se as in Excel, I can get thay. What I want is. Once it starts  it goes though, from the first, send 50 emails, then stops. Then say an hour later, does it again . I just don't want it to send all at once, in a short amount of time. It locks up the computer.

Byw.. thanks for helping.
0
 

Author Comment

by:Chet Powell
ID: 41792311
Emails.. darned autocorrect...
0
 

Author Comment

by:Chet Powell
ID: 41792318
Also wondering if the wait method would work for the interval  but I want it to happen after sending a group of 50
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41792564
So you want a VBA you can start, which then sends 50 mails stored in Drafts, then pauses, then sends the next 50.
The issue is that if you do that with a single, simple VBA, you block Outlook until finished. We can improve that a bit by allowing processing of Outlook's internal tasks while "pausing", but that doesn't work well over an extended period.

However: Outlook has a great feature allowing delayed sending at an individual set up date and time. Since it doesn't seem to be an issue for you to have Outlook running while the send should happen, which is required for delayed send, I suggest we write VBA code to go thru drafts and set that feature time either with continues delay (say 5 seconds for each mail), or in a bulk as originally asked for.
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 total points (awarded by participants)
ID: 41792618
Here is some code to save a TaskItem with information that will send an email when the Reminder event fires.  The first code segment (part of a longer procedure) runs from Access, and the second from the ThisOutlookSession class module:

         'Create task item for sending the mail message later
         Set tsk = pappOutlook.CreateItem(olTaskItem)
         strMessage = "When the task reminder fires, an email message will " _
            & "be created and placed in the Outbox to be sent"
         With tsk
            .Display
            .Subject = strTaskSubject
            .DueDate = dteSend
            .StartDate = dteSend
            .Categories = "Reminder"
            .Body = strMessage
            
            'Store info for mail message in unused Task fields
            .BillingInformation = strToEMail
            .CardData = strMessageSubject
            .Mileage = strBody
            
            'Set task reminder for date when message should be sent
            .ReminderSet = True
            .ReminderTime = dteSend
            '.Display
            .Close (olSave)
         End With

'Procedure in ThisOutlookSession module:

Private Sub Application_Reminder(ByVal Item As Object)
'Created by Helen Feddema 7-Dec-2004
'Last modified by Helen Feddema 30-Sep-2014

On Error GoTo ErrorHandler
   
   Dim msg As Outlook.MailItem
   
   If Item.Categories = "Reminder" Then
      Set msg = Application.CreateItem(olMailItem)
      With msg
         .To = Item.BillingInformation
         .Subject = Item.CardData
         .Body = Item.Mileage
         .BodyFormat = olFormatPlain
         .Save
         .Display
      End With
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 70

Accepted Solution

by:
Qlemo earned 375 total points (awarded by participants)
ID: 41792655
Using the deferred send, as mentioned in my prior comment:
Sub SendDelayed()
Const delay = 5        ' delay for each mail [sec]
Const batchsize = 50   ' maximum number in one batch operation
Const pause = 300      ' added delay between batches [sec]

Dim itm As Object
Dim cnt As Integer
Dim lastdate As Date

  cnt = 0
  lastdate = Now
  For Each itm In Session.GetDefaultFolder(olFolderDrafts).Items
    If itm.Class = olMail Then
      cnt = cnt + 1
      If cnt > batchsize Then
        cnt = 0
        lastdate = DateAdd("s", pause, lastdate)
      Else
        lastdate = DateAdd("s", delay, lastdate)
      End If
      itm.DeferredDeliveryTime = lastdate
      itm.Save
      itm.Send
    End If
    DoEvents
  Next
End Sub

Open in new window

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41856770
Objection: The only complete solution is mine at https:#a41792655.
https:#a41792618 would work with major changes - it shows a concept, but does not solve the majority of the question as-is. It deserves some points for the idea.
0

Featured Post

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question