none
VBA to send an existing email, not create a new one RRS feed

  • Question

  • Outlook 2016 is instructed to not send emails immediately; this is important to us.

    I have an Access vba mail-merge module that creates emails and 'sends' them so they appear in the Outbox.

    My ISP now restricts the number of emails that can be sent in a specified time period.

    I could amend my original email creating program to handle the constraint but that cannot control the user who might reply to inbound emails and write new emails, so that when 'Send/Receive' is clicked, too many emails are sent.

    I wrote VBA code, activated by the user clicking a button on the ribbon, that interrogates the Outbox and iterates through the correct number of items and sends them, then it pauses until enough time has elapsed, then sends another batch.

    My code finds the item but when I assign MailItem to the email it changes its state.  I am unsure how to describe this in technical terms but when viewing the Outbox, when reaching the line 'Set oMail = fldItem' the email changes from a 'send' state shown by italics to a 'non-send' state (not italics) and the item never leaves the Outbox.  When my code reaches the next line 'oMail.Send', the item changes back to italics.

    Can anyone please tell me the correct way to identify an existing email item and send it?

    Private Sub Demo(ByVal numberToSend As Integer)
       Dim oApp As New Outlook.Application
       Dim oNS As Outlook.NameSpace
       Dim oFld As Outlook.MAPIFolder
       Dim oMail As Outlook.MailItem
       Dim fldItem As Object
       Dim idx As Integer
       
       Set oNS = oApp.GetNamespace("MAPI")
       Set oFld = oNS.GetDefaultFolder(olFolderOutbox)
       
       For idx = 1 To numberToSend
          Set fldItem = oFld.Items(idx)
          If fldItem.Class = olMail Then
             Set oMail = fldItem
             oMail.Send
          End If
       Next

    End Sub


    Saturday, June 11, 2016 11:56 PM

Answers

  • Hi Victor,

    Do you want to create an email which is scheduled when to send or create an email and you will check it in Outlook by some logic, if it meets send it again?

    Based on your description, it seems you create email in Access, and want to check it and send in Outlook, am I right? If so, I think you do not need schedule email, you could create an email and save it to drafts, then send emails in drafts in Outlook.

    With your code in Access, use Save instead of send method, and now your email will be saved to drafts in Outlook.

    Here is a simple code to loop emails in Drafts.

    Sub SendEmailInDraft()
    Dim f As Folder
    Set f = Application.Session.GetDefaultFolder(olFolderDrafts)
    Dim e As Outlook.MailItem
    For Each e In f.Items
        If e.Subject = "TestAccess3" Then
            e.Send
        End If
    Next e
    End Sub

    In addition, you could not select any items in Drafts Folder when you run above code in Outlook.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, June 26, 2016 3:20 AM

All replies

  • Hello Victor,

    Do not touch items in the Outbox folder. Such actions can prevent items from sending. 

    Instead, you can place emails to any custom folder and process them later at any convenient time. For example, when users try to send the item in Outlook you can handle the ItemSend event of the Application class which is fired whenever an Microsoft Outlook item is sent, either by the user through an Inspector (before the inspector is closed, but after the user clicks the Send button) or when the Send method for an Outlook item, such as MailItem, is used in a program. So, you can cancel the default action and move the item wherever you need.

    Public WithEvents myOlApp As Outlook.Application 
     
    Public Sub Initialize_handler() 
     Set myOlApp = Outlook.Application 
    End Sub 
     
    Private Sub myOlApp_ItemSend(ByVal Item As Object, Cancel As Boolean) 
     Dim prompt As String 
     prompt = "Are you sure you want to send " & Item.Subject & "?" 
     If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo    Then 
     Cancel = True 
    End If 
    End Sub

    Later you can use your code against items in the custom folder.


    [custom.development]

    Sunday, June 12, 2016 7:19 PM
  • Hi Eugene

    I've tried your code and looked it up on the Office Dev Centre.  On its own it does not seem to do anything different to what was happening before but when I combine it with the solution to another question dated 26/2/2012 from Helveticus titled 'Simulate "Send/Receive" button in vba' I see some progress.

    I am not 'touching' items in the Outbox except to set them back to a state ready for sending (italics) rather than the draft mode they go to.

    I am away for two weeks now so will be unable to continue my efforts.  I will carry on upon my return.

    Thank you for your input.

    Sunday, June 12, 2016 10:32 PM
  • Hi Victor,

    >> I have an Access vba mail-merge module that creates emails and 'sends' them so they appear in the Outbox.

    Could you share us how you create mail with Access vba? Normally, if you create an email and send it, it will in Sent Items instead of Outbox. Do you create the email and set the delay time? If so, I think the emails will not send even though user click “Send/Reveive” until the delivery time.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 13, 2016 7:40 AM
  • Hi Edward

    Firstly to explain I have been away and not able to respond to your request.

    If one creates an item then 'sends' it, the item will go to the Outbox if, as I said in my question, Outlook is told to not send immediately, which is exactly what is wanted so email headers can be viewed before they are sent.

    I show the email creation code below.  This works fine and I do not believe is the issue.

    I am struggling to grasp the concept of the action 'Send'.  Ignoring all code, if one creates a new email manually, once finished the user clicks 'Send' and it then goes from drafts to the Outbox.  There it remains until the ribbon 'Send/Receive' button is clicked.  The code below creates emails that end up in the Outbox.  When the user clicks 'Send/Receive' they are despatched.  I now must control that despatch process by counting them and only despatching a fixed number in a fixed time period.  To do so, I have been using the statement/command 'Send' again but find that disables the email from been able to be sent (it is no longer italicised).  I need a way of sending a batch only.

    Sub CreateEmail(ByVal strCSVPath As String, ByVal strCSV As String, ByVal strDocPath As String, ByVal strDoc As String, Optional ByVal strIssue As String)

        Dim WdApp As Word.Application
       Dim olItem As Outlook.MailItem
       Dim olRecipient As Outlook.Recipient
       Dim wdMaster As Word.Document
       Dim wdMerged As Word.Document

       Dim bytSections As Byte
       Dim x As Byte
       Dim strBCC As String
       Dim strBody As String
       Dim strFrom As String
       Dim strSubject As String
       Dim strTo As String

       On Error GoTo ErrorHandler

       OpenOutlook1

       Set olApp = New Outlook.Application

       ''Create a new instance of Word (hidden)
       Set WdApp = New Word.Application

       If WdApp.Visible = False Then
    ''Unrem the next line if the user is to see the document
          'WdApp.Visible = True
       End If

       Set wdMaster = WdApp.Documents.Open(strDocPath & strDoc)

       With wdMaster.MailMerge
          If .State <> wdMainAndDataSource Then
             .MainDocumentType = wdFormLetters
             .OpenDataSource strCSVPath & strCSV
          End If
          .Destination = wdSendToNewDocument
          .Execute
       End With

       wdMaster.Close SaveChanges:=wdSaveChanges
       Set wdMaster = Nothing

       Set wdMerged = WdApp.ActiveDocument

       With wdMerged
          ''Find out how many messages/sections there are
          bytSections = .Sections.Count
          ''There is always one more section than needed so reduce the count by one
          For x = 1 To bytSections - 1
             .Sections(x).Range.Sentences(1).Select
             .Sections(x).Range.Sentences(1).Copy
             strTo = .Sections(x).Range.Sentences(1)
             Set olItem = olApp.CreateItem(olMailItem)
             Set olRecipient = olItem.Recipients.Add(strTo)
             olRecipient.Type = olTo
             'strBCC = ""
             'Set olRecipient = olItem.Recipients.Add(strBCC)
             'olRecipient.Type = olBCC
             'strBCC = "alan.sherlock36@tiscali.co.uk"
             'Set olRecipient = olItem.Recipients.Add(strBCC)
             'olRecipient.Type = olBCC
             strFrom = "rotasec@bcyc.org.uk"
             .Sections(x).Range.Sentences(1).Delete
             If strIssue = "v1" Then
                strSubject = "BCYC Duty Reminder 1"
             ElseIf strIssue = "v2" Then
                strSubject = "BCYC Duty Reminder 2"
             Else
                MsgBox "An unexpected error occurred in CreateEmail"
             End If
             olItem.Subject = strSubject
             .Sections(x).Range.Select
             .Sections(x).Range.Copy
             strBody = .Sections(x).Range
             olItem.Body = strBody
    ''Unrem the next line if you want the user to see each email before it reaches the Outbox
             'olItem.Display
    ''To run this without placing emails into the Outbox, rem the next line
             olItem.Send
          Next
          '.MailMerge.Destination = wdSendToEmail
    ''Unrem the next line if you want the user to see each email before it reaches the Outbox
          'olItem.Display
          '.MailMerge.Execute
          .Close SaveChanges:=wdDoNotSaveChanges
       End With

       Set olApp = Nothing
       Set wdMerged = Nothing
       WdApp.Quit

    Exit_ErrorHandler:
       On Error GoTo 0
       Exit Sub

    ErrorHandler:
       Const strProc As String = "CreateEmail"
       Select Case Err.Number
          Case 287    ''User cancelled sending an email
             MsgBox "It seems you have cancelled the issue of emails.  This means these reminders will appear again for issue."
             wdMerged.Close SaveChanges:=wdDoNotSaveChanges
             Set wdMerged = Nothing
          Case Else
             spSEMReport cstrMdl, strProc, Err.Number, Err.Description
       End Select
       Set olApp = Nothing
       Set olNS = Nothing
       WdApp.Quit
       EmailCancelled strCSV, strIssue
       GoTo Exit_ErrorHandler
    End Sub


    Private Sub OpenOutlook1()

    On Error GoTo ErrorHandler

        Dim retVal As Double
       
        'MsgBox "You may already have Outlook open.  If you have, it will not matter and a second instance of Outlook will be opened."
     
        retVal = Shell("C:\Program Files (x86)\Microsoft Office\OFFICE15\OUTLOOK.EXE", vbMinimizedNoFocus)
     
        If (retVal = 0) Then
            MsgBox "Failed to open Outlook."
        End If
        
        OpenOutlook2

    Exit_ErrorHandler:
       On Error GoTo 0
       Exit Sub

    ErrorHandler:
       Const strProc As String = "OpenOutlook1"
       Select Case Err.Number
          Case Else
             spSEMReport cstrMdl, strProc, Err.Number, Err.Description
       End Select
       GoTo Exit_ErrorHandler

    End Sub

    Private Sub OpenOutlook2()

    On Error GoTo ErrorHandler
       
       If olApp Is Nothing Then
          If InitialiseOutlook = False Then
             MsgBox "Unable to initialise Outlook application " & _
                    "or NameSpace object variables"
             Exit Sub
          End If
       End If

    Exit_ErrorHandler:
       On Error GoTo 0
       Exit Sub

    ErrorHandler:
       Const strProc As String = "OpenOutlook2"
       Select Case Err.Number
          Case Else
             spSEMReport cstrMdl, strProc, Err.Number, Err.Description
       End Select
       GoTo Exit_ErrorHandler

    End Sub

    Function InitialiseOutlook() As Boolean

    On Error GoTo ErrorHandler
       
       Set olApp = New Outlook.Application
       'Set olApp = GetObject(, "Outlook.Application")

       Set olNS = olApp.GetNamespace("MAPI")
       
       InitialiseOutlook = True

    Exit_ErrorHandler:
       On Error GoTo 0
       Exit Function

    ErrorHandler:
       Const strProc As String = "InitialiseOutlook"
       Select Case Err.Number
          Case 287    ''User cancelled sending an email
             Set olApp = Nothing
             Set olNS = Nothing
             GoTo Exit_ErrorHandler
             InitialiseOutlook = False
             MsgBox "The user seems to have cancelled the process"
          Case Else
             InitialiseOutlook = False
             MsgBox "Outlook cannot initialise properly"
             spSEMReport cstrMdl, strProc, Err.Number, Err.Description
       End Select
       GoTo Exit_ErrorHandler

    End Function

    Thursday, June 23, 2016 10:34 PM
  • Hi Victor,

    Based on your code, I did not find anything you send an email with delay, it seems you use rules to delay the delivery of all messages. It would be helpful if you could share us all the related rules. If you are not using rules, could you share us how you keep emails in Outbox instead of sending directly.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 24, 2016 3:20 AM
  • Hi Edward

    You are right, no delay is in that code.  You asked for the code that creates and 'sends' the email.  That code is in an Access vba module.  The delay and final 'send' code is in an Outlook module and I have not included it because I am unsure how to finish writing it to get the result I need.  Until I understand how to write code to count (I can do that bit) and then actually send items waiting in the Outbox, I can't finish it.

    Thanks for your continuing interest.

    Victor

    Friday, June 24, 2016 6:07 AM
  • Hi Victor,

    Do you want to create an email which is scheduled when to send or create an email and you will check it in Outlook by some logic, if it meets send it again?

    Based on your description, it seems you create email in Access, and want to check it and send in Outlook, am I right? If so, I think you do not need schedule email, you could create an email and save it to drafts, then send emails in drafts in Outlook.

    With your code in Access, use Save instead of send method, and now your email will be saved to drafts in Outlook.

    Here is a simple code to loop emails in Drafts.

    Sub SendEmailInDraft()
    Dim f As Folder
    Set f = Application.Session.GetDefaultFolder(olFolderDrafts)
    Dim e As Outlook.MailItem
    For Each e In f.Items
        If e.Subject = "TestAccess3" Then
            e.Send
        End If
    Next e
    End Sub

    In addition, you could not select any items in Drafts Folder when you run above code in Outlook.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Sunday, June 26, 2016 3:20 AM
  • Edward and Eugene

    I had been diverted to another project and only just had a chance to get back to this one and experiment with code.

    It works thanks to you both.  I had always used 'Send' and now realise the trick is to 'Save' the mail that I had not realised places the mail in Drafts.  As you say I can then process them at will and apply my count and time delay.  What is also crucial is the discovery of 'Sync' that mimics the 'Send/Receive' button so I can now do everything in code.

    Thank you again.

    Victor

    Thursday, July 7, 2016 12:27 AM