Attach a multiple files in VBA

Question

Help to modify the following code to be able to attach two file paths as attachments to send mail using MailCDO object in MS Access. Currently it's only attaching first file path only.

I tried using comma, using & sign and nothing seems to work.

    Dim sSubject As String
    Dim sFrom As String
    Dim sTo As String
    Dim sCC As String
    Dim sBCC As String
    Dim sBody As String
    Dim sAttach As String
    Dim sFilePath As String
    Dim MailCDO
   
  sFrom = "abc@abccom"
  sCC = ""
  sBCC = ""
  sTo = "[email protected]"
 
 
  sFilePath = "E:\Reports\Report1.xlsx"
  sAttach = sFilePath

' Want to attach the second sFilePath2
sFilepath2=  sFilePath = "E:\Reports\Report2.xlsx"
 
  sSubject = "Subject"
  sBody = "<p>Email Body</p>"
 
  Set MailCDO = CreateObject("CDO.Message")
  MailCDO.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  MailCDO.Configuration.Fields.Item _
      ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
      = "smtp.xxxyyy.com"
  MailCDO.Configuration.Fields.Update
  MailCDO.Subject = sSubject
  MailCDO.FROM = sFrom
  MailCDO.To = sTo
  MailCDO.CC = sCC
  MailCDO.BCC = sBCC
  MailCDO.HTMLBody = sBody
  MailCDO.AddAttachment sAttach
  MailCDO.Send
  Set MailCDO = Nothing
End Sub

This part needs to be in a loop: MailCDO.AddAttachment sAttach with sAttach changing for each iteration. Or you can just call it multiple times in that same spot with a different filename each time. — braX
– braX, Commented Aug 25, 2021 at 23:35

Mukibul H · Accepted Answer · 2021-08-26 01:21:43Z

2

you should try the with block for clean code

dim sFilepath as string, sFilepath2 as string

sFilePath = "E:\Reports\Report1.xlsx"
sFilepath2=  "E:\Reports\Report2.xlsx"


With MailCDO
    .Subject = sSubject
    .FROM = sFrom
    .To = sTo
    .CC = sCC
    .BCC = sBCC
    .HTMLBody = sBody
    .Attachments.add sFilePath
    .Attachments.add sFilepath2
End with

you can also use for each loop in a collection to attach multiple files just like braX Suggested.

edited Aug 26, 2021 at 1:21

answered Aug 26, 2021 at 1:11

Mukibul H

9508 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user13231908 Over a year ago

Thanks a lot! It worked as I don't know how to use the loop suggested by braX.

Collectives™ on Stack Overflow

Attach a multiple files in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related