Excel to Outlook error

[tiredofit]

This is the standard code for Excel to set up an e-mail in Outlook:

    ' early binding

    Dim o As Outlook.Application
    Dim om As Outlook.MailItem
    
    Set o = New Outlook.Application

    Set om = o.CreateItem(olMailItem)
    
    With om
    
        .BodyFormat = olFormatPlain
        
        .Body = "hi"
        
        .Display
        
        .To = "dummy@dummy.com"
        
        .Subject = "Some subject"
        
    End With

It works as expected.


The problem is I have to do this a number of times, eg:

    Const N As Long = 10

    Dim o As Outlook.Application
    Dim oe As Outlook.MailItem
    
    Dim i As Long

    For i = 1 to N

        Set o = New Outlook.Application

        Set oe = o.CreateItem(olMailItem)
    
        With oe
    
            .BodyFormat = olFormatPlain
        
            .Body = "hi"
        
            .Display
        
            .To = "dummy@dummy.com"
        
            .Subject = "Some subject"
        
        End With

    Next i

When N is large, (say 50), the code crashes on this line:

    .Display

with the message:

    [ATTACH type="full" size="329x194"]120684[/ATTACH]

Does anyone know what is causing it and how can it be resolved?

Thanks

 

[Joe4]

I don't work much with Excel creating Outlook emails, but is it really necessary to actually Display it in your code?
If not, can you just remove that "Display" line of code.

The other think you might want to do at the end of each loop is to clear your object variables, i.e.

Set o = Nothing
Set oe = Nothing

to keep you memory from bloating.

 

[tiredofit]

I don't work much with Excel creating Outlook emails, but is it really necessary to actually Display it in your code?
If not, can you just remove that "Display" line of code.

The other think you might want to do at the end of each loop is to clear your object variables, i.e.

Set o = Nothing
Set oe = Nothing

to keep you memory from bloating.

If I added:

.Send

the e-mails will be sent.

So without:

.Display

nothing happens.

 

[Joe4]

OK, did you try adding those two lines of code I gave you to see if that resolves your error issue?

 

[tiredofit]

OK, did you try adding those two lines of code I gave you to see if that resolves your error issue?

Yes I added them though it didn't help.

I think there's a limit to how many e-mails get displayed.

 

[Joe4]

Could be. I cannot imagine wanting more than 50 emails open on your computer at one time anyway.

So you are not really trying to send the emails via the VBA code, just display them?

 

[tiredofit]

Could be. I cannot imagine wanting more than 50 emails open on your computer at one time anyway.

So you are not really trying to send the emails via the VBA code, just display them?

I like to give my clients the option of checking the e-mail first, hence the reluctance to add .Send.

 

[Joe4]

I like to give my clients the option of checking the e-mail first, hence the reluctance to add .Send.

Then I would recommend doing it in batches.

IMO, any solution that involves creating more than 50 open email windows at the same time is probably not an optimal solution.
It sounds like you could be pushing the limits of Outlook and/or your computer's memory.
So maybe consider limiting it to something like batches of 25 at a time.

 

[tiredofit]

Then I would recommend doing it in batches.

IMO, any solution that involves creating more than 50 open email windows at the same time is probably not an optimal solution.
It sounds like you could be pushing the limits of Outlook and/or your computer's memory.
So maybe consider limiting it to something like batches of 25 at a time.

Theory and practise is very different. Clients think if something works once, it'll always work! I understand one click is more efficient than two.

What is of interest to me is the constant N is NOT fixed (obviously it's a constant)!

I can't work out when it falls over. It's typically 50 +/- 5 but as there's no concept of pure randomness to computers (at least I don't think so), I would've thought it should always crash at the same point.

Re the memory issue, some suggested for similar problems to save the workbook along the way but even that didn't work, so I'm fairly certain it's an Outlook issue and not Excel / memory.

 

[Joe4]

so I'm fairly certain it's an Outlook issue and not Excel / memory

I suspect you may be right (and suggested as much). As such, I don't think there is much more that I can give you, from an Excel standpoint.

As for the randomness of the number, it could be dependent upon what else may be happening on your computer at the same time, using it resources.
Or how much memory Outlook may be using at the moment.

Like I said, short of changing your approach, the only recommendation I can make to you if you want to use your current methodology is to limit the number of emails is does at a time so you don't run into that error.
I really have no other suggestions for you.

I did find this article, which seems to talk about the problem you are experiencing, and their recommendations:
Classic Outlook errors opening more than sixty emails at the same time - Microsoft Support.