How attach PDF, where location in different Excel sheet from same workbook, to Outlook mail?

Question

I am trying to attach a PDF from a shared drive to many Outlook emails generated by a VBA code in Excel.

One sheet has all of the email addresses that will have an email created.

The other sheet in the same workbook has inputs such as the subject line, email body text, and PDF location that can be changed based on what the mass email is about.

While the code to make the subject and htmlbody of the email works, the attachment code doesn't work.

Copying the PDF address to a cell in the active worksheet with the lines of email data, I can attach the PDF.

It is when I treat it as an input on the Inputs worksheet that the issue occurs.

Sub Email_New_Patent_Case()

    Dim i As Integer
    Dim AttorneyCount As Long

    'Set the end of the range equal to the last row of attorney data in the sheet
    AttorneyCount = WorksheetFunction.CountA(Range("B2:B10"))

    For i = 1 To AttorneyCount
        If ActiveSheet.Cells(i + 1, 16) > 0 Then
            Dim objOutlook As Object
            Set objOutlook = CreateObject("Outlook.Application")

            Dim objEmail As Object
            Set objEmail = objOutlook.CreateItem(olMailItem)

            With objEmail
                .To = ActiveSheet.Cells(i + 1, 10).Value
                .CC = ""

                'Make sure this cell corresponds with the Subject Line
                .Subject = ActiveWorkbook.Worksheets("Inputs").Range("D3")

                'Make sure nickname has nickname or first name as value
                .HTMLBody = ActiveSheet.Cells(i + 1, 4).Value & ",<br><br>"

                'Make sure this cell corresponds with the Body
                .HTMLBody = .HTMLBody & ActiveWorkbook.Worksheets("Inputs").Range("D4") 

                'Make sure this cell corresponds with
                ' the desired attachment location on the shared drive
                .Attachments.Add ActiveWorkbook.Worksheets("Inputs").Range("D5") 

                .Save
            End With
        End If
    Next i

End Sub

I get an error message:

"Object doesn't support this property or method."

Try being a bit more explicit: ActiveWorkbook.Worksheets("Inputs").Range("D5").Value — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2019-07-22T23:00:31

This is a bit ambiguous:

.Attachments.Add ActiveWorkbook.Worksheets("Inputs").Range("D5")

...because Attachments.Add can take either a string representing the full file path or an actual object. In this case Add is unable to tell whether you're trying to add the cell itself, or its Value - seems like it defaults to trying to add the range object instead of reading the cell's default property (Value)

Being more explicit fixes the problem:

.Attachments.Add ActiveWorkbook.Worksheets("Inputs").Range("D5").Value

How attach PDF, where location in different Excel sheet from same workbook, to Outlook mail?

2 Answers