Using an if statement to add an attachment to an email

[Tablecloth98]

I need to use an if statement to add an attachment to an email if cell G5 contains specific text (e.g. "Specific Text").

I've tried using
If Range("G5").Value = "Specific Text" Then
.Attachment.Add "C:\Documents\MadeUpFileLocation\ImageName.JPG"
End If

but the debug is highlighting the attachment line.

Am I doing something wrong? (Apologies if this is a silly question. I'm very new to vba)

 

[DanteAmor]

C:\Documents\MadeUpFileLocation\

Check that the folder name is correct

ImageName.JPG

Check that the file name is correct

C:\Documents\MadeUpFileLocation\ImageName.JPG

Check that the file exists in the folder.

 

[Tablecloth98]

Check that the folder name is correct


Check that the file name is correct


Check that the file exists in the folder.

Sorry about the names listed it's sensitive information so I had to come up with some dummy data.

I'm wondering if I'm putting the if statement in the wrong place. I was putting it after the email body as I'd planned to embed the images after the body text

 

[DanteAmor]

You are missing the letter "S":

.AttachmentS.Add "C:\Documents\MadeUpFileLocation\ImageName.JPG"

 

[Tablecloth98]

You are missing the letter "S":

.AttachmentS.Add "C:\Documents\MadeUpFileLocation\ImageName.JPG"

It's highlighting the word attachments and saying 'Compile error: Invalid or unqualified reference' for some reason I'm really bad at this

 

[Tablecloth98]

Still having trouble with this. The file location and file name are all correct

 

[Domenic]

Try...

.Attachments.Add "C:\Users\Documents\MadeUpFileLocation\ImageName.JPG"

Hope this helps!

 

[DanteAmor]

'Compile error: Invalid or unqualified reference'

You should put all your code here to see the objects you are using, maybe you are not using the right objects.

Or check the following example:

Sub exampleemail()
  Dim OutApp As Object, OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  With OutMail
    .To = "email@domain.com"
    .Subject = "This is the Subject line"
    If Range("G5").Value = "Specific Text" Then
      .Attachments.Add "C:\trabajo\files\report.xlsx"
    End If
    '.Send
    .Display
  End With
End Sub

 

[Tablecloth98]

You should put all your code here to see the objects you are using, maybe you are not using the right objects.

Or check the following example:

Sub exampleemail()
  Dim OutApp As Object, OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
 
  With OutMail
    .To = "email@domain.com"
    .Subject = "This is the Subject line"
    If Range("G5").Value = "Specific Text" Then
      .Attachments.Add "C:\trabajo\files\report.xlsx"
    End If
    '.Send
    .Display
  End With
End Sub

Try...

.Attachments.Add "C:\Users\Documents\MadeUpFileLocation\ImageName.JPG"

Hope this helps!

Still can't seem to get it working. My full code is here (sensitive info omitted but all file names and locations have been double checked):

Sub Process1()



Dim App As Object, Itm As Object

Dim EmailSubject As String

Dim SendTo As String

Dim EmailBody As String

Dim ccTo As String

Dim i As Long



Application.ScreenUpdating = False

Application.DisplayAlerts = False



Range("B2") = Date

Range("B3") = Time



For i = 5 To Range("B" & Rows.Count).End(3).Row

If Not IsEmpty(Range("B" & i).Value) Then

Set App = CreateObject("Outlook.Application")

Set Itm = App.createitem(0)



SendTo = Range("B" & i).Value

EmailSubject = Account Number- " & Range("C" & i).Value

EmailBody = Range("I3") & vbNewLine & vbNewLine & Range("I" & i).Value & Range("M" & i) & Range("H" & i).Value & Range("J" & i).Value & vbNewLine & Range("B1") & vbNewLine & Range("D1") & vbNewLine & Range("K" & i).Value

If Range("G" & i).Value = "1" Then

.Attachment.Add "C:\Users\UsernameOmitted\Documents\Image1.JPG"
.HTMLBody = "<img src=""cid:Image1.jpg"

End If



With Itm

.SentOnBehalfOfName = Range("L" & i).Value

.Subject = EmailSubject

.To = SendTo

.Body = EmailBody



.Send

End With



Set App = Nothing

Set Itm = Nothing

End If

Next



Application.ScreenUpdating = True

Application.DisplayAlerts = True



MsgBox "Process Complete", vbInformation



End Sub

Thank you for all of your help

 

[Domenic]

Try it like this...

Option Explicit

Sub Process1()

    Dim App As Object, Itm As Object
    Dim EmailSubject As String
    Dim SendTo As String
    Dim EmailBody As String
    Dim ccTo As String
    Dim i As Long
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Range("B2").Value = Date
    Range("B3").Value = Time
   
    Set App = CreateObject("Outlook.Application")
   
    For i = 5 To Range("B" & Rows.Count).End(3).Row
        If Not IsEmpty(Range("B" & i).Value) Then
            SendTo = Range("B" & i).Value
            EmailSubject = "Account Number- " & Range("C" & i).Value
            EmailBody = Range("I3").Value & vbNewLine & vbNewLine & Range("I" & i).Value & Range("M" & i).Value & Range("H" & i).Value & Range("J" & i).Value & vbNewLine & Range("B1").Value & vbNewLine & Range("D1").Value & vbNewLine & Range("K" & i).Value
            Set Itm = App.createitem(0)
            With Itm
                .SentOnBehalfOfName = Range("L" & i).Value
                .Subject = EmailSubject
                .To = SendTo
                .Body = EmailBody
                If Range("G" & i).Value = "1" Then
                    .Attachments.Add "C:\Users\UsernameOmitted\Documents\Image1.JPG"
                    .HTMLBody = .HTMLBody & "<img src=""cid:Image1.jpg"">"
                End If
                .Send
            End With
            Set Itm = Nothing
        End If
    Next i
   
    Set App = Nothing
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
    MsgBox "Process Complete", vbInformation

End Sub

Note that if you want the image placed at the beginning of the email instead of the end, replace...

.HTMLBody = .HTMLBody & "<img src=""cid:Image1.jpg"">"

with

.HTMLBody = "<img src=""cid:Image1.jpg"">" & .HTMLBody

Hope this helps!