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..

 

[mole999]

it may be your messaging system, if it detects abnormal activity (multiple emails sent really quickly it might decide it is spamming, so message software or antivirus. Adding a short time delay between each message or DoEvents may allow it enough time to finish

 

[alexanderbon]

it may be your messaging system, if it detects abnormal activity (multiple emails sent really quickly it might decide it is spamming, so message software or antivirus. Adding a short time delay between each message or DoEvents may allow it enough time to finish

What do I need to change to do this?
I'm not so experienced with VBA, I build this mainly on examples from different websites.

 

[mole999]

i would start with

DoEvents
msg.send

and see if that works, are everybody on the same excel / os. are they trying to do other work whilst it runs ?

 

[alexanderbon]

Thanks, I have added this to the macro now. I have to see tomorrow if that helps, since it was always working when I tried it myself.

 

[mole999]

users can cause a problem if they continue to do other things in different workbooks, where you may just watch the one task to ensure it completes

 

[Computerman]

Thanks, I have added this to the macro now. I have to see tomorrow if that helps, since it was always working when I tried it myself.

Try DIMing your Last_Row as Long, instead of as Integer.

 

[alexanderbon]

Try DIMing your Last_Row as Long, instead of as Integer.

I'm gonna try that as well, thanks!

 

[alexanderbon]

Unfortunately both modifications didn't help to solve the problem. It was stuck at pretty much the same point again today.

 

[mole999]

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