VBA stopped with Error-Message

Try this:

Sub Excel_Serial_Mail()

    Dim objOLOutlook                As Object
    Dim objOLMail                   As Object
    Dim lngMailNr                   As Long
    Dim lngZaehler                  As Long

    Set objOLOutlook = CreateObject("Outlook.Application")
    lngMailNr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

    For lngZaehler = 2 To lngMailNr
        If Cells(lngZaehler, 2) <> "" Then
            Set objOLMail = objOLOutlook.CreateItem(olMailItem)
            With objOLMail
                .To = Cells(lngZaehler, 3)
                .CC = Cells(lngZaehler, 4)
                .BCC = "test@test.com"
                .SentOnBehalfOfName = "test@test.com"
                .Subject = Cells(lngZaehler, 6)
                .BodyFormat = olFormatPlain
                .Body = Cells(lngZaehler, 7)
                Dim strAttachmentPfad1 As String
                If Len(ActiveSheet.Cells(lngZaehler, 8).Value2) <> 0 Then .Attachments.Add ActiveSheet.Cells(lngZaehler, 8).Value2
                .Display
            End With
            Set objOLMail = Nothing
        End If
    Next lngZaehler
    Set objOLOutlook = Nothing

End Sub

Hi Rorya,
thank you for your help.
But there is a Problem with the following part: .Attachments.Add ActiveSheet.Cells(lngZaehler, 8).Value2

What is the problem specifically?

I have found, that the VBA works if I delete the path for the file that not exists. Perfect!
Do you have an idea if I have for two lines sometimes two files to attach?

Are the two attachments listed in the same cell? If so, how are they separated? If not, where are they?

how is the format to list them in the same cell? This is the first attachment in cell H3 = G:\Shareplan\test_31686\Plan\08\Sale Proceeds\sales_breakdown 210416 ret.pdf and the second file Name is sales breakdown 100516 ret.pdf

If you separate them with a comma, like:

G:\Shareplan\test_31686\Plan\08\Sale Proceeds\sales_breakdown 210416 ret.pdf, G:\Shareplan\test_31686\Plan\08\Sale Proceeds\sales breakdown 100516 ret.pdf

then you can use:

Sub Excel_Serial_Mail()

    Dim objOLOutlook                As Object
    Dim objOLMail                   As Object
    Dim lngMailNr                   As Long
    Dim lngZaehler                  As Long
    Dim vAttachments
    Dim n                           As Long

    Set objOLOutlook = CreateObject("Outlook.Application")
    lngMailNr = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

    For lngZaehler = 2 To lngMailNr
        If Cells(lngZaehler, 2) <> "" Then
            Set objOLMail = objOLOutlook.CreateItem(olMailItem)
            With objOLMail
                .To = Cells(lngZaehler, 3)
                .CC = Cells(lngZaehler, 4)
                .BCC = "test@test.com"
                .SentOnBehalfOfName = "test@test.com"
                .Subject = Cells(lngZaehler, 6)
                .BodyFormat = olFormatPlain
                .Body = Cells(lngZaehler, 7)

                If Len(ActiveSheet.Cells(lngZaehler, 8).Value2) <> 0 Then
                    vAttachments = VBA.Split(ActiveSheet.Cells(lngZaehler, 8).Value2, ",")
                    For n = LBound(vAttachments) To UBound(vAttachments)
                        If VBA.Dir$(vAttachments(n)) <> vbNullString Then .Attachments.Add vAttachments(n)
                    Next n
                End If
                .Display
            End With
            Set objOLMail = Nothing
        End If
    Next lngZaehler
    Set objOLOutlook = Nothing

End Sub

which will check each file to see if it exists and add it to the email if it does.

Hi Rorya,

thanks a lot.
But I have 24 different lines (for each City I have one line because in this line are different E-Mail addresses and subjects). Most of the cities have one attachment. 2 or 3 no attachment but not always the same and two of them have two attachments. The path is from h2 to h25.
Do you have a solution for this?

Hi Ben,

I have changed the two Points.

Kind regards,

Thorsten

When you've done that, please clarify what the problem is with the last code.

I have 24 different lines (for each City I have one line because in this line are different E-Mail addresses and subjects). Most of the cities have one attachment. 2 or 3 no attachment but not always the same and two of them have two attachments. The path is from h2 to h25.
If I use a comma between the file path for each of them I get the following error message: Run-time error 52 - Bad file Name or numer.

The code works for every line with 1 attachment fine. If I have a second attachment I got this error.

Please give an exact example of the contents when there are two files.

Hi,
for two cities I have in a Special path sometimes two files which have to be attached:
for example: G:\Sale Proceeds\sales_breakdown 210416 bah br.pdf and G:\Sale Proceeds\sales_breakdown 210416 bah tr.pdf

Those are not separated by a comma.

If I make it in this way: "G:\Sale Proceeds\sales_breakdown 210416 bah br.pdf,G:\Sale Proceeds\sales_breakdown 210416 bah tr.pdf"
I get an error message: Run-time error 52 - Bad file Name or number.

I take it you don't actually have the " in the cell?

Which line gives the error?

Hi Rorya,
I don't have the " in the cell.
I have copied the code into a new file and now it works.
I have added: ".Save and .Close olPromtForSave "and now I have all E-Mails in my drafts - perfect.
Now the code drafts an E-Mail for every line. Is it easy to Change the code, that he don't create an E-Mail if there is no file to attach?