Compile Error when Adding Attachments

[lharr28]

I'm trying to get the code to include an attachment if the file's path is entered in column G. When I run the macros I get an compile error message. I tried to define the attachment but it didn't work. I'm thinking something is wrong with the if/then syntax I added. Below I've included a copy of the error message, I screenshot of the document, and the VBA Code.

Sub sendMail()
'Add microsoft word, outlook library object tools
'As the code uses early binding it needs references to:
    'Microsoft Outlook nn.n Object Library
    'Microsoft Word nn.n Object Library

    Dim ol As Outlook.Application
    Dim olm As Outlook.MailItem
    Dim Attachments As Outlook.Attachments
    
    Dim wd As Word.Application
    Dim doc As Word.Document
    
    Set ol = New Outlook.Application
    
'***Start from row 11 and go to the last row with data
    Dim r As Long
    
    For r = 11 To Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
        '***will send email if sent status is "No"
        If StrConv(Cells(r, "K"), vbProperCase) = "No" Then '***the code is checking the active row (r) in Col. K and if the text is "No" (I use proper case so there's no question on matching the text) it runs the script else it goes to the next row (r)
            If Sheet4.Cells(r, 7).Value <> "" Then
            Set olm = ol.CreateItem(olMailItem)
            
        '***pulling the template to use, note: document must be saved as a word template!
            Set wd = New Word.Application
            Set doc = wd.documents.Open(Cells(6, 2).Value)
        
            With wd.Selection.Find
            .Text = "<<first name>>"
            .Replacement.Text = Sheet4.Cells(r, 3).Value '****info is in column 3 or C
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<Merchant>>"
            .Replacement.Text = Sheet4.Cells(r, 9).Value '****info is in column 9 or I
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<Amount>>"
            .Replacement.Text = Sheet4.Cells(r, 10).Value '****info is in column 10 or J
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<transaction date>>"
            .Replacement.Text = Sheet4.Cells(r, 8).Value '****info is in column 8 or H
            .Execute Replace:=wdReplaceAll
            End With
        
            doc.Content.Copy
            
        '***Set the properties of the mail item, to, cc, subject, etc...
            With olm
                .Display
                .To = Sheet4.Cells(r, 4).Value
                .CC = Sheet4.Cells(r, 5).Value
                .Subject = Sheet4.Cells(r, 6).Value
            
        '***Copying the information from the word document into the body of the email
                Dim editor As Object
                Set editor = .GetInspector.WordEditor
                editor.Content.Paste
                '.Send
            End With
        
            Set olm = Nothing
        
            Application.DisplayAlerts = False
            doc.Close SaveChanges:=False
            Set doc = Nothing
            wd.Quit
            Set wd = Nothing
            Application.DisplayAlerts = True
            
            .Attachments.Add Sheet4.Cells(r, 7).Value
            End If
        
        End If
    
    Next r

End Sub

 

[6StringJazzer]

Your line of code

            .Attachments.Add Sheet4.Cells(r, 7).Value

is written to be inside the With statement, but it's not. Move the End With down below this line, or in this particular case you can move this line up above the End With.

 

[lharr28]

Thanks Jeff! I tried this and it did work but now it only works if I have an attachment listed in the attachment field (column G). How would I get it to send even if there is no attachment listed in that filed. I've included the code below of what I did.

For r = 11 To Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
        '***will send email if sent status is "No"
        If StrConv(Cells(r, "K"), vbProperCase) = "No" Then 
            If Sheet4.Cells(r, 7).Value <> "" Then
            Set olm = ol.CreateItem(olMailItem)
            
        '***pulling the template to use, note: document must be saved as a word template!
            Set wd = New Word.Application
            Set doc = wd.documents.Open(Cells(6, 2).Value)
        
            With wd.Selection.Find
            .Text = "<<first name>>"
            .Replacement.Text = Sheet4.Cells(r, 3).Value '****info is in column 3 or C
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<Merchant>>"
            .Replacement.Text = Sheet4.Cells(r, 9).Value '****info is in column 9 or I
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<Amount>>"
            .Replacement.Text = Sheet4.Cells(r, 10).Value '****info is in column 10 or J
            .Execute Replace:=wdReplaceAll
            End With
            
            With wd.Selection.Find
            .Text = "<<transaction date>>"
            .Replacement.Text = Sheet4.Cells(r, 8).Value '****info is in column 8 or H
            .Execute Replace:=wdReplaceAll
            End With
        
            doc.Content.Copy
            
        '***Set the properties of the mail item, to, cc, subject, etc...
            With olm
                .Display
                .To = Sheet4.Cells(r, 4).Value
                .CC = Sheet4.Cells(r, 5).Value
                .Subject = Sheet4.Cells(r, 6).Value
            
        '***Copying the information from the word document into the body of the email
                Dim editor As Object
                Set editor = .GetInspector.WordEditor
                editor.Content.Paste
                '.Send
            
            '***add attachment if field has data
            .Attachments.Add Sheet4.Cells(r, 7).Value
            End With
        
            Set olm = Nothing
        
            Application.DisplayAlerts = False
            doc.Close SaveChanges:=False
            Set doc = Nothing
            wd.Quit
            Set wd = Nothing
            Application.DisplayAlerts = True
            
            End If
            
            Next r
        
        End If
    
    Next r

 

[6StringJazzer]

            '***add attachment if field has data
            If Sheet4.Cells(r, 7).Value <> "" Then
               .Attachments.Add Sheet4.Cells(r, 7).Value
            End If

This does not test if the cell has a valid file path, just that it has something.

 

[lharr28]

            '***add attachment if field has data
            If Sheet4.Cells(r, 7).Value <> "" Then
               .Attachments.Add Sheet4.Cells(r, 7).Value
            End If

This does not test if the cell has a valid file path, just that it has something.

Ok, I may have misinterpreted the meaning. I thought it meant that if the field isn't blank then it will attach that file. I will see if I can find a work around for this. Thanks, again

 

[6StringJazzer]

I don't understand your latest comment.

I assumed that you were getting an error trying to attach something using a blank path, so the mail was not being sent.

I showed a code modification that will attach a file based on the pathname in column G of that row, but only if that cell is not blank. I wrapped it in that If statement, which was not present in your code.

However, that does not guarantee that the content of the cell is a valid pathname.