Can I add a file path from a cell in Attachments.Add?

Question

I'm trying to create an email for every row of data on my sheet.

It seems to be working until I add the .Attachments.Add line.

I am trying to identify the file path from a cell.

Sub CreateEmails()
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    Dim objEmail As Object
    
    Dim eBody As String
            
    Range("A2").Select
    
    Do Until IsEmpty(ActiveCell)
        Set objEmail = objOutlook.CreateItem(olMailItem)
            
        eBody = "<p>Hi " & ActiveCell(0, 1).Value & ", </p>" _
                & "<p>Message Body</p>" & _
                "<p>Thank you!</p><br>"
    
        With objEmail
            .to = ActiveCell(0, 2).Value
            .Subject = ActiveCell(0, 3).Value
            .HTMLBody = "<html><head></head><body>" & eBody & "</body></html>"
            .Attachments.Add ActiveCell(0, 5).Value
            .Display
        End With
    
        ActiveCell.Offset(1, 0).Select
    Loop
    
End Sub

What happens when it fails? You need the full path stored in your sheet (or if just the filename is there then your code needs to add the path) — Tim Williams, Mar 19 '21 at 03:07
May help to first work out if the file is found from the cell value: https://www.automateexcel.com/vba/file-exists/ — Tragamor, Mar 19 '21 at 10:13
If `ActiveCell` is A2 then `ActiveCell(0, 1)` is A1. If you want B2 then use `ActiveCell.offset(0, 1)`. Same for the others. — CDP1802, Mar 20 '21 at 11:55

score 0 · Answer 1 · answered Mar 21 '21 at 16:31

The Attachments.Add method requires the source of the attachment which can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment. It is not clear what value is passed in your sample code, so I'd suggest debugging the code more carefully and making sure a valid value is passed. For example:

Sub AddAttachment() 
 Dim myItem As Outlook.MailItem 
 Dim myAttachments As Outlook.Attachments 
 
 Set myItem = Application.CreateItem(olMailItem) 
 Set myAttachments = myItem.Attachments 
 myAttachments.Add "C:\Test.doc", _ 
 olByValue, 1, "Test" 
 myItem.Display 
End Sub

score 0 · Answer 2 · answered Mar 21 '21 at 16:43

Instead of using something like this,

ActiveCell(0,1).Value

you should use something like this.

ActiveCell.Offset(0,1).Value

Even better would be not to use ActiveCell, declare and use a variable for the range.

Sub CreateEmails()
Dim objOutlook As Object
Dim objEmail As Object
Dim rng As Range
Dim eBody As String

    
    Set objOutlook = CreateObject("Outlook.Application")
            
    Set rng = Range("A2")
    
    Do Until rng.Value <> ""
    
        Set objEmail = objOutlook.CreateItem(olMailItem)
            
        eBody = "<p>Hi " & rng.Offset(0, 1).Value & ", </p>" _
                & "<p>Message Body</p>" & _
                "<p>Thank you!</p><br>"
    
        With objEmail
            .to = rng.Offset(0, 2).Value
            .Subject = rng.Offset(0, 3).Value
            .HTMLBody = "<html><head></head><body>" & eBody & "</body></html>"
            .Attachments.Add rng.Offset(0, 5).Value
            .Display
        End With
    
        Set rng = rng.Offset(1, 0)
        
    Loop
    
End Sub

Can I add a file path from a cell in Attachments.Add?

2 Answers2