VBA sometimes error while sending email

[alexanderbon]

I have created a excel workbook with serveral sheets, one of them is a sale offer. The macro copies the sales offer to a new workbook, than changes one cell, which is the name of the receiver.
For each receiver, it saves a seperate excel workbook with the sales offer.

After that, it calls a seperate sub, to send those files per email to the receivers.
The original workbook has a sheet for each day, which have the name, email adress and file patch+name in it. It's used for 5 days a week, so monday-friday.
The amount of receivers are 30-40 a day.

Now when I test it, it works good for each day. But when I let the user do it (on another computer), it sometimes trows an error at almost the end of the list of the mails it has to send.

The error is: vba error 5 invalid procedure call or argument
VBA marks it at: msg.send

This is the code for generating the files:

Sub Maakbestanden_maandag()

    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Sheets("Kopers-Maandag")


    Dim Ab As Worksheet
    Set Ab = ThisWorkbook.Sheets("Aanbod")




    Application.ScreenUpdating = False
  
    Application.DisplayAlerts = False


    Sheets("Aanbod").Select
    Sheets("Aanbod").Copy
        Cells.Select


    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False




    Range("A15:C15").Select
   
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 14336204
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


    Range("D20:D49").Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
  


    Range("C20:C49").Select
    Selection.NumberFormat = "@"
    


    Range("E20:F49").Select
    Selection.NumberFormat = "0"




    Columns("E:E").ColumnWidth = 8
    Columns("F:F").ColumnWidth = 6


' Stel auteur in
    ActiveWorkbook.BuiltinDocumentProperties("Author") = "author name here"




    Range("G50").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-30]C:R[-1]C)"
    


    Range("G51").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/12"




    Dim i As Integer
    Dim last_row As Integer


    last_row = Application.WorksheetFunction.CountA(Sh.Range("A:A"))




    For i = 2 To last_row




    Range("D15:H15").Select
    ActiveCell.FormulaR1C1 = Sh.Range("B" & i).Value




    Range("D15:H15").Select




    Application.ActiveWorkbook.SaveAs Filename:=Sh.Range("C" & i).Value, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


    Next i




    Application.DisplayAlerts = True




    ActiveWindow.Close




    MsgBox "Bestanden aangemaakt"




    Call Verstuuremail_maandag


End Sub

And this is to send the emails:

Sub Verstuuremail_maandag()

Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("Kopers-Maandag")


Dim OA As Object
Dim msg As Object


Set OA = CreateObject("Outlook.Application")


Dim i As Integer
Dim last_row As Integer


last_row = Application.WorksheetFunction.CountA(Sh.Range("A:A"))


For i = 2 To last_row
Set msg = OA.createitem(0)


msg.To = Sh.Range("A" & i).Value
msg.Subject = "Aanbod Maandag"


msg.body = ""


If Sh.Range("C" & i).Value <> "" Then
msg.attachments.Add Sh.Range("C" & i).Value
End If




msg.send


Dim LDate As String


'Sh.Range("F" & i).Value = "Sent"


Next i


MsgBox "E-mails voor maandag verstuurd"




End Sub

Each day has copies of these subs, the only difference is the sheet it looks in for each day.

Like I said, it works sometimes, sometimes not. If it does trow the error, it already has send about 2/3 of the emails, but than it suddenly come up with the error. The day also doesn't matter, and it also can do all of them good at the first try, and then the second try on the same day, it suddenly trows the error at 2/3 of the way.

Can somebody explain what I'm doing wrong here, and how to solve it?
It's beginning to drive me crazy..

 

[alexanderbon]

I haven't set breakpoints yet, i doubt it would help. It's doing the same piece of code about 25 times the correct way, since it's a loop, and than suddenly comes up with a error.
Where should I set breakpoints than, on the loop?

 

[Computerman]

if the user has a breakpoint set and you use F5 to run each stage, does it run to the end after the required number if F5s

Alexanderbon,
Along with mole999’s suggestion I would also comment out the msg.send line and use msg.display. That way the email will show on your screen. In my Excel Email program, I have both statements one under the other. I comment out the send function when I want to test a change to my program and then uncomment the send and comment out the display when I want to go live.

 

[alexanderbon]

Alexanderbon,
Along with mole999Â’s suggestion I would also comment out the msg.send line and use msg.display. That way the email will show on your screen. In my Excel Email program, I have both statements one under the other. I comment out the send function when I want to test a change to my program and then uncomment the send and comment out the display when I want to go live.

Alright I will do it.

Question about breakpoints, I set it on the loop and than saved the file. But when I close and open the file again, the breakpoint is gone. Is this normal?

 

[Computerman]

Alexanderbon,
Setting a breakpoint will help you analyze how your program is functioning. Is it calculating the correct number of times it needs to run through the code. Is it properly formatting the emails, with the correct attachments each time. I would place the breakpoint on the msg.body = “” line.

 

[Computerman]

Alexanderbon,
I was about to say no it isn’t, then decided to test it myself. And yes the breakpoint did not save with the file. So that is normal.
Computerman

 

[alexanderbon]

An update: It works good everytime with msg.display. Evey email is formatted correctly that way, and no errors.
I haven't got a chance yet to try it with a breakpoint though, since I can't set the breakpoint in advance, so I have to do it togheter with the user sometime.

Would it maybe help if I send the emails directly through SMTP instead of outlook? I think that's also a possibility right?

 

[mole999]

so the email system is the blockage, not processing fast enough, hence do events, after the send mail. or end of that loop. or you could add a time of 1 or 2 seconds per cycle, faster disk or memory or processor or less open operations

 

[alexanderbon]

I already have doevents in the code, but that doesn't seem to help. I doubt the hardware is not good enoug, it's a fairly new PC with 8gb ram, SSD with the OS, HDD for storage.

I will try to add a couple of second to it.

 

[alexanderbon]

Unfortunatly it's still not working as it should, even with some wait time between each mail.

I use displaymail now, so the user has to click on send himself still.
Some emails are not correct, it than only shows like this:

So it doesn't have a subject line, no body and it's missing some email adresses, since it usually happens with mails that has multiple receiptens (up to 3 adresses).
Also it doesn't do anything when clicking on the send button.

When I look at the event logs in Windows, it does have an error event for each of the mail, but only saying: There was an error, you can try again.

Anybody know what to do next?

 

[mole999]

in the names are they separated by ; or , language can change the marks need to be used, if it is your multiple recipients that fail then try swapping