# VBA sometimes error while sending email



## alexanderbon (Apr 16, 2019)

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 (Apr 16, 2019)

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 (Apr 16, 2019)

mole999 said:


> 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 (Apr 16, 2019)

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 (Apr 16, 2019)

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 (Apr 16, 2019)

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 (Apr 16, 2019)

alexanderbon said:


> 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 (Apr 16, 2019)

Computerman said:


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



I'm gonna try that as well, thanks!


----------



## alexanderbon (Apr 17, 2019)

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


----------



## mole999 (Apr 17, 2019)

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 (Apr 16, 2019)

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 (Apr 17, 2019)

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 (Apr 17, 2019)

mole999 said:


> 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 (Apr 17, 2019)

Computerman said:


> 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 (Apr 17, 2019)

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 (Apr 17, 2019)

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 (Apr 21, 2019)

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 (Apr 21, 2019)

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 (Apr 21, 2019)

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 (May 8, 2019)

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 (May 8, 2019)

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


----------



## alexanderbon (Apr 16, 2019)

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 (May 8, 2019)

mole999 said:


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


----------

