Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: VBA sometimes error while sending email

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA sometimes error while sending email

    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:
    Code:
    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:

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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA sometimes error while sending email

    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA sometimes error while sending email

    Quote Originally Posted by mole999 View Post
    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.

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA sometimes error while sending email

    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 ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA sometimes error while sending email

    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.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA sometimes error while sending email

    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    Board Regular
    Join Date
    Mar 2016
    Location
    Northwest Arkansas
    Posts
    90
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA sometimes error while sending email

    Quote Originally Posted by alexanderbon View Post
    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.

  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA sometimes error while sending email

    Quote Originally Posted by Computerman View Post
    Try DIMing your Last_Row as Long, instead of as Integer.
    I'm gonna try that as well, thanks!

  9. #9
    New Member
    Join Date
    Apr 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA sometimes error while sending email

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

  10. #10
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA sometimes error while sending email

    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •