Mail Attachment

[nikhil83]

Mentioned code is used to attach PDF files to mail from a destination . I need each mail to have 1 attachement based on 'Cells(v, 3).Value' . but from second mail onwards, previous attachment is also getting attached to mail.i.e first mail is having the right attachment,second mail has the right attachment + the attachment of previous mail and so on..Please help out..

For i = 1 To valcount
 address = Cells(v, 25).Value
 attachment = "C:\Users\nikhil.r\Desktop\Temp\" & Cells(v, 3).Value & ".pdf"
 MsgBox (attachment)
 With NewMail
  .Subject = "Skill baselining assessment score card"
  .From = "academy@payoda.com"
  .To = address
  .CC = ""
  .BCC = ""
  .HTMLBody = "Hi,<br><br>Find your scorecard attached<br><br>Best Regards<br>L&D"
' For multiple Attachment you can add below lines as many times
  .AddAttachment attachment  '"C:\ExcelMacro-help.xls"
 End With
  NewMail.Send
  v = v + 1
Next

 

[Luke M]

I'd put in a quick loop to delete any pre-existing attachments. I'm not sure what ou have the variable NewMail defined as, so you may need to tweak the code. Hopefully you get the gist of the idea.

Dim cp As Long
For i = 1 To valcount
    Address = Cells(v, 25).Value
    attachment = "C:\Users\nikhil.r\Desktop\Temp\" & Cells(v, 3).Value & ".pdf"
    MsgBox (attachment)
    With NewMail
        .Subject = "Skill baselining assessment score card"
        .From = "academy@payoda.com"
        .to = Address
        .CC = ""
        .BCC = ""
        .HTMLBody = "Hi,<br><br>Find your scorecard attached<br><br>Best Regards<br>L&D"
        ' For multiple Attachment you can add below lines as many times
       
        'Clear any previous attachments
        For cp = .Item.attachments.Count To 1 Step -1
            .Item.attachments(cp).Delete
        Next cp
        .AddAttachment attachment  '"C:\ExcelMacro-help.xls"
    End With
    NewMail.Send
    v = v + 1
Next

 

[nikhil83]

Sorry for that..Code bit is to send mail through gmail . Following is what i have used.

Set NewMail = CreateObject("CDO.Message")

Following error is returned by the code you gave."Object doesn't support this property or method"

 

[Luke M]

Ah, that's a little different. I was guessing it was an Outlook object. Try this:

For i = 1 To valcount
    Address = Cells(v, 25).Value
    attachment = "C:\Users\nikhil.r\Desktop\Temp\" & Cells(v, 3).Value & ".pdf"
    MsgBox (attachment)
    With NewMail
        .Subject = "Skill baselining assessment score card"
        .From = "academy@payoda.com"
        .To = Address
        .CC = ""
        .BCC = ""
        .HTMLBody = "Hi,<br><br>Find your scorecard attached<br><br>Best Regards<br>L&D"
        ' For multiple Attachment you can add below lines as many times
     
        'Clear any previous attachments
        If .attachments.Count > 0 Then .attachments.DeleteAll
       
        .AddAttachment attachment  '"C:\ExcelMacro-help.xls"
   End With
    NewMail.Send
    v = v + 1
Next