CODEWITHSUNDEEP
vbams-access
user13231908
user13231908

Reputation: 47

Attach a multiple files in VBA

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

Upvotes: 1

Views: 875

Answers (1)

Mukibul H
Mukibul H

Reputation: 850

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.

Upvotes: 2

Related Questions