Adding Attachments with VBA

[patrickliuhhs]

Hi All,

I'm somewhat new to VBA so bear with me. I'm trying to use VBA to automate emails with personalized attachments for each person. To do this, I created an excel spreadsheet with the person's name in the first column, email address in the second, and path to the attachment in the third.

I haven't been able to get the code to work but I feel like I'm close.

Sub AttachSend()

Dim objMail As Outlook.MailItem
Dim intX As Integer
Dim FileCount As Integer
Dim MailAttachment As String
Dim MailAddress As String


FileCount = Application.WorksheetFunction.CountA(Range("C2:C200"))
Set objMail = Outlook.Application.CreateItem(olMailItem)


For intX = 2 To FileCount
    With Application.ActiveSheet
        Set MailAttachment = Cells(intX, 3).Value
        Set MailAddress = Cells(intX, 2).Value
    
    With objMail
        .Subject = MailAddress.Value
        .Body = "Message Body"
        .Recipients.Add MailAdress.Value
        .Attachments.Add MailAttachment.Value
        .Display
    End With
    Set objMail = Nothing


Next


End Sub

When I run this code, I receive an "Object Required" error with the line "Set MailAttachment =" highlighted. I unfortunately haven't been able to figure out what the issue is so you guys are like my last hope.

Thanks in advance!

 

[John_w]

Set is only used with objects. Try:

With Application.ActiveSheet 
   MailAttachment = .Cells(intX, 3).Value         
   MailAddress = .Cells(intX, 2).Value 
End With

and then:

.Recipients.Add MailAddress
.Attachments.Add MailAttachment