CODEWITHSUNDEEP
excelvbaemailoutlook
joselyn reda
joselyn reda

Reputation: 17

How to add multiple attachments to an email?

I am trying to add two attachments to an email.

Below is the code I'm using.
My msgbox pops up saying emails sent but the attachments are not attaching.
The paths where I identify where the files live are in columns F & G.

Sub Send_Multiple_Emails_Match45()
Dim sh As Worksheet, Issent As Boolean, i As Long, last_row As Long
Set sh = ThisWorkbook.Sheets("Match 45 Vendors Emails")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.Application")

last_row = sh.Range("B" & Rows.Count).End(xlUp).Row

For i = 4 To last_row
    Set msg = OA.createitem(0)
    msg.To = sh.Range("B" & i).Value
    msg.cc = sh.Range("C" & i).Value
    msg.Subject = sh.Range("D" & i).Value
    msg.body = sh.Range("E" & i).Value

    If sh.Range("F" & "G" & i).Value <> "" Then
        If Dir(sh.Range("F" & "G" & i).Value) <> "" Then
            msg.Attachments.Add sh.Range("F" & "G" & i).Value
        Else
            Range("H" & i).Value = "Wrong attachment path"
            GoTo NextMail
        End If
    End If
   
    On Error Resume Next
    msg.send
    If Err.Number <> 0 Then
        Err.Clear: On Error GoTo 0
        Issent = False
    Else
        On Error GoTo 0
        Issent = True
    End If
   
    If Issent = True Then
        Range("H" & i).Value = "Sent"
    Else
        Range("H" & i).Value = "Not Sent"
    End If

NextMail:
    Set msg = Nothing
Next i

MsgBox "Mails Sent"

End Sub

Upvotes: 0

Views: 1304

Answers (3)

syed ibrahim
syed ibrahim

Reputation: 26

Hi Guys i solve this by adding additional if condition for another attachment. below is my code for you. Enjoy the code 

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value

If sh.Range("E" & i).Value <> "" Then
    msg.attachments.Add sh.Range("E" & i).Value
    
End If

If sh.Range("F" & i).Value <> "" Then
    msg.attachments.Add sh.Range("F" & i).Value
    
End If

msg.send

sh.Range("g" & i).Value = "Sent"

Next i

MsgBox "All the mails have been sent successfully, Thank u Syed"


End Sub

Upvotes: 1

joselyn reda
joselyn reda

Reputation: 17

I figured out the problem, in case in the future anyone needs help with this. This is the updated code where it attaches two files and tells me if each attachment was sent or not sent or there was a wrong path.

Dim sh As Worksheet, Issent As Boolean, i As Long, last_row As Long
Set sh = ThisWorkbook.Sheets("Match 45 Vendors Emails")

Dim OA As Object
Dim msg As Object

Dim rngAttach1 As Range
Dim rngAttach2 As Range

Set OA = CreateObject("Outlook.Application")

last_row = sh.Range("B" & Rows.Count).End(xlUp).Row

Set rngAttach1 = sh.Range("F:F")
Set rngAttach2 = sh.Range("G:G")

For i = 4 To last_row
Set msg = OA.createitem(0)
   msg.To = sh.Range("B" & i).Value
   msg.cc = sh.Range("C" & i).Value
   msg.Subject = sh.Range("D" & i).Value
   msg.body = sh.Range("E" & i).Value

If rngAttach1(i).Value <> "" Then
            If Dir(rngAttach1(i).Value) <> "" Then
                msg.Attachments.Add rngAttach1(i).Value
            Else
                Range("H" & i).Value = "Wrong attachment path"
                GoTo NextMail
            End If
        End If
        
If rngAttach2(i).Value <> "" Then
            If Dir(rngAttach2(i).Value) <> "" Then
                Attachments.Add rngAttach2(i).Value
            Else
                Range("I" & i).Value = "Wrong attachment path"
                GoTo NextMail
            End If
        End If
   
   On Error Resume Next
   msg.send
   If Err.Number <> 0 Then
      Err.Clear: On Error GoTo 0
      Issent = False
   Else
      On Error GoTo 0
      Issent = True
   End If
   
    If Issent = True Then
       Range("H" & i).Value = "Sent"
    Else
       Range("H" & i).Value = "Not Sent"
    End If
    
     If Issent = True Then
       Range("I" & i).Value = "Sent"
    Else
       Range("I" & i).Value = "Not Sent"
    End If

NextMail:
        Set msg = Nothing
Next i

MsgBox "Mails Sent"

End Sub

Upvotes: 0

TinMan
TinMan

Reputation: 7759

"F:G" & i is incorrect. You need to specify the row for both F and G. The code will not concatenate the two value for you. It would be best to add a helper variable for the filename. This will help make it easier to test your code.

Upvotes: 1

Related Questions