Please help : Macro Error Handling, ignore wrong path name

sam_xcv

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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

 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,479
Office Version
  1. 2019
Platform
  1. Windows
Hi
welcome to forum

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

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

New Member
Joined
Jan 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,479
Office Version
  1. 2019
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,313
Messages
5,623,934
Members
416,001
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top