Please help : Macro Error Handling, ignore wrong path name

[sam_xcv]

Hi All,

This is my first post and was hoping some guidance reg. my issue.

So i have the following code which creates an email and attaches files to it. However it's not necessary that each file is going to exist every time, so the pathname could be wrong. I am very new to the world of macros and would appreciate your help.

1. So incase my macro doesn't find the path ("can not find path" error.), it should still go ahead and check other files and attach the files if they exist. Basically surpass the error of not finding path
2. and display the email, where I can decide if I should send the email or not.


many many thanks in advance
--------------------------

Sub send_email_with_multiple_attachements()




Dim o As Outlook.Application

Set o = New Outlook.Application

Dim omail As Outlook.MailItem

Dim File As String



Dim i As Long

For i = 2 To Range("a100").End(xlUp).Row

Set omail = o.CreateItem(olMailItem)

With omail

.Body = "Dear " & Cells(i, 1).Value & "," & Cells(i, 4).Value

.To = Cells(i, 2).Value

.CC = Cells(i, 5).Value

.Subject = Cells(i, 3).Value

.Attachments.Add Cells(i, 6).Value

.Attachments.Add Cells(i, 7).Value

.Attachments.Add Cells(i, 8).Value

.Attachments.Add Cells(i, 9).Value


.Display

End With
Next
End Sub

 

[dmt32]

Hi
welcome to forum

Untested but see if this update to your code does what you want

Sub send_email_with_multiple_attachements()

    Dim o           As Outlook.Application
    Dim omail       As Outlook.MailItem
    Dim File        As String
    Dim i           As Long
    Dim c           As Integer
   
    Set o = New Outlook.Application
   
    For i = 2 To Range("a100").End(xlUp).Row
       
        Set omail = o.CreateItem(olMailItem)
       
        With omail
           
            .Body = "Dear " & Cells(i, 1).Value & "," & Cells(i, 4).Value
           
            .To = Cells(i, 2).Value
            .CC = Cells(i, 5).Value
            .Subject = Cells(i, 3).Value
           
            For c = 6 To 9
                If Not Dir(Cells(i, c).Value, vbDirectory) = vbNullString Then
                    .Attachments.Add Cells(i, c).Value
                End If
            Next c
           
            .Display
           
        End With
    Next
End Sub

Do be aware that if cells are blank code may throw an error - if this is likely to occur, an additional line of code can be added.

Dave

 

[sam_xcv]

Hey Dave - many thanks for helping out. All working fine, you are a legend

I added on error resume next to deal with blank cells

btw, one more cheeky advice. lets say i want the file path to be dynamic such that when i run the macro, the macro should adjust the pathname with today's date. Say, my current path is "C:/06/01/2020/report/abc.xlx", but when i run my macro next day, it should automatically update it to "C:/07/01/2020/report/abc.xlx". is it possible?

 

[dmt32]

Hey Dave - many thanks for helping out. All working fine, you are a legend

I added on error resume next to deal with blank cells

btw, one more cheeky advice. lets say i want the file path to be dynamic such that when i run the macro, the macro should adjust the pathname with today's date. Say, my current path is "C:/06/01/2020/report/abc.xlx", but when i run my macro next day, it should automatically update it to "C:/07/01/2020/report/abc.xlx". is it possible?

If you mean automatically in cell value?

then maybe something like this

="C:/"&TEXT(TODAY(),"dd/mm/yyyy")&"/report/abc.xlsx"

Dave

 

[sam_xcv]

many thanks mate

 

[dmt32]

many thanks mate

welcome

Dave