.attachment.add - 440 - Property is read-only

owen4512

Board Regular
Joined
Dec 10, 2014
Messages
71
Hi all,

I have created the below macro to help with sending emails to multiple clients in a table. 'm getting error 440 - property is read-only. I'm looking to attach a pdf file that is stored in the location shown below. I have verified the path works as the same path has been used on another vba project I've completed.

I cant seem to figure out why I'm getting this message and any help would b greatly appreciated.

Here's what I've done s far;

Code:
Sub Send_email()
   Dim sh As Worksheet
   Set sh = ThisWorkbook.Sheets("Customer_Data")
   
   Dim Outlook As Object
   Dim msg As Object
   
   Dim BCCTemp, SubjTemp, BdyTemp, FrmTemp As String
   
   Set Outlook = CreateObject("outlook.application")
  
   Dim i As Integer
   Dim last_row As Integer
   
   last_row = Application.WorksheetFunction.CountA(sh.Range("F:F")) + 2 
   For i = 4 To last_row
   Set msg = Outlook.createitem(0)
   
    If sh.Range("G" & i) = ""
    
                If sh.Range("B" & i).Value = "Test1" Then
                FrmTemp = "[EMAIL="Test1@hotmail.co.uk"]Test1@hotmail.co.uk[/EMAIL]"
                BCCTemp = "[EMAIL="test1@hotmail.co.uk"]test1@hotmail.co.uk[/EMAIL]"
                SubjTemp = "Important information"
                BdyTemp = "Dear " & sh.range("A" & i).value
                elseif sh.Range("B" & i).Value = "Test2" Then
                FrmTemp = "[EMAIL="Test2@hotmail.co.uk"]Test2@hotmail.co.uk[/EMAIL]"
                BCCTemp = "[EMAIL="test2@hotmail.co.uk"]test2@hotmail.co.uk[/EMAIL]"
                SubjTemp = "Important information"
                BdyTemp = "Dear " & sh.range("A" & i).value         
                End If
    
        'Email template
        msg.To = sh.Range("F" & i).Value
        msg.SentOnBehalfOfName = FrmTemp
        msg.bcc = BCCTemp
        msg.Subject = SubjTemp
        msg.body = BdyTemp
        msg.attachments.Add = "D:\users\owen\desktop\Important Information.pdf"
        msg.display
        
        
        If sh.Range("G" & i).Value = "Sent" Then
        Else
        sh.Range("G" & i) = "Sent"
        End If
    End If
    
    Next i
End Sub
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Remove the = sign. It's just:

Code:
 msg.attachments.add filepath
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top