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]

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

There are sperated with ;
I tried with and without a space between ; and the next adress, but that doesn't make an difference.
What's odd, is that it works as is should be a lot of times, but randomly some times it doesn't work.

I just tried it with commas, but Outlook doesn't accept than when trying to send an email like that, so that's not it.