Adding multiple attachments to a single email using outlook VBA

Question

EDIT

Sub CreateEmail(Subject As String, Body As String, ToSend As String, CCs As String, FilePathtoAdd As String)

 Dim OlApp As Object
 Dim OlMail As MailItem
 Dim ToRecipient As Variant
 Dim CcRecipient As Variant
 Dim Attachments() As String
 Dim i As Integer


 Set OlApp = Application
 Set OlMail = OlApp.CreateItem(olMailItem)


 OlMail.Recipients.Add ToSend

 OlMail.Subject = Subject
 OlMail.Body = Body
 OlMail.SentOnBehalfOfName = "mailbox"



 If FilePath1 <> "" Then

If FilePathtoAdd <> "" Then
    Attachments = Split(FilePathtoAdd, ",")
    For i = LBound(Attachments) To UBound(Attachments)
        If Attachments(i) <> "" Then
            OMail.Attachments.Add Trim(Attachments(i))
        End If
    Next i
End If


End If

 OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

 End Sub
Sub EmailIt()
CreateEmail "This is Subject", "Body", "To", "CC", "C:\Users\b\Desktop\NFM\Export\0418 LSN " & Format(Date, "mm-dd-yy") & ".xls", "C:\Users\b\Desktop\NFM\Export\0418 Backorder " & Format(Date, "mm-dd-yy") & ".xls"

End Sub

I'm using the code below, in outlook vba, to create an email, attach a file, and send the email. It works fine, except I can't figure out how to add multiple attachments to a single email? Any help is greatly appreciated.

Sub CreateEmail(Subject As String, Body As String, ToSend As String, CCs As String, FilePathtoAdd As String)

 'write the default Outlook contact name list to the active worksheet

 Dim OlApp As Object
 Dim OlMail As MailItem
 Dim ToRecipient As Variant
 Dim CcRecipient As Variant

'Set OlApp = CreateObject("Outlook.Application")
 'Set OlMail = OlApp.CreateItem(olMailItem)

 Set OlApp = Application
 Set OlMail = OlApp.CreateItem(olMailItem)

 'For Each ToRecipient In Array("[email protected]", "[email protected]", "[email protected]")
 'OlMail.Recipients.Add ToRecipient
 OlMail.Recipients.Add ToSend
 'Next ToRecipient


'fill in Subject field
 OlMail.Subject = Subject
 OlMail.Body = Body
 OlMail.SentOnBehalfOfName = "email.com"


 'Add the active workbook as an attachment
' OlMail.Attachments.Add "C:\Users\Ali\Desktop\Sentence Correction\Comparisons.pdf"
 If FilePathtoAdd <> "" Then
    OlMail.Attachments.Add FilePathtoAdd
End If
 'Display the message
 OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

 End Sub
Sub EmailIt()
CreateEmail "This is Subject", "Body", "email.com", " ", "C:\Users\b\Desktop\NFM\Export\0418 LSN " & Format(Date, "mm-dd-yy") & ".xls"


End Sub

Already asked and answered here: Attach two files in a mail

Jean-François Corbett
– Jean-François Corbett

2015-03-19 21:14:35 +00:00
Commented Mar 19, 2015 at 21:14 — Jean-François Corbett
– Jean-François Corbett, Commented Mar 19, 2015 at 21:14

OpiesDad · Accepted Answer · 2015-03-20 19:41:27Z

4

You just need to do:

 Olmail.attachments.add secondpath

If you put the attachment paths in a comma delimited string and pass it as "FilePathToAdd" then you can do this:

Dim Attachments() As String
Dim i As Integer

If FilePathToAdd <> "" Then
    Attachments = Split(FilePathToAdd, ",")
    For i = LBound(Attachments) To UBound(Attachments)
        If Attachments(i) <> "" Then
            OlMail.Attachments.Add Trim(Attachments(i))
        End If
    Next i
End If

edited Mar 20, 2015 at 19:41

answered Mar 19, 2015 at 21:09

OpiesDad

3,4352 gold badges19 silver badges31 bronze badges

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

7 Comments

WeathermanJB Over a year ago

Thanks so much for the reply. I'm still having difficulties getting this to work. I receive the error "Wrong number of arguments or invalid property assignment." I think this is because it is looking for another argument in the sub. If I insert another ADD function, like suggested in the other reply, I can get it to work, but the problem with that is not all of the emails will have the same number of attachments. Your code seems like it is what i am looking for. Maybe I am just inserting the second file path incorrectly? I edited my post with the code inserted, is this correct? Thanks again!

OpiesDad Over a year ago

What line is giving you this error? What are the values of the variables at this point? What does the "Filepathtoadd" string look like? It needs to be comma delimited so "Mypath/File1.xls,Mypath2/File2.xls, Mypath3/File3.xls...."

WeathermanJB Over a year ago

Okay, I messed with it a bit, It looks like i had an extra " in the string. Now I'm getting Run-Time error '424' Object Required on line OMail.Attachments.Add Trim(Attachments(i)) but the filepath is correct and the file is there. My "filepathtoadd" string is

"C:\Users\balljt\Desktop\NFM\Export\0418 LSN " & Format(Date, "mm-dd-yy") & ".xls, C:\Users\balljt\Desktop\NFM\Export\0418 Backorder " & Format(Date, "mm-dd-yy") & ".xls"

Maybe the date format is throwing it off?

WeathermanJB Over a year ago

It grabs the single file fine with OlMail.Attachments.Add FilePathtoAdd and C:\Users\balljt\Desktop\NFM\Export\0418 LSN " & Format(Date, "mm-dd-yy") & ".xls"

OpiesDad Over a year ago

Does this error out the first time it runs through or the second time? Debug.print attachments(i) when the error happens and make sure that the path is valid. It could be happening on the second file. That error occurs when the file doesn't exist...

|

Eugene Astafiev · Accepted Answer · 2015-03-20 10:33:40Z

0

The following lines from your code add the attachment:

 'Add the active workbook as an attachment
 ' OlMail.Attachments.Add "C:\Users\Ali\Desktop\Sentence Correction\Comparisons.pdf"
 If FilePathtoAdd <> "" Then
    OlMail.Attachments.Add FilePathtoAdd
 End If

You just need to call the Add method of the Attachment class as many times as you need to add attachments specifying different file paths.

answered Mar 20, 2015 at 10:33

Eugene Astafiev

49.8k3 gold badges28 silver badges50 bronze badges

1 Comment

WeathermanJB Over a year ago

Thanks for the reply, I got this to work, but the problem is not all of the emails have the same number of attachments. The macro fails if a path is missing. Is there a way around this?

Collectives™ on Stack Overflow

Adding multiple attachments to a single email using outlook VBA

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related