Consulting

Results 1 to 3 of 3

Thread: New mails are not send until Macro is 'reset'

  1. #1

    New mails are not send until Macro is 'reset'

    Hi folks,

    I'm using VBA in OUTLOOK for the first time(till now only used it in EXCEL) ....
    I have the following code :

    Sub ScreenAndSend()
    Dim olAPP As Outlook.Application
    Dim olNs As Outlook.NameSpace
    Dim olFolder As Outlook.Folder
    Dim olMailOutput As Outlook.MailItem
    Dim StartTime as Single
    Dim ExtTime as single
    fLoop As Boolean
    ExtTime = 100
    fLoop = true
    While fLoop
        'somewhere in my code :
    Set olMailOutput = Application.CreateItem(olMailItem)
        With olMailOutput
       .To = "<list of e-mail addresses"
       .BCC = "<list of e-mail addresses"
       .Subject = "my subject"
       .BodyFormat = olFormatHTML
       .Body = "content of the mail"
       .Send
       Debug.Print "mail send"
        End with
    StartTime = Timer
        Do While Timer < StartTime + ExtTime
               DoEvents    ' Yield to other processes.
        Loop
    Debug.Print "LOOPED"
    Wend
    End sub
    Everything works fine, including the 'message send' notification.
    But the mail isn't send (that is, they're not arriving at destination) until I stop the VBA code from executing.
    Then the receivers get a mail, with a timestamp equals to the date/time when I stop the code.

    The 'DoEvents'-loop (of 100 seconds) is there to free CPU for other activities.
    I do get the "LOOPED" message after approx 100 seconds, confirming that I get that far.

    Can anyone tell me what I'm forgetting, or doing wrong ?

    Thanks,

    Martin.
    Last edited by Aussiebear; 03-15-2023 at 04:01 PM. Reason: Added code tags to wrap the submitted code

  2. #2
    The problem seems to relate to the apparently unnecessary loops, the purpose of which is not explained by your code.
    Are you planning on running this code from Excel or as implied from your post from Outlook. If the latter you can simplify the code
    Sub ScreenAndSend()
    Dim olMailOutput As Outlook.MailItem
        Set olMailOutput = CreateItem(olMailItem)
        With olMailOutput
            .To = "<list of e-mail addresses"
            .BCC = "<list of e-mail addresses"
            .Subject = "my subject"
            .BodyFormat = olFormatHTML
            .Body = "content of the mail"
            .Display 'change to .Send after testing
    Debug.Print "mail send"
        End With
        Set olMailOutput = Nothing
    End Sub
    If the former, it gets a whole lot more complicated. The following is a typical example
    Public Sub CreateEmail()
    'Graham Mayor - https://www.gmayor.com - Last updated - 18 May 2021
    'Requires the code - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to either retrieve an open instance of Outlook or open Outlook if it is closed.
    Dim olApp As Object
    Dim olMail As Object        ' Outlook.MailItem
    Dim olInsp As Object        ' Outlook.Inspector
    Dim wdDoc As Object        ' Word.Document
    Dim wdRange As Object        ' Word.Range
    
        Set olApp = OutlookApp()
        Set olMail = olApp.CreateItem(0)
        With olMail
            .BodyFormat = 2
            .Display
            .To = "someone@somewhere.com"
            .Subject = "Message Subject"
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set wdRange = wdDoc.Range
            With wdRange
                .Collapse 1
                .Font.Color = RGB(0, 0, 0)
                .Font.Size = 11
                .Font.Bold = False
    
                .Text = "Hi, " & "Name of person" & vbCr & vbCr & _
                        "Here is your invoice # " & "123456"
                .Collapse 0
    
                .Text = " PAID "
                .Font.Color = RGB(255, 0, 0)
                .Font.Size = 16
                .Font.Bold = True
                .Collapse 0
    
                .Text = "more text and end of email"    'signature associated with account is retained.
                .Font.Color = RGB(0, 0, 0)
                .Font.Size = 11
                .Font.Bold = False
            End With
        End With
    lbl_Exit:
        Set wdRange = Nothing
        Set wdDoc = Nothing
        Set olInsp = Nothing
        Set olMail = Nothing
        Set olApp = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Hello,

    To get the context explained:

    I have this 'main' macro that runs 24/7 in Outlook.
    It has 2 main functions :

    - screen my inbox every 15 minutes for known senders, opening the mail and save the attachment to disk and increase a dedicated counter;
    - at 6:00AM, 13:00PM and 17:00PM it sends a report (statistics) by mail to management, and resets the counters.

    To test it, I added some debug.print commands in the code to track/monitor the execution.

    All works fine, except the .send ....
    I do see the result of the debug.print "LOOPED" 15 minutes after the .send, but no mail is going out (that is, management isn't receiving the report), unless I click the reset icon to stop te macro.
    Then the mail arrives at destination, but the timestamp = time that I stopped the macro, not the time it was generated (15 minutes earlier)

    I hope this makes sense ...

    Regards,

    Martin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •